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

Поиск
Список
Период
Сортировка
От Steve Petrie, P.Eng.
Тема BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?
Дата
Msg-id AAE80C8C76F549D7B4B1467D1F87DA54@Dell
обсуждение исходный текст
Ответы Re: BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?
Список pgsql-general
Дерево обсуждения
 
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
 

В списке pgsql-general по дате отправления:

Предыдущее
От: Michael Paquier
Дата:
Сообщение: Re: Replication with 9.4
Следующее
От: "Steve Petrie, P.Eng."
Дата:
Сообщение: *** CORRECTION -- PLEASE IGNORE *** BEGIN, END & ROLLBACK commands -- do they produce a SQLSTATE value?