Postgres Pro
Downloads
Home   >   mailing lists

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

From Steve Petrie, P.Eng.
Subject BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?
Date
Msg-id AAE80C8C76F549D7B4B1467D1F87DA54@Dell
Whole thread Raw
Responses Re: BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?  (rob stone <floriparob@gmail.com>)
List pgsql-general
Tree view
 
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
 
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
...
* * *
* * *
 
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      web
                              prog     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
 

pgsql-general by date:

Previous
From: Michael Paquier
Date:
Subject: Re: Replication with 9.4
Next
From: "Steve Petrie, P.Eng."
Date:
Subject: *** CORRECTION -- PLEASE IGNORE *** BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?