Hi
I managed to implement correct TField.IsNull behaviour for fields inside TIBQuery dataset. I'm attaching:
-- patch to packages/base/ibase/ibase60types.inc, this fixes a small bug in ibase60 unit that defined Short as Integer (= 32 bits, since ibase60 unit is compiled in objfpc mode), while it should be 16 bits.
-- patch to fcl/db/interbase/interbase.pp that
1) Fixes FFieldFlag declaration, it should be an array of IBase60.Short type (16 bits, not 8-bits ShortInt type as it was).
2) Implements correct TField.IsNull behaviour by recording "IsNull" state of each field of each record, along with data of that field. ThenTIBQuery.GetFieldData can be modified to return correct value of IsNull, and set Buffer^ only if field is not null and Buffer <> nil (since TField.GetIsNull calls TField.GetData with Buffer = Nil, and this causes call to FDataset.GetFieldData with Buffer = nil)
3) Oh, and small changes to TIBTransaction that I sent in my previous email this day (publishing 4 properties) are also included in this patch. Sorry, this was just easier for me, and since I didn't see them committed yet... Anyway, these changes to TIBTransaction are absolutely not related to all that "IsNull stuff", so feel free to cut them out if you don't like them.
-- I'm also attaching a trivial program using Interbase unit and some testing db to check that IsNull works as it should. Just in case it will save you some time in testing my patch :)
Michalis.
Index: ibase60types.inc
===================================================================
RCS file: /FPC/CVS/fpc/packages/base/ibase/ibase60types.inc,v
retrieving revision 1.2
diff -u -r1.2 ibase60types.inc
--- ibase60types.inc 4 Feb 2005 18:14:22 -0000 1.2
+++ ibase60types.inc 17 Mar 2005 00:15:54 -0000
@@ -14,7 +14,7 @@
Int = LongInt;
Long = LongInt;
- Short = Integer;
+ Short = SmallInt;
Float = Single;
{ Pointers to basic types }
Index: interbase.pp
===================================================================
RCS file: /FPC/CVS/fpc/fcl/db/interbase/interbase.pp,v
retrieving revision 1.15
diff -u -r1.15 interbase.pp
--- interbase.pp 14 Feb 2005 17:13:12 -0000 1.15
+++ interbase.pp 17 Mar 2005 00:26:20 -0000
@@ -175,6 +175,16 @@
{ Transaction must be assigned to some database session, for which purpose
you must use this property}
property Database : TIBDatabase read FDatabase write FDatabase;
+
+ { These four 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 ilReadCommitted;
+ property LockResolution: TLockResolution
+ read FLockResolution write FLockResolution default lrWait;
+ property TableReservation: TTableReservation
+ read FTableReservation write FTableReservation default trNone;
end;
{ TIBQuery }
@@ -195,7 +205,7 @@
FTransaction : TIBTransaction;
FDatabase : TIBDatabase;
FStatus : array [0..19] of ISC_STATUS;
- FFieldFlag : array [0..1023] of shortint;
+ FFieldFlag : array [0..1023] of IBase60.Short;
FBufferSize : integer;
FSQLDA : PXSQLDA;
FSQLDAAllocated : integer;
@@ -578,6 +588,18 @@
{ TIBQuery }
+type
+ { For now, we could simply say here that TFieldDataPrefix = boolean.
+ But making TFieldDataPrefix as record will be allow to very easy add
+ similar things like "IsNull" in the future.
+ Any information that has constant length, and should be
+ specified separately for every field of every row can be added as
+ another TFieldDataPrefix field. }
+ TFieldDataPrefix = record
+ IsNull: boolean;
+ end;
+ PFieldDataPrefix = ^TFieldDataPrefix;
+
procedure TIBQuery.SetTransaction(Value : TIBTransaction);
begin
CheckInactive;
@@ -748,6 +770,12 @@
begin
with FSQLDA^.SQLVar[x] do
begin
+ PFieldDataPrefix(Buffer)^.IsNull :=
+ { If 1st bit of SQLType is not set then field *cannot* be null,
+ and we shouldn't check SQLInd }
+ ((SQLType and 1) <> 0) and (SQLInd^ = -1);
+ Inc(Buffer, SizeOf(TFieldDataPrefix));
+
if ((SQLType and not 1) = SQL_VARYING) then
begin
Move(SQLData^, VarcharLen, 2);
@@ -785,11 +813,12 @@
x : integer;
begin
FRecordSize := 0;
- FBufferSize := 0;
{$R-}
for x := 0 to FSQLDA^.SQLD - 1 do
Inc(FRecordSize, FSQLDA^.SQLVar[x].SQLLen);
{$R+}
+ Inc(FRecordSize, SizeOf(TFieldDataPrefix) * FSQLDA^.SQLD);
+
FBufferSize := FRecordSize + SizeOf(TIBBookmark);
end;
@@ -963,29 +992,33 @@
{$R-}
if (Field.FieldName = FSQLDA^.SQLVar[x].SQLName) then
begin
- case Field.DataType of
- ftInteger :
- begin
- b := 0;
- Move(b, Buffer^, 4);
- Move(CurrBuff^, Buffer^, Field.Size);
- end;
- ftDate, ftTime, ftDateTime:
- GetDateTime(CurrBuff, Buffer, FSQLDA^.SQLVar[x].SQLType);
- ftString :
- begin
- Move(CurrBuff^, Buffer^, Field.Size);
- PChar(Buffer + Field.Size)^ := #0;
- end;
- ftFloat :
- GetFloat(CurrBuff, Buffer, Field);
- end;
+ Result := not PFieldDataPrefix(CurrBuff)^.IsNull;
- Result := True;
+ if Result and (Buffer <> nil) then
+ begin
+ Inc(CurrBuff, SizeOf(TFieldDataPrefix));
+ case Field.DataType of
+ ftInteger :
+ begin
+ b := 0;
+ Move(b, Buffer^, 4);
+ Move(CurrBuff^, Buffer^, Field.Size);
+ end;
+ ftDate, ftTime, ftDateTime:
+ GetDateTime(CurrBuff, Buffer, FSQLDA^.SQLVar[x].SQLType);
+ ftString :
+ begin
+ Move(CurrBuff^, Buffer^, Field.Size);
+ PChar(Buffer + Field.Size)^ := #0;
+ end;
+ ftFloat :
+ GetFloat(CurrBuff, Buffer, Field);
+ end;
+ end;
Break;
end
- else Inc(CurrBuff, FSQLDA^.SQLVar[x].SQLLen);
+ else Inc(CurrBuff, FSQLDA^.SQLVar[x].SQLLen + SizeOf(TFieldDataPrefix));
{$R+}
end;
end;
{ Create test db with SQL
create database 'test_isnull.fdb';
create table TestTable(Foo integer not null, Bar integer);
insert into TestTable(Foo) values(1);
insert into TestTable(Foo, Bar) values(222, 333);
insert into TestTable(Foo) values(44);
insert into TestTable(Foo, Bar) values(888, 999);
Then run this program with $1 = SYSDBA password, it should correctly
output table TestTable including null values.
}
{$mode delphi}
uses SysUtils, DB, Interbase;
function FieldToStr(F: TField): string;
begin
if F.IsNull then
Result := 'NULL' else
Result := IntToStr(F.AsInteger);
end;
var
Database: TIBDatabase;
Transaction: TIBTransaction;
Query: TIBQuery;
begin
Database := TIBDatabase.Create(nil);
try
Database.DatabaseName := 'test_isnull.fdb';
Database.UserName := 'SYSDBA';
Database.Password := ParamStr(1);
Database.Open;
try
Transaction := TIBTransaction.Create(nil);
try
Transaction.Database := Database;
Transaction.StartTransaction;
try
Query := TIBQuery.Create(nil);
try
Query.Database := Database;
Query.Transaction := Transaction;
Query.SQL.Text := 'select * from TestTable';
Query.Open;
try
while not Query.Eof do
begin
Writeln(FieldToStr(Query.Fields[0]), ' ',
FieldToStr(Query.Fields[1]));
Query.Next;
end;
finally Query.Close end;
finally Query.Free end;
finally Transaction.Rollback end;
finally Transaction.Free end
finally Database.Close end;
finally Database.Free end;
end._______________________________________________ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
