Re: BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value? - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value? |
Date | |
Msg-id | 56105677.3090804@aklaver.com Whole thread Raw |
In response to | Re: BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value? ("Steve Petrie, P.Eng." <apetrie@aspetrie.net>) |
List | pgsql-general |
On 10/03/2015 02:50 PM, Steve Petrie, P.Eng. wrote: > Hello Rob, > > Thanks for your prompt response -- much appreciated. > > I actually re-submitted my questions to <pgsql-novice@postgresql.org> > with a correction. After the "BEGIN;" query and the pg_get_result(...); > all calls to function pg_result_error_field(...) are returning type NULL > (not type boolean, as I reported erroneously in my post to > <pgsql-general@postgresql.org>). > > * * * > * * * > > [RS] > I'm a Linux user. However, libpq version 8.4.1 doesn't jell with version > 9.3 of PostgreSql > > [SP] > OK, so presumably I need to upgrade the libpq, to something compatible > with version 9.3 of PostgreSql? Not necessarily, see below but it would not hurt to use new version. http://grokbase.com/t/postgresql/pgsql-docs/129chxfrdm/details-about-libpq-cross-version-compatibility > > * * * > * * * > > [RS] > So, are you trying to obtain the failure "reason" when a "BEGIN", > "COMMIT" or "ROLLBACK" fails? > > [SP] > Actually no, I don't believe that the "BEGIN;" query is failing. My > question is whether or not the "BEGIN;" query result should provide a > SQLSTATE value that I can test, as an additional check on the success / > failure of the "BEGIN;" query. > > Until I received your posting (that "libpq version 8.4.1 doesn't jell > with version 9.3 of PostgreSql"), I was pretty sure that the "BEGIN;" > query is working OK, because: > > $sql_conn = pg_connect(...); // returns a value of type resource. > ... > pg_connection_status($sql_conn); // returns an integer type with a value > == 0 (PGSQL_CONNECTION_OK). > ... > pg_send_query($sql_conn, 'BEGIN;'); // returns a boolean type with a > value == TRUE. > ... > $sql_transaction_status = pg_transaction_status($sql_conn); // returns > an integer type with a value == 1 (PGSQL_TRANSACTION_ACTIVE). > ... > $sql_result = pg_get_result($sql_conn); // returns a value of type > resource. > ... > $sql_result_status_long = pg_result_status($sql_result, > PGSQL_STATUS_LONG); // returns an integer type with a value == 1 > (PGSQL_COMMAND_OK). > ... > $sql_result_status_string = pg_result_status($sql_result, > PGSQL_STATUS_STRING); // returns a string result with a value == 'BEGIN'. > ... > $sql_result_error = pg_result_error($sql_result); // returns a string > result with a value == '' (empty string). > > All the above seem to me -- the PostgreSQL novice -- to indicate that > the "BEGIN;" query is working OK > > * * * Have you looked at: http://php.net/manual/en/function.pg-query.php " query The SQL statement or statements to be executed. When multiple statements are passed to the function, they are automatically executed as one transaction, unless there are explicit BEGIN/COMMIT commands included in the query string. However, using multiple transactions in one function call is not recommended. " and here: http://stackoverflow.com/questions/9704557/php-pgsql-driver-and-autocommit Answer 1 > > However, I was also trying to use SQLSTATE as an additional check on the > success / failure of the "BEGIN;" query, but it appears to me that there > is no SQLSTATE value available in the result returned by the "BEGIN;" > query: > > $sql_state = pg_result_error_field($sql_result, PGSQL_DIAG_SQLSTATE); // > returns a NULL type , instead of a string type with a SQLSTATE value. See here: http://www.postgresql.org/docs/9.4/static/libpq-exec.html PG_DIAG_SQLSTATE The SQLSTATE code for the error. The SQLSTATE code identifies the type of error that has occurred; it can be used by front-end applications to perform specific operations (such as error handling) in response to a particular database error. For a list of the possible SQLSTATE codes, see Appendix A. This field is not localizable, and is always present. http://www.postgresql.org/docs/9.4/static/errcodes-appendix.html No error no error code. You already found what you wanted: $sql_result_status_long = pg_result_status($sql_result, PGSQL_STATUS_LONG); // returns an integer type with a value == 1 (PGSQL_COMMAND_OK). ... $sql_result_status_string = pg_result_status($sql_result, PGSQL_STATUS_STRING); // returns a string result with a value == 'BEGIN'. http://www.postgresql.org/docs/9.4/static/libpq-exec.html See PGRES* codes. > > So my question was -- should there be a SQLSTATE available, in the > result returned by the "BEGIN;" query? > > * * * > * * * > > Now however, based on your posting (that "libpq version 8.4.1 doesn't > jell with version 9.3 of PostgreSql") I will upgrade the libpq version > to be compatible with version 9.3 of PostgreSql, and then re-try the > "BEGIN;" query and see if there is a SQLSTATE value available in the > result. > > Thanks Again and Regards, > > Steve > > ----- Original Message ----- From: "rob stone" <floriparob@gmail.com> -- Adrian Klaver adrian.klaver@aklaver.com
pgsql-general by date: