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

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(at)postgresql(dot)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(at)postgresql(dot)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(at)gmail(dot)com>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com