🚀 go-pugleaf

I think I have finally found out why I have been beating my head against 
the wall trying to get my DataSetProvider component that allows 
multi-table updates to work.  ALL TRANSACTION SUPPORT IN THE DBEXPRESS 
DRIVERS IS BROKEN!!!!!  At least so I think.  Here is what I found:

     Everything looked perfectly fine except my rollbacks were still failing
and my updates were committing as soon as they were applied and not when
the transaction was committed.
     I traced the SQL commands being issued and everything looked perfectly
fine.  If I issued those sames commands from the psql or mysql
interfaces, they all worked perfectly.
     It looks like the problem is the driver components.  I started
investigating the somewhat obscure AutoClone and MaxStmtsPerConn
fields.  It appears that the PostgreSQL and MySQL drivers limit the
maximum number of statements per connection to 1.  When I issue the
'begin' statement, it must put the active statement count to 1.  When I
issue the next update, it must be the second active statement.  Since
MaxStmtsPerConn is limited to 1, TSQLConnection clones a new
connection.  This connection knows nothing of the transaction started by
the first connection.  This would explain why PostgreSQL always returns
a "NOTICE: not in an active transaction" (or something like that)
whenever I issue a 'rollback' - it is happening on a different
connection than the transaction was started.  That's why the rollback
doesn't know about it.
     In fact, it would appear that even the touted automatic transactions
that wrap every call to ApplyUpdates are really broken.  To test both of
these hypotheses, I set AutoClone to false for my TSQLConnection.  Just
as I suspected, it raised an EDatabaseError exception as soon as I
called ApplyUpdates from the Borland supplied DataSetProvider.
     In other words, I did not manually start a transaction.  I just called
the standard ApplyUpdates method of a standard DataSetProvider
component.  If AutoClone is set to off, it fails because it can't clone
the needed alternate connections that are needed to handle the
statements that are supposedly wrapped in the just launched
transaction.  In reality, those alternate connections know nothing of
the transaction started by the initial connection.
     In other words . . . IT LOOKS LIKE ALL TRANSACTION SUPPORT IS 
BROKEN IN DBEXPRESS!!!!!
     Am I out of my mind? If not, is there a work around
by reconfiguring the database settings? I saw nothing in either the
MySQL or PostgreSQL documentation that seemed like it would solve the
problem from the database side.  Thanks - John

-- 
John A. Sullivan III
Chief Technology Officer
Nexus Management
+1 207-985-7880
john.sullivan@nexusmgmt.com
---
If you are interested in helping to develop a GPL enterprise class
VPN/Firewall/Security device management console, please visit
http://iscs.sourceforge.net