PostgreSQL and SQLSTATE

Hi there,

is there a way to access the SQL STATE when obtaining an error message from the PostgreSQL class? I am getting only error code “3” when there’s something wrong. But PgAdmin tells me much more (as stated in the PostgreSQL docs) - in the SQL STATE. But - I cannot access this at the moment. Am I overlooking something?

Thank you for any hint :slight_smile:

Jan

the error code that the PostgreSQL plugin returns in the Error Code Class number and not the exact number (exact numbers are 4-5 digits in length. last 3 digits are the specific error within the error code class, and the first 1-2 digits is the error code class number.

I am learning a lot about PostgreSQL and coding for it right now. Mostly by baptism by fire.

Good luck! if you figure out how to do this, I would love to know.

You mean “trial and error” ? since baptism has nothing to do with fire :slight_smile:

As an aside, I’ve grown to love Postgres. I used to think it was quirky, but after having used it exclusively for a few years, I’ve come to realize how convenient and well thought-out some of its features are. I mean, just the RETURNING clause when inserting or deleting is worth the price of admission.

Edit: It helps that the price of admission is actually $0.

trial and lots of very painful errors… very painful… hence the phrase…

Ok … Thank you for the replies :slight_smile: I will keep trying to get around it - and … in case I’ll find something, I will post it here.

@Kem Tekinay: having used dBase (last century) and a lot of SQLite I am baffled by what PostgreSQL can do … if you kick the little inner self to get over the learning threshold. I start to like it very much!

:slight_smile:

Another tip that was a real eye-opening time-saver. Postgres has a language, PLPGSQL, that lets you create functions and triggers, but you can also use it for in-line functions. We do a lot of code that looks something like this:

create or replace function ...

--
-- Unit tests
--
savepoint unittests;

do $$
begin
  -- Test our function
end $$;

rollback to savepoint unittests;

When we apply a migration, we test it right away and raise an exception if it fails. It’s prevented introducing errors that didn’t show up on our development systems but appear in production. It also lets us do more complex migrations that we’d otherwise have to code in Xojo or another language.

thats a new one to me. if it half as good as it sounds, I am in love.

INSERT INTO my_table (col1, col2) values (data1, data2) RETURNING id;

-- or

INSERT INTO my_table (col1, col2) values (data1, data2) RETURNING *;

Your love is justified.

RETURNING id is a powerfull feature PostgreSQL.

For those interested: MySQL needs a little code after executing the INSERT INTO to achieve the same

 Rs = mDb.SQLSelect("SELECT LAST_INSERT_ID()")

For SQLite use:

SELECT last_insert_rowid()

The PostgreSQL solution is much better than the MySQL and SQLite solution to retrieve the id of the last added record.

Imagine you have a very busy database with lots of users connected. In case you have to run a SELECT query right after the INSERT INTO, it could occur that someone else has inserted a new record in between.
So, you should have the INSERT INTO and the SELECT within just ONE transaction.

AFAIK, you are right, the SQL STATE is not available through the Plugin. I requested this long ago as <https://xojo.com/issue/28040> Feel free to add your voice there or assign some of your feedback points.

You may want to read the ErrorMessage property and try to parse this.

Further, even if there is no error, there may be some messages generated as NOTICE (or WARNNIG) by the backend which you can receive with other clients like psql. In the ErrorMessage property there are only errors reported. To receive these messages, please support <https://xojo.com/issue/13158>