Thread: BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?
BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?
From
"Steve Petrie, P.Eng."
Date:
Greetings To <pgsql-general@postgresql.org>:
I am migrating a PHP web application to use PostgreSQL instead of MySQL.
And I have never used PostgreSQL before.
I have two questions about function pg_result_error_field(...);
An extensive search of the web, did not yield a solution to my problem.
* * *
* * *
My PHP program is using the PHP pg_* PostgreSQL functions.
The test environment is Windows XP SP3 with: PostgreSQL 9.3, Apache 2.2.14 and PHP 5.3.1.
The file php.ini has a line extension = php_pgsql.dll
phpinfo() shows (when my PHP program is not running):
...
pgsql
PostgreSQL Support enabled
PostgreSQL(libpq) Version 8.4.1
Multibyte character support enabled
SSL support enabled
Active Persistent Links 0
Active Links 0Directive Local Value Master Value
pgsql.allow_persistent On On
pgsql.auto_reset_persistent Off Off
pgsql.ignore_notice Off Off
pgsql.log_notice Off Off
pgsql.max_links Unlimited Unlimited
pgsql.max_persistent Unlimited Unlimited
...
* * *
* * *
In the PHP program:
$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_result = pg_get_result($sql_conn); // returns a value of type resource.
$sql_state = pg_result_error_field($sql_result, PGSQL_DIAG_SQLSTATE); // returns a boolean type with a value == FALSE, instead of a string type with a SQLSTATE value.
My question #1 is: should BEGIN; END; & ROLLBACK; commands return a result with a SQLSTATE value available?
* * *
* * *
Additional investigation reveals that every call to function pg_result_error_field($sql_result, ...); with a different constant for the fieldcode parameter, returns a boolean type == FALSE, instead of a meaningful value for the corresponding field:
------------------------------------------------------------------------------- pg_result_error_field(...) ------------------ constant ------------- -------- value ------- -- type -name <value> <1> (TRUE) / <> (FALSE)-------------------------- ------- ----------------------- ---------PGSQL_DIAG_SEVERITY <83> <> <boolean>
PGSQL_DIAG_SQLSTATE <67> <> <boolean>
PGSQL_DIAG_MESSAGE_PRIMARY <77> <> <boolean>
PGSQL_DIAG_MESSAGE_DETAIL <68> <> <boolean>
PGSQL_DIAG_MESSAGE_HINT <72> <> <boolean>
PGSQL_DIAG_STATEMENT_POSITION <80> <> <boolean>
PGSQL_DIAG_INTERNAL_POSITION <112> <> <boolean>
PGSQL_DIAG_INTERNAL_QUERY <113> <> <boolean>
PGSQL_DIAG_CONTEXT <87> <> <boolean>
PGSQL_DIAG_SOURCE_FILE <70> <> <boolean>
PGSQL_DIAG_SOURCE_LINE <76> <> <boolean>
PGSQL_DIAG_SOURCE_FUNCTION <82> <> <boolean>---------------------------------------------------------------------------
According to web page http://www.icosaedro.it/phplint/phplint2/doc/modules/pgsql.html the values of the constants are not the same as the constant values my PHP program shows:
--------------------------------------------my webprog page---- ----PGSQL_DIAG_SEVERITY <83> ==> <23>PGSQL_DIAG_SQLSTATE <67> ==> <24)PGSQL_DIAG_MESSAGE_PRIMARY <77> ==> <25>PGSQL_DIAG_MESSAGE_DETAIL <68> ==> <26>PGSQL_DIAG_MESSAGE_HINT <72> ==> <27>PGSQL_DIAG_STATEMENT_POSITION <80> ==> <28>PGSQL_DIAG_INTERNAL_POSITION <112> ==> <29>PGSQL_DIAG_INTERNAL_QUERY <113> ==> <30>PGSQL_DIAG_CONTEXT <87> ==> <31>PGSQL_DIAG_SOURCE_FILE <70> ==> <32>PGSQL_DIAG_SOURCE_LINE <76> ==> <33>PGSQL_DIAG_SOURCE_FUNCTION <82> ==> <34>--------------------------------------------
I note that the constant values listed on the web page, are in a tight strict ascending sequence from 23 ... 34, whereas the values obtained by my program are scattered.
Perhaps the values of the constants being obtained by my PHP program are not correct?
Question #2: Is there a PHP source code module my program needs to 'require' to obtain the correct constant values?
Any comments / suggestions would be appreciated.
Thanks,
Steve
On Sat, 2015-10-03 at 08:18 -0400, Steve Petrie, P.Eng. wrote: > Greetings To <pgsql-general@postgresql.org>: > > I am migrating a PHP web application to use PostgreSQL instead of > MySQL. > > And I have never used PostgreSQL before. > > I have two questions about function pg_result_error_field(...); > > An extensive search of the web, did not yield a solution to my > problem. > > * * * > * * * > > My PHP program is using the PHP pg_* PostgreSQL functions. > > The test environment is Windows XP SP3 with: PostgreSQL 9.3, Apache > 2.2.14 and PHP 5.3.1. > > The file php.ini has a line extension = php_pgsql.dll > > phpinfo() shows (when my PHP program is not running): > ... > pgsql > PostgreSQL Support enabled > PostgreSQL(libpq) Version 8.4.1 > Multibyte character support enabled > SSL support enabled > Active Persistent Links 0 > Active Links 0 > I'm a Linux user. However, libpq version 8.4.1 doesn't jell with version 9.3 of PostgreSql. So, are you trying to obtain the failure "reason" when a "BEGIN", "COMMIT" or "ROLLBACK" fails? Cheers, Rob
Re: BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?
From
"Steve Petrie, P.Eng."
Date:
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? * * * * * * [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 * * * 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. 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> To: "Steve Petrie, P.Eng." <apetrie@aspetrie.net>; <pgsql-general@postgresql.org> Sent: Saturday, October 03, 2015 1:54 PM Subject: Re: [GENERAL] BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value? > On Sat, 2015-10-03 at 08:18 -0400, Steve Petrie, P.Eng. wrote: >> Greetings To <pgsql-general@postgresql.org>: >> >> I am migrating a PHP web application to use PostgreSQL instead of >> MySQL. >> >> And I have never used PostgreSQL before. >> >> I have two questions about function pg_result_error_field(...); >> >> An extensive search of the web, did not yield a solution to my >> problem. >> >> * * * >> * * * >> >> My PHP program is using the PHP pg_* PostgreSQL functions. >> >> The test environment is Windows XP SP3 with: PostgreSQL 9.3, Apache >> 2.2.14 and PHP 5.3.1. >> >> The file php.ini has a line extension = php_pgsql.dll >> >> phpinfo() shows (when my PHP program is not running): >> ... >> pgsql >> PostgreSQL Support enabled >> PostgreSQL(libpq) Version 8.4.1 >> Multibyte character support enabled >> SSL support enabled >> Active Persistent Links 0 >> Active Links 0 >> > > I'm a Linux user. However, libpq version 8.4.1 doesn't jell with > version 9.3 of PostgreSql. > > So, are you trying to obtain the failure "reason" when a "BEGIN", > "COMMIT" or "ROLLBACK" fails? > > Cheers, > Rob > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/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