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

From Steve Petrie, P.Eng.
Subject Re: BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?
Date
Msg-id 0C895FCEF09C484D89B2E2CD47E00EB0@Dell
Whole thread Raw
In response to BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?  ("Steve Petrie, P.Eng." <apetrie@aspetrie.net>)
Responses Re: BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?
List pgsql-general
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



pgsql-general by date:

Previous
From: Madovsky
Date:
Subject: Re: Replication with 9.4
Next
From: Adrian Klaver
Date:
Subject: Re: BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?