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

[Michalis Kamburelis]

I wrote:
> Michael Van Canneyt wrote:
> ...
> 
>> 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.
>>
> ...
> 
> 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 :).
> 

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.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: fcl_db.patch
Type: text/x-patch
Size: 5953 bytes
Desc: not available
URL: <http://lists.freepascal.org/pipermail/fpc-devel/attachments/20050320/c1c4da19/attachment.bin>