[fpc-devel] Changing transaction properties specific to IB when using IBConnection

[Michalis Kamburelis]

Hi

I played with Sqldb and IBConnection units, and now I see the real 
benefit of them: there's only one dataset and one transaction class for 
all Firebird, PostgreSQL and MySQL bindings. Each specific database 
"binding" must only introduce new TSQLConnection descendant. It's great 
since it gives me even easier switching between various databases. This 
reminds me the zeos project.

However, there's a problem. In real programs I want to be able to set 
some FB-specific transaction parameters. By doing this I'm agreeing to 
sacrifice some portability of my programs across various databases, but 
I think it's useful, at least with FB, since there are many sensible 
transaction isolation modes and I want to be able to choose what I want.

When I was playing with Interbase unit yesterday, this was simple, much 
like with Delphi's IBX: just set appropriate properties of 
TIBTransaction class, like IsolationLevel.

How to get equivalent functionality with Sqldb and IBConnection ?

I implemented some small patches that enable this. The essential thing 
is new public function TSQLTransaction.SQLHandle that gives an access to 
TIBTrans class specific to FB database. Note that this forced me to 
write some additional code to react when user changes value of 
TSQLConnection.Database property. User of this code must be prepared 
that changing TSQLConnection.Database resets properties of SQLHandle.

(
Some notes about the problem with resetting properties of SQLHandle:

This problem is (at least partially) inevitable, since when value of 
Database property changes, it may change to a different descendant of 
TSQLConnection so the set of available properties may change anyway. So 
I think that the current method (that will implicitly reset all 
properties of SQLHandle, because internally FTrans will be destroyed and 
then (at the next call to SQLHandle) created again) is OK.
)

Now I can change properties of FB transaction doing e.g.
   (SQLTransaction.SQLHandle as TIBTrans).IsolationLevel := ilReadCommitted;

Attached patch also fixes a small bug, by changing in 
TSQLQuery.InternalOpen FieldByName to FindField. See inside the patch 
for comments what it solves.

One more thing: note that default IsolationLevel of 
IBConnection.TIBTrans is ilConcurrent, while default IsolationLevel of 
Interbase.TIBTransaction is ilReadCommitted. It's not my fault :), 
that's the way they were done. And none of my patches tries to change 
it, since it would be an incompatible change. However, I would advice 
changing default IsolationLevel of Interbase.TIBTransaction to 
ilConcurrent. This way not only IBConnection and Interbase units would 
use the same IsolationLevel by default, but also this would make 
Interbase.TIBTransaction a little more compatible to Delphi's IBX and to 
standard FB behaviour (empty TPB passed to isc_start_transaction is 
equivalent to isc_tpb_concurrency).

Michalis.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: fcl_db.patch
Type: text/x-patch
Size: 6930 bytes
Desc: not available
URL: <http://lists.freepascal.org/pipermail/fpc-devel/attachments/20050318/fb2485e6/attachment.bin>