zMemtable

[aehimself]

1. 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.

2. 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:

Code: Select all

ms := TMemoryStream.Create;
Try
 ZMemTable1.SaveToStrem(ms);
 ZMemTable2.SaveToStrem(ms);
 ZMemTable3.SaveToStrem(ms);

 ZMemTable1.Close;
 ZMemTable2.Close;
 ZMemTable3.Close;

 ZMemTable1.LoadFromStream(ms);
 ZMemTable2.LoadFromStream(ms);
 ZMemTable3.LoadFromStream(ms);
Finally
 FreeAndNil(ms);
End;

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.

[kjteng]

1. 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, ...')

This is my code to read/write the zmemtable:

Code: Select all

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)

[kjteng]

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) .

[miab3]

Hi aehimself, All,

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.

Michał

[aehimself]

Michal,

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.

[miab3]

Hi All,

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ł

[aehimself]

@ Michal,

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:

Code: Select all

Var
 ms: TMemoryStream;
begin
 ZMemTable1.FieldDefs.Add('Test', ftBlob);

 ZMemTable1.Open;

 ZMemTable1.Append;
 (ZMemTable1.FieldByName('Test') As TBlobField).LoadFromFile('C:\LocalWork\EdgeDockInsidePanel.zip');
 ZMemTable1.Post;

 ms := TMemoryStream.Create;
 Try
  ZMemTable1.SaveToStream(ms);

  ms.Position := 0;

  ZMemTable1.Close;

  ZMemTable1.LoadFromStream(ms);
 (ZMemTable1.FieldByName('Test') As TBlobField).SaveToFile('C:\LocalWork\EdgeDockInsidePanel1.zip');
 Finally
  FreeAndNil(ms);
 End;

[miab3]

Hi,

Code: Select all

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

Delphi 10.3.3-Win32 Zeos8-trunk7719

Michał

[aehimself]

@Michal,

Can you please extract the data in your blob field and attempt to run the following?

Code: Select all

Var
 ms: TMemoryStream;
begin
 ZMemTable1.FieldDefs.Add('pub_id', ftWideString, 24, True);
 ZMemTable1.FieldDefs.Add('logo', ftBlob, 15);
 ZMemTable1.FieldDefs.Add('pr_info', ftWideMemo, 39);

 ZMemTable1.Open;

 ZMemTable1.Append;
 ZMemTable1.FieldByName('pub_id').AsString := '0736';
 (ZMemTable1.FieldByName('logo') As TBlobField).LoadFromFile();
 ZMemTable1.FieldByName('pr_info').AsString := 'Hello, world';
 ZMemTable1.Post;

 ms := TMemoryStream.Create;
 Try
  ZMemTable1.SaveToStream(ms);

  ms.Position := 0;

  ZMemTable1.Close;

  ZMemTable1.LoadFromStream(ms);
 Finally
  FreeAndNil(ms);
 End;

Does the issue appear at the first record?

[miab3]

Hi aehimself, Jan,

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?

Michał

[miab3]

Hi aehimself, Jan, All:

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ł

[marsupilami]

Hello Michal,

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.

Best regards,

Jan

[miab3]

Hi,

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:

Code: Select all

CREATE TABLE dbo.blob_values_new (
  b_id int NOT NULL,
  b_text varchar(max) COLLATE Polish_CI_AS NULL,
  b_image varbinary(max) NULL,
  PRIMARY KEY CLUSTERED (b_id)
    WITH (
      PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
      ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
ON [PRIMARY]
GO

Michał

[miab3]

Hi,

Sample application running for odbc_w:
1
1

and a causing error for FreeTDS:
1
Out of memory

Delphi 10.3.3-Win32 Zeos8svn7719

Project2.zip

Michał

[miab3]

Hi Jan, aehimself, All,

A better example varchar(10), varchar(max), nvarchar(max), varbinary(max)
odbc_w:
a 1
b 1
c 1
A 1

mssql-FreeTDS:
a 1
-Out of memory
-Out of memory
-Cannot access blob record in column 1 with type Bytes

Delphi 10.3.3-Win32 Zeos8svn7719

Project3.zip

Michał