[unixODBC-support] unixODBC bug with PHP, Postgres?

Justin Bailey jgbailey+unixODBC at gmail.com
Wed Aug 20 21:57:05 BST 2008


Hi,

I am using unixODBC 2.2.9,  PHP 5.2.3 and Postgres 8.2.4.   I believe
I have found a bug when using prepared statements with autocommit
turned off. It appears all queries executed will be rolled back,
except the last one. The rest of this email shows how it happens.

I am executing prepared statements via PHP's ODBC interface. I am not
using this facility to prepare statements and re-use them - I only
want the parameter binding facility. Therefore, I prepare a statement,
execute it, get the results, and throw away the statement. In my brief
reading of the ODBC reference on MSDN this seems OK.

I am using SELECT queries to execute user-defined functions (postgres
does not allow them be executed any other way). When I try to execute
multiple queries in one transaction,  logs show these statements
(remember, autocommit is off):

postgres[7615]: [21-1] LOG:  statement: BEGIN;SELECT
myFunction(CAST(NULL AS bigint),CAST(NULL AS bigint),NULL) as result
postgres[7615]: [22-1] LOG:  statement: ROLLBACK
postgres[7615]: [23-1] LOG:  statement: BEGIN;SELECT myFunction(CAST(
'616013000000000136' AS bigint),CAST('616013000000000064' AS bigint),
'xxx') as result
postgres[7615]: [24-1] LOG:  statement: SELECT myFunction(CAST(NULL AS
bigint),CAST(NULL AS bigint),NULL) as result
postgres[7615]: [25-1] LOG:  statement: ROLLBACK
postgres[7615]: [26-1] LOG:  statement: BEGIN;SELECT myFunction(CAST(
'616013000000000139' AS bigint),CAST( '616013000000000064' AS bigint),
'xxx') as result
postgres[7615]: [27-1] LOG:  statement: COMMIT

The log shows this sequence of events:

 1) Issue a query with all parameters set to NULL.
 2) ROLLBACK
 3) Issue the same query with the real parameters. This is the end of
the first query I issued.
 4) Issue a new query with parameters again set to NULL.
 5)  ROLLBACK
 6) Again, Issue the same query with real parameters. This is the end
of my second query.
 7) COMMIT

It looks like preparing a statement causes a rollback, for each
statement prepared. When multiple statements are executed, only the
results of the last statement are committed.

Does anyone have any comments or workarounds?  Otherwise please
consider this a bug report.

Justin



More information about the unixODBC-support mailing list