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:
 
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
 

Re: BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?

From
rob stone
Date:
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



Re: BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?

From
Adrian Klaver
Date:
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