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

From: "Steve Petrie, P(dot)Eng(dot)" <apetrie(at)aspetrie(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Subject: BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?
Date: 2015-10-03 12:18:50
Message-ID: AAE80C8C76F549D7B4B1467D1F87DA54@Dell
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings To <pgsql-general(at)postgresql(dot)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

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Petrie, P.Eng. 2015-10-03 12:35:26 *** CORRECTION -- PLEASE IGNORE *** BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?
Previous Message Michael Paquier 2015-10-03 11:48:23 Re: Replication with 9.4