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); }=20
catch (CDBException* pEx)
{ pEx->ReportError(); pEx->Delete(); }
}

void test()
{
CDatabase* pDB =3D 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
=20
Senior Scientist
Application Developement Branch
Operations Research Division
NATO C3 Agency

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Aug 23, 2006, at 7:31 AM, <Christof.Paal (AT) nc3a (DOT) nato.int>
<Christof.Paal (AT) nc3a (DOT) nato.int> wrote:
Why continue to run commands if you're just going to roll them back?

In any case, I fail to see how this makes it impossible to control
transactions. Perhaps you need to look into savepoints?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby (AT) pervasive (DOT) com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq