Transactions from Visual C++

Hi,

I noticed that the database engine does not let you implement your own
transaction logic from Microsoft VC++ applications.
The database automatically rolls back when the first error occurs, not
the way it is programmed.

Consider the following example:

void RunMyQuery(CDatabase* pDB, LPCTSTR lpszSQL)
{
try { pDB->ExecuteSQL(lpszSQL); }
catch (CDBException* pEx)
{ pEx->ReportError(); pEx->Delete(); }
}

void test()
{
CDatabase* pDB = NULL; // TODO: open connection to postgreSQL here
pDB->BeginTrans();
RunMyQuery(pDB,"INSERT INTO mytable (mycol) VALUES ('a');");
RunMyQuery(pDB,"INSERT INTO mytable (mycol) VALUES ('b');");
RunMyQuery(pDB,"INSERT INTO mytable (mycol) VALUES ('c');");
RunMyQuery(pDB,"INSERT INTO mytable (mycol) VALUES ('d');");
RunMyQuery(pDB,"INSERT INTO mytable (mycol) VALUES ('e');");
pDB->Rollback();
}

Let us assume that the second statement fails because value 'b' is
illegal.
Either the ODBC driver or the DBMS now rejects all follow-on queries
with the error message:
Error while executing the query;
ERROR: current transaction is aborted, commands ignored until
end of transaction block

I would have expected that it would still be possible to run queries
inside this transaction (in fact, the same code continues to run in
Sybase and MSSQL Server).
Otherwise it is not possible to write your own transaction logic or your
own error handling in this environment.

I am using PostgreSQL 8.1.4 under Windows XP SP 2 and connecting with
the PostgreSQL ANSI ODBC driver.

Regards,

Christof Paal

Senior Scientist
Application Developement Branch
Operations Research Division
NATO C3 Agency