zMemtable
Re: zMemtable
I managed to put together a stream loading / saving method which MIGHT work with newer and older versions too but I simply cannot test the one without WITH_TVALUEBUFFER. An other thing I don't like about this method is that it writes all leading zeroes, which increases the data size from 191 to 298 bytes. It eliminated the reading and writing fields as TBytes so it should work. In theory.
I opened a pull request on GitHub. Once accepted, you have to define ZMEMTABLE_ENABLE_STREAM_EXPORT_IMPORT in your project, otherwise these two methods are invisible.
Please test it, especially with older Delphi versions and Lazarus.
Once it seems to be stable enough we can get rid of the conditional directive and allow everyone to use this feature.
I opened a pull request on GitHub. Once accepted, you have to define ZMEMTABLE_ENABLE_STREAM_EXPORT_IMPORT in your project, otherwise these two methods are invisible.
Please test it, especially with older Delphi versions and Lazarus.
Once it seems to be stable enough we can get rid of the conditional directive and allow everyone to use this feature.
Delphi 12.1, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Re: zMemtable
Hi ashimself,
The procedure "SaveToFile from i to j" and "MergeFromFile from i to j" with and without fielddefs would be handy.
Michał
The procedure "SaveToFile from i to j" and "MergeFromFile from i to j" with and without fielddefs would be handy.
Michał
Re: zMemtable
"From i to j" means fields or records? :)
Delphi 12.1, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Re: zMemtable
Records.
But you threw up an even more interesting idea of extracting specific records from the indicated fields.
Such a microSQL.
Michał
But you threw up an even more interesting idea of extracting specific records from the indicated fields.
Such a microSQL.
Michał
Re: zMemtable
Hi aehimself,
I hv tried your code on lazarus 2.3.0 trunk version.
SavetoStream: integer and float field is not saved to stream correctly
LoadFromStream: get 'Invalid type conversion to Bytes in fields XXX' error, probably Fields.AsBytes := ReadBytes; doesnt work in Lazarus.
I hv tried your code on lazarus 2.3.0 trunk version.
SavetoStream: integer and float field is not saved to stream correctly
LoadFromStream: get 'Invalid type conversion to Bytes in fields XXX' error, probably Fields.AsBytes := ReadBytes; doesnt work in Lazarus.
Re: zMemtable
Kjteng,
That was my assumption too - as I mentioned I remember having the exact same issue with Michael.
You can wait until the pull request is accepted and merged back to SubVersion, or you can try to copy the new version using .GetData and .SetData. I hope it works :)
That was my assumption too - as I mentioned I remember having the exact same issue with Michael.
You can wait until the pull request is accepted and merged back to SubVersion, or you can try to copy the new version using .GetData and .SetData. I hope it works :)
Delphi 12.1, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Re: zMemtable
I have modified your code by using string instead of TBytes type, it seems to work on lazarus/win (I am unable to test it on other platform). For the time being I have coded the two procedures outside the ZMemTable.pas instead of zMemtable methods (so that I dont have to keep reinstalling the component for testing purposes), as follows:
Code: Select all
procedure mtLoadFromStream(mt: TZMemTable; AStream: TStream);
function ReadInt: longint;
begin
AStream.Read(result, Sizeof(longint));
end;
var
len, ftype, a, b, kk: Integer; tx: string; req: Boolean;
begin
with mt, AStream do
begin
//CheckInactive;
mt.Close;
FieldDefs.Clear;
DisableControls;
AStream.Position:= 0;
kk := ReadInt; //fieldcount
try
for a := 0 To kk - 1 do
begin
tx := ReadAnsiString;
ftype := ReadInt;
len := ReadInt;
req := ReadInt > 0; // required field if boolean value > 0
FieldDefs.Add(tx, TFieldType(fType), len, req);
end;
Open; // Open - setup fields structure for memtable
kk := ReadInt; // recordcount
for a := 0 to kk-1 do
begin
mt.Append;
for b := 0 to FieldCount - 1 do
begin
tx := AStream.ReadAnsiString;
Fields[b].AsString := tx
end;
mt.Post;
end;
First;
finally
EnableControls;
end;
end;
end;
procedure mtSaveToStream(mt: TZMemTable; AStream: TStream);
procedure WriteInt(ii: longint);
begin
AStream.Write(ii, Sizeof(longint))
end;
var bm: TBookMark; a: Integer;
begin
with mt, AStream do
begin
if not active then exit;
// CheckActive;
bm := GetBookmark;
AStream.Position:= 0;
try
DisableControls;
try
WriteInt(FieldDefs.Count);
for a := 0 To FieldDefs.Count - 1 do
begin
WriteAnsiString(FieldDefs[a].Name);
WriteInt(Integer(FieldDefs[a].DataType));
WriteInt(FieldDefs[a].Size);
WriteInt(ord(FieldDefs[a].Required));
end;
First;
WriteInt(RecordCount);
while not Eof Do
begin
for a := 0 To FieldCount - 1 Do
WriteAnsiString(Fields[a].AsString);
Next;
end;
finally
EnableControls;
end;
finally
GotoBookmark(bm);
end;
end;
end;
Re: zMemtable
Depending on your application it might be sufficient, but copying data as String will fail in most data types, especially if the format settings are different.
E.g.: you export column PI as "3.1415" but the system which loads the stream expects "3,1425".
Or you export a date with "01/01/79" but the system which would load the data expects "1979.01.01.".
Blobs or any kind of binary data will most probably get corrupted if attempted to be converted to String.
This is why I used TBytes before and experimented with GetData / SetData now to be able to access the native field buffer. I hope that this way we can be older Delphi / Lazarus compatible while keeping the exported data compatible between any number of systems running the same method.
E.g.: you export column PI as "3.1415" but the system which loads the stream expects "3,1425".
Or you export a date with "01/01/79" but the system which would load the data expects "1979.01.01.".
Blobs or any kind of binary data will most probably get corrupted if attempted to be converted to String.
This is why I used TBytes before and experimented with GetData / SetData now to be able to access the native field buffer. I hope that this way we can be older Delphi / Lazarus compatible while keeping the exported data compatible between any number of systems running the same method.
Delphi 12.1, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Re: zMemtable
Yes you are absolutely right.Hope that we will have a more comprehensive solution later. Meanwhile I will copy the data as string because my apps only involves string, integer and float (two decimal placesprecison).
Re: zMemtable
Hi dear experts and masters,
I finally managed to use getdata and setdatato save/load most of the simple data (integer, float, date, string) but not memo and blob field.
May I know how can I get the memo field data using getdata (or other method) ? (I noticed that the datasize of the memofield is 0 eventhough it contains a long string).
Would appreciate it if can give me some pointers.
I finally managed to use getdata and setdatato save/load most of the simple data (integer, float, date, string) but not memo and blob field.
May I know how can I get the memo field data using getdata (or other method) ? (I noticed that the datasize of the memofield is 0 eventhough it contains a long string).
Would appreciate it if can give me some pointers.
Re: zMemtable
Good point there, I'll need to update my method too.
Edit: unfortunately it seems GetData does not work on BLOB fields, it's simply not coded (see the Case statement in ZAbstractRODataSet : 2708; TZAbstractRODataset.GetFieldData).
It wouldn't be hard to code it, but I'd like to hear from Michael first... is this behavior normal?
On the other hand, if we already need separate handling of BLOB fields we simply can extract the data via a stream (or TBytes with TBlobField.Value).
Code: Select all
function TBlobField.GetDataSize: Integer;
begin
// Blob data is not stored in the record buffer and can not be read
// with a call to TField.GetData. Use GetBlobSize instead.
Result := 0;
end;
Code: Select all
If ZMemTable1.FieldByName('Test').IsBlob Then
a := (ZMemTable1.FieldByName('Test') As TBlobField).BlobSize
Else
a := ZMemTable1.FieldByName('Test').DataSize;
It wouldn't be hard to code it, but I'd like to hear from Michael first... is this behavior normal?
On the other hand, if we already need separate handling of BLOB fields we simply can extract the data via a stream (or TBytes with TBlobField.Value).
Delphi 12.1, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Re: zMemtable
The pull request was extended with the commit to fix data reading / writing of blob fields.
I went with the stream method, seems to work:
I went with the stream method, seems to work:
Code: Select all
begin
ZMemTable1.FieldDefs.Add('Test', ftWideMemo);
ZMemTable1.Open;
ZMemTable1.Append;
ZMemTable1.FieldByName('Test').AsString := 'Hello';
ZMemTable1.Post;
ms := TMemoryStream.Create;
Try
ZMemTable1.SaveToStream(ms);
ZMemTable1.Close;
ms.Position := 0;
ZMemTable1.LoadFromStream(ms);
Finally
FreeAndNil(ms);
End;
Delphi 12.1, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Re: zMemtable
Can some with LCL tell me what Lazarus wants if the compilation failed with:
So it expected an endif, which it found and then unexpected EOF...?
I downloaded and installed Lazarus but it compiles the package for me (at least I think... Compile button gets disabled for a while and then no message, nothing).
Attached is the file which just got accepted.
Code: Select all
17:53:44 ZMemTable.pas(787) Error: (2017) $ENDIF expected for $IFDEF ZMEMTABLE_ENABLE_STREAM_EXPORT_IMPORT defined in ZMemTable.pas line 640
17:53:44 ZMemTable.pas(787) (2033) ENDIF ZMEMTABLE_ENABLE_STREAM_EXPORT_IMPORT found
17:53:44 ZMemTable.pas(787) Fatal: (2000) Unexpected end of file
I downloaded and installed Lazarus but it compiles the package for me (at least I think... Compile button gets disabled for a while and then no message, nothing).
Attached is the file which just got accepted.
You do not have the required permissions to view the files attached to this post.
Delphi 12.1, Zeos 8 from latest GIT snapshot
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Using:
- MySQL server 8.0.18; libmariadb.dll 3.3.8
- Oracle server 11.2.0, 12.1.0, 19.0.0; oci.dll 21.13
- MSSQL 2012, 2019; sybdb.dll FreeTDS_2435
- SQLite 3.45.2
Re: zMemtable
I do not know the best /most efficient way to do it..... so I tried with my limited knowledge and skill. This is my codes after few days of trying. Now it works for Integer/date/float/string/memo/blob fields. Please guide me how to improve it. Thank you.
Code: Select all
procedure TZAbstractMemTable.LoadFromStream(AStream: TStream);
var
len, ftype, a, b, cc, kk: Integer; tx: string; req: Boolean;
ms: TMemoryStream; tb: TBytes;
function ReadInt: longint;
begin
AStream.ReadBuffer(result, Sizeof(longint));
end;
function ReadStr: string;
begin
cc := ReadInt;
SetLength(result, cc);
AStream.Readbuffer(Pointer(result)^, cc);
end;
procedure ReadBlob;
begin
ms := TMemoryStream.Create;
ms.CopyFrom(AStream, abs(cc));
(Fields[b] as TBlobField).LoadFromStream(ms);
ms.Free;
end;
procedure ReadData;
begin
SetLength(tb, cc);
AStream.ReadBuffer(Pointer(tb)^, cc);
Fields[b].SetData(Pointer(tb));
end;
begin
CheckInactive;
//Close;
FieldDefs.Clear;
DisableControls;
AStream.Position:= 0;
kk := ReadInt; //fieldcount
try
for a := 0 To kk - 1 do
begin
//tx := AStream.ReadAnsiString;
tx := ReadStr;
ftype := ReadInt;
len := ReadInt;
req := ReadInt > 0; // required field if boolean value > 0
FieldDefs.Add(tx, TFieldType(fType), len, req);
end;
Open; // Open - setup fields structure for memtable
kk := ReadInt; // recordcount
for a := 0 to kk-1 do
begin
Append;
for b := 0 to FieldCount - 1 do
begin
cc := ReadInt;
case cc of
-maxint..-1: ReadBlob;
0: ; //No data - ignore
1..1000: ReadData; //simple type
1001: Fields[b].AsString := ReadStr;
end; //case
end;
Post;
end;
First;
finally
EnableControls;
end;
// end;
end;
procedure TZAbstractMemTable.SaveToStream(AStream: TStream);
var bm: TBookMark; a, cc: Integer;
tb: TBytes;
ms: TMemoryStream;
procedure WriteInt(ii: longint);
begin
AStream.WriteBuffer(ii, Sizeof(longint))
end;
procedure WriteStr(tx: string);
begin
cc := Length(tx); WriteInt(cc);
AStream.WriteBuffer(Pointer(tx)^, cc);
end;
procedure WriteBlob;
var ms: TStream;
begin
ms:= TMemoryStream.Create;
(Fields[a] as TBlobField).SaveToStream(ms);
cc := -ms.Size; // -ve to indicate BLOB type
WriteInt(cc);
if cc < 0 then
begin
ms.Position := 0;
AStream.CopyFrom(ms, abs(cc));
end;
ms.Free;
end;
procedure WriteData;
begin
cc := Fields[a].DataSize;
SetLength(tb, cc);
Fields[a].GetData(Pointer(tb));
WriteInt(cc);
AStream.Writebuffer(Pointer(tb)^, cc);
end;
begin
CheckActive;
bm := GetBookmark;
AStream.Position:= 0;
try
DisableControls;
try
WriteInt(FieldDefs.Count);
for a := 0 To FieldDefs.Count - 1 do
begin
WriteStr(FieldDefs[a].Name);
WriteInt(Integer(FieldDefs[a].DataType));
WriteInt(FieldDefs[a].Size);
WriteInt(ord(FieldDefs[a].Required));
end;
First;
WriteInt(RecordCount);
while not Eof Do
begin
for a := 0 To FieldCount - 1 Do
case Fields[a].DataType of
ftString, ftMemo:
begin
cc := 1001; WriteInt(cc); // 1001 indicate that string type
WriteStr(Fields[a].AsString);
end;
ftBlob: WriteBlob;
else WriteData
end; //case
Next;
end;
finally
EnableControls;
end;
finally
GotoBookmark(bm);
end;
end;
procedure TZAbstractMemTable.LoadFromFile(Filename: TFilename);
var F1: TFileStream;
begin
F1 := TFileStream.Create(Filename, fmOpenRead + fmShareCompat);
try
Self.LoadFromStream(F1);
finally;
F1.Free;
end;
end;
procedure TZAbstractMemTable.SaveToFile(Filename: TFilename);
var F1: TFileStream;
begin
F1 := TFileStream.Create(Filename, fmCreate);
try
Self.SaveToStream(F1);
finally
F1.Free;
end;
end;
Re: zMemtable
Are you sure you have attached the right file. I copied your file to my pc (overwrite the original pas file) and tried with a simple program (cloneDataFrom another zquery dataset). No problem to compile and run.aehimself wrote: ↑04.11.2021, 18:38 Can some with LCL tell me what Lazarus wants if the compilation failed with:
So it expected an endif, which it found and then unexpected EOF...?Code: Select all
17:53:44 ZMemTable.pas(787) Error: (2017) $ENDIF expected for $IFDEF ZMEMTABLE_ENABLE_STREAM_EXPORT_IMPORT defined in ZMemTable.pas line 640 17:53:44 ZMemTable.pas(787) (2033) ENDIF ZMEMTABLE_ENABLE_STREAM_EXPORT_IMPORT found 17:53:44 ZMemTable.pas(787) Fatal: (2000) Unexpected end of file
I downloaded and installed Lazarus but it compiles the package for me (at least I think... Compile button gets disabled for a while and then no message, nothing).
Attached is the file which just got accepted.