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-novice@postgresql.org>:
(Note: an erroneous version of this posting was sent earlier to <pgsql-general@postgresql.org> -- please ignore that erroneous posting).
I am migrating a PHP web application to use PostgreSQL instead of MySQL.
And I have never used PostgreSQL before.
In my PHP program, I am testing whether or not a PostgreSQL BEGIN; command has succeeded.
It appears to me that there is no SQLSTATE value available, in the result obtained from the BEGIN; command.
Instead of just assuming that this is correct behavior, I prefer to know for sure -- should there be a SQLSTATE value available?
An extensive search of the web, did not yield an answer to my question.
* * *
* * *
Background
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:
...
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
...
* * *
* * *
The Problem
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_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).
$sql_state = pg_result_error_field($sql_result, PGSQL_DIAG_SQLSTATE); // returns a NULL type , instead of a string type with a SQLSTATE value.
* * *
* * *
Constant Values
Additional investigation reveals that, after the pg_get_result($sql_conn);, every call to function pg_result_error_field($sql_result, ...); with a different constant for the fieldcode parameter, returns a NULL type, instead of a meaningful type and value for the corresponding field:
--------------------------------------------------------------------------------------- constant ----------- ---- pg_result_error_field(...) ----
name <value> <returns type>
-------------------------- ------- --------------
PGSQL_DIAG_SEVERITY <83> <NULL>
PGSQL_DIAG_SQLSTATE <67> <NULL>
PGSQL_DIAG_MESSAGE_PRIMARY <77> <NULL>
PGSQL_DIAG_MESSAGE_DETAIL <68> <NULL>
PGSQL_DIAG_MESSAGE_HINT <72> <NULL>
PGSQL_DIAG_STATEMENT_POSITION <80> <NULL>
PGSQL_DIAG_INTERNAL_POSITION <112> <NULL>
PGSQL_DIAG_INTERNAL_QUERY <113> <NULL>
PGSQL_DIAG_CONTEXT <87> <NULL>
PGSQL_DIAG_SOURCE_FILE <70> <NULL>
PGSQL_DIAG_SOURCE_LINE <76> <NULL>
PGSQL_DIAG_SOURCE_FUNCTION <82> <NULL>
-------------------------------------------------------------------------
Web page http://www.icosaedro.it/phplint/phplint2/doc/modules/pgsql.html lists values for the constants, that ARE NOT the same as the constant values listed above, obtained in my PHP program.
However, according to a web page that I saved earlier from github.com (it appears the page is no longer online), the values of constants listed above ARE the same as the constant values listed above, obtained in my PHP program.
Here is a summary from the two web pages:
------------------------------------------------------------------------------- constant value -----------
my ---------- web page ---------
constant name prog www.icosaedro.it github.com
----------------------------- ---- ---- ----
PGSQL_DIAG_SEVERITY <83> ==> <23> <83>
PGSQL_DIAG_SQLSTATE <67> ==> <24) <67>
PGSQL_DIAG_MESSAGE_PRIMARY <77> ==> <25> <77>
PGSQL_DIAG_MESSAGE_DETAIL <68> ==> <26> <68>
PGSQL_DIAG_MESSAGE_HINT <72> ==> <27> <72>
PGSQL_DIAG_STATEMENT_POSITION <80> ==> <28> <80>
PGSQL_DIAG_INTERNAL_POSITION <112> ==> <29> <112>
PGSQL_DIAG_INTERNAL_QUERY <113> ==> <30> <113>
PGSQL_DIAG_CONTEXT <87> ==> <31> <87>
PGSQL_DIAG_SOURCE_FILE <70> ==> <32> <70>
PGSQL_DIAG_SOURCE_LINE <76> ==> <33> <76>
PGSQL_DIAG_SOURCE_FUNCTION <82> ==> <34> <82>
--------------------------------------------------------------------
I tried replacing the constant name PGSQL_DIAG_SQLSTATE with the integer value 24 in the call to function pg_result_error_field(...); but still got a NULL type for a return value:
$sql_state = pg_result_error_field($sql_result, 24); // returns a NULL type , instead of a string type with a SQLSTATE value.
* * *
* * *
Questions
My question #1 is: should PostgreSQL BEGIN; END; & ROLLBACK; commands, provide a result that contains a SQLSTATE value, that can be obtained as a string type value, with a call to function pg_result_error_field($sql_result, PGSQL_DIAG_SQLSTATE); ?
Question #2: Is there a PHP source code module my program needs to 'require' to obtain the correct constant values?
Any comments / suggestions / questions would be appreciated.
Thanks,
Steve