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.
cvs server: Diffing .
Index: db.pp
===================================================================
RCS file: /FPC/CVS/fpc/fcl/db/db.pp,v
retrieving revision 1.38
diff -u -r1.38 db.pp
--- db.pp 16 Feb 2005 09:31:58 -0000 1.38
+++ db.pp 18 Mar 2005 03:39:48 -0000
@@ -1264,7 +1264,6 @@
FDatabase : TDatabase;
FDataSets : TList;
FOpenAfterRead : boolean;
- Procedure SetDatabase (Value : TDatabase);
Function GetDataSetCount : Longint;
Function GetDataset(Index : longint) : TDBDataset;
procedure RegisterDataset (DS : TDBDataset);
@@ -1272,6 +1271,7 @@
procedure RemoveDataSets;
procedure SetActive(Value : boolean);
Protected
+ Procedure SetDatabase (Value : TDatabase); virtual;
procedure CloseTrans;
procedure openTrans;
Procedure CheckDatabase;
cvs server: Diffing dbase
cvs server: Diffing interbase
cvs server: Diffing memds
cvs server: Diffing mysql
cvs server: Diffing odbc
cvs server: Diffing sdf
cvs server: Diffing sqldb
Index: sqldb/sqldb.pp
===================================================================
RCS file: /FPC/CVS/fpc/fcl/db/sqldb/sqldb.pp,v
retrieving revision 1.14
diff -u -r1.14 sqldb.pp
--- sqldb/sqldb.pp 14 Feb 2005 17:13:12 -0000 1.14
+++ sqldb/sqldb.pp 18 Mar 2005 03:39:48 -0000
@@ -116,6 +116,7 @@
FAction : TCommitRollbackAction;
protected
function GetHandle : Pointer; virtual;
+ procedure SetDatabase(Value: TDatabase); override;
public
procedure Commit; virtual;
procedure CommitRetaining; virtual;
@@ -126,6 +127,27 @@
destructor Destroy; override;
property Handle: Pointer read GetHandle;
procedure EndTransaction; override;
+
+ { Use this to get access to transaction properties/methods that are
+ specific to some TSQLConnection descendant, e.g. when
+ Database is TIBConnection then this SQLHandle returns always
+ an instance of class TIBTrans.
+
+ Notes:
+ - You must assign Database before using this property
+ - Each time you change Database property, you're causing
+ new underlying instance of SQLHandle to be created
+ and old one to be destroyed. That's because different
+ values of Database property may even mean different
+ TSQLHandle descendants that will be used here,
+ e.g. if Database is TIBConnection that SQLHandle is TIBTrans,
+ but if Database is TPQConnection then SQLHandle is TPQTrans.
+
+ This means that if you change some properties of SQLHandle
+ (e.g. IsolationLevel in case SQLHandle is TIBTrans)
+ and then you change Database property then your changes to
+ SQLHandle will be lost. }
+ function SQLHandle: TSQLHandle;
published
property Action : TCommitRollbackAction read FAction write FAction;
property Database;
@@ -309,6 +331,12 @@
Result := (Database as tsqlconnection).GetTransactionHandle(FTrans);
end;
+procedure TSQLTransaction.SetDatabase(Value: TDatabase);
+begin
+ if Value <> Database then FreeAndNil(FTrans);
+ inherited;
+end;
+
procedure TSQLTransaction.Commit;
begin
if active then
@@ -355,14 +383,13 @@
if Active then
DatabaseError(SErrTransAlreadyActive);
+ { This will create FTrans if necessary, this also checks that Database <> nil }
+ SQLHandle;
+
db := (Database as tsqlconnection);
- if Db = nil then
- DatabaseError(SErrDatabasenAssigned);
-
if not Db.Connected then
Db.Open;
- if not assigned(FTrans) then FTrans := Db.AllocateTransactionHandle;
if Db.StartdbTransaction(FTrans) then OpenTrans;
end;
@@ -378,6 +405,20 @@
inherited Destroy;
end;
+function TSQLTransaction.SQLHandle: TSQLHandle;
+{ Return FTrans, creating it first if necessary }
+var db : TSQLConnection;
+begin
+ db := (Database as tsqlconnection);
+
+ if Db = nil then
+ DatabaseError(SErrDatabasenAssigned);
+
+ if not assigned(FTrans) then FTrans := Db.AllocateTransactionHandle;
+
+ Result := FTrans;
+end;
+
{ TSQLQuery }
procedure TSQLQuery.SetDatabase(Value : TDatabase);
@@ -618,8 +659,21 @@
begin
// Todo: If there is more then one field in the key, that must be parsed
s := indexdefs[tel].fields;
- F := fieldbyname(s);
- F.ProviderFlags := F.ProviderFlags + [pfInKey];
+ F := FindField(s);
+
+ { Using FindField and checking is F<>nil
+ (instead of using FieldByName) is *not* only a workaround
+ for this "Todo: If there is more then one field ..." above.
+
+ Even if S consists of only one field, there still is
+ a chance that this field is not available in our dataset.
+ Consider e.g. when I do `select Xyz from SomeTable'
+ where SomeTable is defined as
+ `(Foo integer primary key, Xyz integer)'.
+ Then S above will be 'Foo', but there is no field 'Foo'
+ in my dataset ! }
+ if F <> nil then
+ F.ProviderFlags := F.ProviderFlags + [pfInKey];
end;
end;
end;
cvs server: Diffing sqldb/interbase
Index: sqldb/interbase/ibconnection.pp
===================================================================
RCS file: /FPC/CVS/fpc/fcl/db/sqldb/interbase/ibconnection.pp,v
retrieving revision 1.12
diff -u -r1.12 ibconnection.pp
--- sqldb/interbase/ibconnection.pp 14 Feb 2005 17:13:12 -0000 1.12
+++ sqldb/interbase/ibconnection.pp 18 Mar 2005 03:39:49 -0000
@@ -31,14 +31,24 @@
end;
TIBTrans = Class(TSQLHandle)
- protected
+ private
TransactionHandle : pointer;
TPB : string; // Transaction parameter buffer
Status : array [0..19] of ISC_STATUS;
- AccessMode : TAccessMode;
- IsolationLevel : TIsolationLevel;
- LockResolution : TLockResolution;
- TableReservation : TTableReservation;
+ FAccessMode : TAccessMode;
+ FIsolationLevel : TIsolationLevel;
+ FLockResolution : TLockResolution;
+ FTableReservation : TTableReservation;
+ public
+ { Values of these properties will be used in next StartTransaction calls }
+ property AccessMode: TAccessMode
+ read FAccessMode write FAccessMode default amReadWrite;
+ property IsolationLevel: TIsolationLevel
+ read FIsolationLevel write FIsolationLevel default ilConcurrent;
+ property LockResolution: TLockResolution
+ read FLockResolution write FLockResolution default lrWait;
+ property TableReservation: TTableReservation
+ read FTableReservation write FTableReservation default trNone;
end;
TIBConnection = class (TSQLConnection)
cvs server: Diffing sqldb/mysql
cvs server: Diffing sqldb/postgres
cvs server: Diffing sqlite
cvs server: Diffing tests
_______________________________________________ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
