The reporting system in my current project has the need for serialized
transactions so numbers can be consistent coming from two queries
within one transaction. Using a psql command line, I can test the
functionality I need using:
begin;
set transaction isolation level serializable;
select count(*) from mytable;
*** Perform insert on mytable from another connection here
select count(*) from mytable;
end;
Both selects should give me the same result (which they do). The problem
I'm having is when I try to translate this into ODBC. It appears from the
driver code that the ODBC driver automatically calls BEGIN when not in
autocommit mode and only does it for a certain set of functions (insert,
update, delete, grant, revoke, and a couple others I'm forgetting).
Consequently, the "set transaction" statement executed from an ODBC
application is useless because it's not done inside the current transaction.
The normal solution would be to use SQLSetConnectOption with
SQL_TXN_ISOLATION set to SQL_TXN_SERIALIZABLE, but the
ODBC driver ignores those settings (according to the source).
So, I'm wondering how to get a serialized transaction through ODBC
without making changes to the driver source.
Thanks,
Jason Koeninger
J&J Computer Consulting