The offical for ZeosLib 7.3 Report problems, ask for help, post proposals for the new version of Zeoslib 7.3/v8
Quick Info:
-We made two new drivers: odbc(raw and unicode version) and oledb
-GUID domain/field-defined support for FB
-extended error infos of Firebird
-performance ups are still in queue
In future some more feature will arrive, so stay tuned and don't hassitate to help
kjteng wrote: ↑14.11.2021, 06:581. Line 722 (Savetostream), if we include ftstring in the first case (see below), the stream/file size will reduced significantly
Case field.DataType Of
ftString, ftMemo {$IFDEF WITH_WIDEMEMO}, ftWideMemo{$ENDIF}: WriteString(field.AsString); // <--add ftString
....
(LoadFromStream method also need to be changed accordingly)
Does Lazarus have TValueBuffer? If no, the only thing which should be done is the "compression" in the pointer version of the code. All fields, including strings will benefit from that. If yes, I don't think the difference would be that significant, and at least most fields are handled by the same code.
kjteng wrote: ↑14.11.2021, 06:582. Line 544, it will be safer to reset the position of Astream before reading the fieldcount (this is because the AStream passed to the method might not be in the 0 position)
None of the library methods reset the stream, that's the responsibility of the user. And the reason is exactly - the stream might not "start" at position 0. This way (in theory - didn't test it) you can save and reload multiple MemTables using the same stream:
Resetting (or rewinding) the stream would render the stream to one-use only. This way you can send a picture AND a dataset in the same stream, if you wish.
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
kjteng wrote: ↑14.11.2021, 06:581. Line 722 (Savetostream), if we include ftstring in the first case (see below), the stream/file size will reduced significantly
Case field.DataType Of
ftString, ftMemo {$IFDEF WITH_WIDEMEMO}, ftWideMemo{$ENDIF}: WriteString(field.AsString); // <--add ftString
....
(LoadFromStream method also need to be changed accordingly)
Does Lazarus have TValueBuffer? If no, the only thing which should be done is the "compression" in the pointer version of the code. All fields, including strings will benefit from that. If yes, I don't think the difference would be that significant, and at least most fields are handled by the same code.
Attached two files for your reference.
1. test2.dat (71k) (using your original code);
2. test3.dat (16k) (after changing the code to ' Case field.DataType Of ftString, ...')
procedure TForm1.bt1ReadClick(Sender: TObject);
var ms: TMemoryStream;
begin
ms := TMemoryStream.Create;
try
ms.LoadFromFile('test.dat');
zmemtable1.LoadFromStream(ms);
finally
ms.Free;
end;
end;
procedure TForm1.bt2SaveClick(Sender: TObject);
var ms: TMemoryStream;
begin
ms := TMemoryStream.Create;
try
zmemtable1.SaveToStream(ms);
ms.SaveToFile('test3.dat');
finally
ms.Free;
end;
end;
Did I use it in a correct way?
BTW, What do you mean by '"compression" in the pointer version of the code'. Is there another version of zMemtable which can compress the stream?
(actually I have created a helper to compress the stream but still under testing)
You do not have the required permissions to view the files attached to this post.
aehimself wrote: ↑14.11.2021, 18:22
None of the library methods reset the stream, that's the responsibility of the user. And the reason is exactly - the stream might not "start" at position 0. This way (in theory - didn't test it) you can save and reload multiple MemTables using the same stream:
Resetting (or rewinding) the stream would render the stream to one-use only. This way you can send a picture AND a dataset in the same stream, if you wish.
Ok. Now I see the reason why you didnt reset the position of AStream. I asked for this is because I have added methods to Save the stream to Blob field and to compress the stream. Both methods actually moved the position of the stream and caused problem in subsequent loading of the stream. So I have to change my codes to reset the position before I exit the two methods (saveToBlob and zip/unzip stream) .
It is a pity that there is no compatibility of the SaveToFile / LoadFromFile format of streams between systems and compilers.
Therefore, they are not transferable and shared.
As per Jan's request I'm already looking into the output format of TClientDataSet's XML.
Once I have the format clear I can switch up the current binary method completely OR make a parameter to define if SaveToStream should be binary or XML.
That would be extra nice as data could be exchanged between a TClientDataSet and ZMemTable.
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
I have noticed one problem with the ZMemTable
Applies to MS SQL and FreeTDS (in any version up to 1.3).
Loads MS SQL via FreeTDS and clones tables with a blob field to the ZMemTable.
When trying to SaveTo File (SaveToStream), this ZMemTable always throws an error.
There is no problem when connecting via ODBC or OleDB.
Is there any way to remedy this?
Attach a picture:
fre2.png
Michał
You do not have the required permissions to view the files attached to this post.
Is it possible for you to extract the FieldDefs of the ZMemTable and maybe the contents of the first record (maybe via .AsBytes?)
It would be useful to compare the fielddefs and the contents of the blob field if you clone via FreeTDS and ODBC.
It'd also help to know which IDE you used.
Edit: The following code compiles, runs and produces an uncorrupted ZIP file on D10.4.2 and on 7:
odbc-w-SQL Server Native Client 11.0
ZQuery:
After Open:
pub_id;WideString(24);4;-1;
logo;Blob(15);0;0;
pr_info;WideMemo(39);0;0;
ZMemTable:
After Clone:
pub_id;WideString(24);4;-1;
logo;Blob(15);0;0;
pr_info;WideMemo(39);0;0;
After Save/Load:
pub_id;WideString(24);4;-1;
logo;Blob(15);0;0;
pr_info;WideMemo(39);0;0;
mssql-FreeTDS v 1.3:
After Open:
pub_id;WideString(24);4;-1;
logo;Blob(15);0;0;
pr_info;WideMemo(39);0;0;
After Clone:
pub_id;WideString(24);4;-1;
logo;Blob(15);0;0;
pr_info;WideMemo(39);0;0;
Try SaveToFile(SaveToStream)
Cannot access blob record in column 1 with type Bytes
It seems to me the problem is earlier.
When opening a table from MS SQL via FreeTDS.
The file generated by the ODBC connection is loaded and written to the ZMemTable without any problems.
I noticed that an error
'Cannot access blob record in column n with type Bytes'
occurs for a type
image
There is also an error
'Range check error'
and it looks like for the type
nvarchar(max)
and maybe
varbinary(max)
This is an example MS SQL table that causes this error:
CategoryID;Integer(3);0;0;
CategoryName;WideString(24);30;-1;
Description;WideString(24);2147483646;0; --nvarchar(max)
Picture;Blob(15);0;0;
The same for ODBC
CategoryID;Integer(3);0;0;
CategoryName;WideString(24);15;-1;
Description;WideMemo(39);0;0;
Picture;Blob(15);0;0;
These could be some of the limitations of FreeTDS.
As if the data length could not be determined.
Maybe Jan knows more?
After more thorough testing, for mssql FreeTDS:
MS SQL type image is loaded incorrectly into ZTable / ZQuery and cloned to ZMemTable.
MS SQL type nvarchar(max) is defectively copied / cloned to ZMemTable from ZTable, ZQuery.
In the case of odbc_w, everything is fine (including LoadFromFile to ZMemTable).
Michał
Last edited by miab3 on 21.11.2021, 20:23, edited 1 time in total.
unfortunately I don't have much time to spare for Zeos currently. Could you please try to create an example application that exhibits the error - including an SQL create script for a test database? That really would help in tracking things down.
Regarding data length and FreeTDS: The dblib API tells the application the actual length for the contents of a field. Zeos uses that information to store a full copy of the result set in memory. Metadata is derived from the dblib api to some degree but also very much from metadata that we query from the SQL server. The dblib api isn't very informative in that regard and nobody had the time (yet) to build a ctlib driver. So if we have a problem with these metadata queries things might become unpleasent.
If we have a working test application it might be a question of minutes or hours to fix the problem.
Also now, unfortunately, I don't have much time.
In general, I am concerned that the following data types(with data) may be buggy loaded into ZQuery / ZTable using mssql-FreeTDS:
NTEXT, TEXT and IMAGE
NVARCHAR(max), VARCHAR(max), and VARBINARY(max)
And this is sometimes revealed only when trying to clone to ZMemTabla and trying to SaveToStream / LoadFromStream.
ADD: Sample MSSQL table with problems with FeeTDS: