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.

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.

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

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

Michael.

Oh, and here's one additional patch to sqldb.pp that improves (ok, ok: *corrects*) my previous patch. This way FTrans is not freed each time transaction ends, so properties of FTrans like IsolationLevel are preserved, e.g. code like

      (Transaction.SQLHandle as TIBTrans).IsolationLevel := ilReadCommitted;
      Transaction.StartTransaction;
      ...
      Transaction.Commit;
      Transaction.StartTransaction;
      ...
      Transaction.Commit;

will create read committed transaction two times.

Michalis.

Michael Van Canneyt wrote:
...

...

Indeed, this would be more elegant than my solution, but this would require finding some common functionality between various databases (Firebird, PostgreSQL, MySQL, and thinking about others in the future). I.e. something that would be both flexible and yet (at least relatively) portable to all TSQLConnection descendants. I'll look into this tomorrow. Although I may need some help about what transaction modes can be expressed in PostgreSQL and MySQL. I'll see tomorrow what I can do (and where I will need some help :).

Michalis.

1. After reading docs of PostgreSQL and MySQL (I know possibilities of Firebird by heart :), I must admit that I don't see a way to define TSQLTransactionStyle in a way that would be both really flexible and portable to all 3 databases (Firebird, PostgreSQL, MySQL). Every database has an idea of at least two transaction modes like

a) Concurrent (aka "Snapshot")
b) Read committed

... but that's all. Many details seem to be different, especially "read committed" mode has many diffeent variants and possibilities (whether one multirow select can be only partially affected by committing some other transaction ? In Firebird yes, it's always possible, in PostgreSQL you can avoid this. What should happen when uncommitted data is pending ? In Firebird -- many choices: read committed record version, wait for committing or rollback, return immediately with error... PostgreSQL seems to always return committed record version. Anyway -- these are just examples).

I'm waiting for your opinion on this point. How exactly would you propose TSQLTransactionStyle to be defined, and how it should behave when some modes cannot be implemented in some database ?

2. To clear field for our discussion, I'm attaching all my simple corrections to fcl/db/ as a patch to this email. This patch does *not* mess with anything related to changing transaction modes (neither "your" way of TSQLTransactionStyle nor "my" initial way of exposing TSQLTransaction.SQLHandle). In other words, this is the patch that you want to apply now, no matter what is your answer to point 1 above :)

Summary of changes in this patch:
-- Default TIBTransaction.IsolationLevel is changed to ilConcurrent. This makes TIBTransaction more consistent with TIBConnection behaviour, and Delphi's IBX, and Firebird C API (as I explained in one of previous letters).

-- In units Interbase and IBConnection, SQLVar[...].AliasName is used everywhere, instead of SQLVar[...].SQLName. As I explained in previous letter "[fpc-devel] Fix to IBConnection field names", using AliasName is just better than SQLName. Consider e.g. `select count(*) from ...', that has one field with SQLName = '' but AliasName = 'COUNT'. Also, developer writing SQL statememt has full control over what alias name will be assigned for the field.

-- Fix for Sqldb unit that used in one place FieldByName while FindField
+ manual check was required. This is described in detail in comments inside patch.

-- TIBDatabase should allow setting Transaction from non-nil to nil (TIBDatabase must anyway be prepared everywhere to handle the case when Transaction = nil), and also TIBDatabase should automatically set it's Transaction property to nil when Transaction instance is destroyed (using FreeNotification). I rearranged TIBDatabase.SetTransaction to make it (in my opinion) cleaner.

Michalis.