BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value? - Mailing list pgsql-novice

From Steve Petrie, P.Eng.
Subject BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?
Date
Msg-id 3216DF5CFAA542FBA1DE35696B13A875@Dell
Whole thread Raw
List pgsql-novice
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                 0
 
Directive                    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
 

pgsql-novice by date:

Previous
From: Ken Benson
Date:
Subject: Re: Backup failed using pg_dump from command promt
Next
From: droberts
Date:
Subject: Best design for aggregrate fact table