Hi Takayuki,
Thanks for your in-depth information. It helped a lot. AutoCommit worked the right way as you indicated in your test code.
There was an application problem where-in explicit transaction was opened and before the updates got committed in postgres database, we hit an error (application error) due to which entire transaction got rolled back.
Your helpful suggestion of enabling logging to identify behavior of autocommit helped me catch the exact problem.
Appreciate your help on this. Sincere thanks to everyone who replied to my email with valuable guidance J.
Thanks,
Sekhar
-----Original Message-----
From: Tsunakawa, Takayuki [mailto:tsunakawa.takay@jp.fujitsu.com]
Sent: Wednesday, February 17, 2016 9:14 AM
To: Venkatesan, Sekhar; Adrian Klaver; Joshua D. Drake; pgsql-odbc@postgresql.org
Subject: RE: [ODBC] PostgreSQL: Autocommit through windows odbc driver doesnt work!!!
Sorry, the attached test program is the right one.
Try adding the following line in postgresql.conf and run your program:
log_statement = 'all'
If SQL_AUTOCOMMIT_OFF is effective, these lines are output in the server log. This shows that the ODBC driver sent BEGIN at the first SQL statement in a transaction, and the application explicitly committed the transaction with SQLEndTran(SQL_COMMIT).
LOG: statement: BEGIN;INSERT INTO a VALUES(100)
LOG: statement: COMMIT
If SQL_AUTOCOMMIT_ON is effective, this line is output. The ODBC driver doesn't send BEGIN.
LOG: statement: INSERT INTO a VALUES(100)
Regards
Takayuki Tsunakawa