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@xxxxxxxxxxxxxx>
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@xxxxxxxxxxxxxx>).
* * *
* * *
[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@xxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
