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

[Michael Van Canneyt]

On Fri, 18 Mar 2005, Michalis Kamburelis wrote:

> 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 would propose to introduce a enumerated

TSQLTransactionStyle =(tsConcurrent,tsReadCommit, etc.);

Then add a TransactionStyle to TSQLTransaction;
This must be mapped by the TSQLConnection when creating the handle.

> 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.

An event 'AfterGetSQLHandle' could be introduced in TTransaction. But I
think the abo

>
> (
> 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).

This can be done. None of the DB components are 'fixed', i.e. you are
free to submit patches, suggest changes etc.

Michael.