zMemtable

[marsupilami]

To make sure, we don't forget this, I created Ticket 543 on our bug tracker.

[marsupilami]

Delphi 10.3.3-Win32 Zeos8svn7719
Project3.zip

Hello Michal,

the problem here is that you have set Usemetadata to false. This is not supposed to work correctly because the dblib interface doesn't provide enough valid information for us to work correctly. I am not 100% sure about the mechanics but with UseMetadata = false Delphi tries to goet two memory blocks of more than 1GB in size. This most probably is too much for a 32 bits application on Windows. Please always set UseMetadata = true on the dblib drivers (mssql, sybase).

when setting UseMetadata to true, the output of your sample is:

a 1
b 1
c 1
Cannot access blob record in column 1 with type Bytes

So - this leaves us with the "Cannot access blob record in column 1 with type Bytes" error.

Best regards,

Jan

[miab3]

Hi Jan,

You're probably right.
In every table with which I have a problem in testing (during ZMemTable tests) FreeTDS has a BLOB field.

Michał

[aehimself]

So, the issue is NOT with ZMemTable / streaming. I got the same error while building the sample database:

CREATE TABLE 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)
)

INSERT INTO blob_values_new (b_id) VALUES (1)

Then attempting to load an image in the blob field with .LoadFromStream:

Code: Select all

An error happened while loading blob field from file:

EZSQLException was raised with the message Cannot access blob record in column 2 with type Bytes

Stack trace:
008ae571 MyApplication.exe ZDbcCachedResultSet 584 TZAbstractCachedResultSet.CreateLob
008afe30 MyApplication.exe ZDbcCachedResultSet 1531 TZAbstractCachedResultSet.GetBlob
00c87095 MyApplication.exe ZAbstractRODataset 5218 TZAbstractRODataset.CreateBlobStream
008378ec MyApplication.exe Data.DB         TBlobField.LoadFromStream
00837888 MyApplication.exe Data.DB         TBlobField.LoadFromFile
0100fbc0 MyApplication.exe uSQLConnectionFrame 1133 TSQLConnectionFrame.Uploadbinaryfromfile1Click
007a1cc4 MyApplication.exe Vcl.Menus        TMenuItem.Click
007a410e MyApplication.exe Vcl.Menus        TMenu.DispatchCommand
007a58a5 MyApplication.exe Vcl.Menus        TPopupList.WndProc
007a57a5 MyApplication.exe Vcl.Menus        TPopupList.MainWndProc
00556683 MyApplication.exe System.Classes      StdWndProc
7ffbe8c8 USER32.dll               DispatchMessageW
007cb36e MyApplication.exe Vcl.Forms        TApplication.ProcessMessage
007cb3e3 MyApplication.exe Vcl.Forms        TApplication.HandleMessage
007cb831 MyApplication.exe Vcl.Forms        TApplication.Run
0112fbd5 MyApplication.exe AEMultiSQL      164 initialization
7ffbe78d KERNEL32.DLL              BaseThreadInitThunk
7ffbe986 ntdll.dll                RtlUserThreadStart

Connection information: MsSQL 11.0.7507, database access component version: 8.0.0-d390f7dd4
Connected to database ___, schema dbo at host ___\SQLEXPRESS as user ___
Loaded library: C:\WINDOWS\sybdb.dll
Transaction isolation level: None
RawStringEncoding: DB_CP
codepage: UTF-8
timeout: 10
LobCacheMode: OnLoad

Will try to see what is going on.

[aehimself]

The issue simply disappears if the following modifications are made:

ZDbcCache.pas, TZRowAccessor.GetBlob
Change all 3

Code: Select all

if FColumnLengths[ColumnIndex] <= 0

to

Code: Select all

if FColumnLengths[ColumnIndex] > 0

ZDbcCachedResultSet.pas, TZAbstractCachedResultSet.CreateLob
Change both

Code: Select all

if RowAccessor.GetColumnLength( ColumnIndex) <= 0

to

Code: Select all

if RowAccessor.GetColumnLength( ColumnIndex) > 0

this way DbLib can upload and fetch blobs properly, in Oracle functionality is the same.

However I am completely unsure about this as I have no idea how LOBs are supposed to be handled. If the issue is generic, it should be present in all protocols, not just DbLib I think.

I can prepare a pull request to see what the test suite will say, if @Jan agrees.

[miab3]

Hi Jan, aehimself,

However, it is still bad in all three case ( varchar(max), nvarchar(max), varbinary(max)-FreeTDS):
a 1

b 1
ERROR2

c 1
ERROR2
ERROR3

Cannot access blob record in column 1 with type Bytes
==============================
The aehimself fix does not help much:
a 1

b 1
ERROR2

c 1
ERROR2
ERROR3

A 1
ERROR3
============================
And this is what it looks like for odbc_w:
a 1

b 1

c 1

A 1
===========================

The problem is most likely related to a defective field definition in ZTable/ZQuery when using mssql-FreeTDS?

Test program attached:

Project51.zip

Michał

[miab3]

Hi aehimself, Jan, All:

I explained a bit with FreeTDS.
I changed the line 3785 in zdbccache.pas from:

Code: Select all

stBytes: if FColumnLengths[ColumnIndex] <= 0

on:

Code: Select all

stBytes: if ((FColumnLengths[ColumnIndex] <= 0) or (FColumnLengths[ColumnIndex] = 2147483647) or (FColumnLengths[ColumnIndex] = 1073741823))

(From what I found max for Delphi and Lazarus)
And the blob problem is gone.
I suspect this is not the best place for a correction, as it concerns FreeTDS.
It seems to me that this should be generally solved for types (max) and FreeTDS(because after e.g. SET TEXTSIZE 100000000 the problem returns).
Such an excerpt from the M$ documentation:

SET TEXTSIZE { number }
Note

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments
number
Is the length of varchar(max), nvarchar(max), varbinary(max), text, ntext, or image data, in bytes. number is an integer with a maximum value of 2147483647 (2 GB). A value of -1 indicates unlimited size. A value of 0 resets the size to the default value of 4 KB.

The SQL Server Native Client (10.0 and higher) and ODBC Driver for SQL Server automatically specify -1 (unlimited) when connecting.

Drivers older than SQL Server 2008: The SQL Server Native Client ODBC driver and SQL Server Native Client OLE DB Provider (version 9) for SQL Server automatically set TEXTSIZE to 2147483647 when connecting.

Remarks
Setting SET TEXTSIZE affects the @@TEXTSIZE function.

The setting of set TEXTSIZE is set at execute or run time and not at parse time.

Michał

[marsupilami]

Hello Michal,

today I tried to add a bit of code that should correctly handle varchar(max) columns and treat them as Memo Fields. In the test case, I used, the problem is solved. I didn't try another test case (yet). Could you please retest with the current trunk if the problem still exists on FreeTDS?

Regarding ODBC/OLEDB/ADO, I assume, we have to add some code in these drivers to convert varchar(max) fields to stAsciiStrem or stUnicodeStream correctly. But maybe Egonhugeist already took care of this.

Best regards,

Jan

[miab3]

Hi Jan,

You didn't quite understand me.
The problem is that for FreeTDS, FColumnLengths[ColumnIndex] returns the current max limit for blob.
ODBC initially makes -1 of this and therefore has no problem with detecting and copying blobs.
After your recent changes(svn7752) to Delphi I still have to use max limt detection in line 3785 in zdbcache.pas(for FreeTDS).

But in Lazarus for odbc_w and SQLNCLI11 I also noticed a problem with the blob, this time elsewhere.
More precisely here:

Code: Select all

    Blob := FResultSet.GetBlob(ColumnIndex, TZLobStreamMode(Mode));
    if (Blob <> nil) then
      Blob.Open(TZLobStreamMode(Mode));
    if Blob <> nil then begin
      case Field.DataType of      // <<======================== Line5523 ZAbstractRODataset.pas
        {$IFDEF WITH_WIDEMEMO}
        ftWideMemo: begin
            Assert(Blob.QueryInterface(IZCLob, CLob) = S_OK);
            Result := Clob.GetStream(zCP_UTF16);
          end;
        {$ENDIF}  

ADDED:The same applies to FirebirdSQL and Table with Blob and Lazarus 2.0.12-Win64. When trying to SaveToStream Cloned to ZMemTable.
Both of the above goes without error in Delphi 10.3.3-Win32.


By the way, you paid attention to that?: viewtopic.php?f=50&t=150370

Michał

[miab3]

Hi Jan,

Summarizing
My old FreeTDS and ODBC test are currently passing identically and correctly.
It means:
a 1
a
a
a

b 1
b
b
b

c 1
c
c
c

A 1
A
A
A
======================
At present (svn7755), the problem actually remains the Lazarus BLOB behavior in combination with the ZMemTable.

Michał

[miab3]

Hi Jan, All,

But in Lazarus for odbc_w and SQLNCLI11 I also noticed a problem with the blob, this time elsewhere.
More precisely here:

Code: Select all

    Blob := FResultSet.GetBlob(ColumnIndex, TZLobStreamMode(Mode));
    if (Blob <> nil) then
      Blob.Open(TZLobStreamMode(Mode));
    if Blob <> nil then begin
      case Field.DataType of      // <<======================== Line5523 ZAbstractRODataset.pas
        {$IFDEF WITH_WIDEMEMO}
        ftWideMemo: begin
            Assert(Blob.QueryInterface(IZCLob, CLob) = S_OK);
            Result := Clob.GetStream(zCP_UTF16);
          end;
        {$ENDIF}  

ADDED:The same applies to FirebirdSQL and Table with Blob and Lazarus 2.0.12-Win64. When trying to SaveToStream Cloned to ZMemTable.
Both of the above goes without error in Delphi 10.3.3-Win32.

It turns out that this BLOB bug only occurs when running an application from inside Lazarus with a debugger.
The application itself appears to be working properly.
I checked it on Lazartus 2.2.0(final)-Win64 (Zeos8-svn7755,56,58)which has just been released.
This could be considered as a temporary inconvenience (pending possible clarification in the future).

Michał

[miab3]

In my opinion, in the mssql-dblib connection it should be possible to read, set and use TEXTSIZE.
For now, I uses in (Zeos8 trunk svn 7805)ZDbcCache.pas in line 3785:

Code: Select all

stBytes: if ((FColumnLengths[ColumnIndex] <= 0) or (FColumnLengths[ColumnIndex] = 2147483647) or (FColumnLengths[ColumnIndex] = 1073741823) or (FColumnLengths[ColumnIndex] = 10000000)) //M.A.

instead:

Code: Select all

stBytes: if FColumnLengths[ColumnIndex] <= 0

And it would be better

Code: Select all

stBytes: if ((FColumnLengths[ColumnIndex] <= 0) or (FColumnLengths[ColumnIndex] = CURRENT_TEXTSIZE))

But we would have to add TEXTSIZE manipulation functions for dblib.

Michał

[marsupilami]

Hello Michal,

I am not sure which code change you propose. Could you maybe create apatch file or create a pull request on Github?

Best regards,

Jan

[miab3]

Hi Jan,

What I meant was to introduce a variable that would hold the current TEXTSIZE value agreed with the MS SQL server for later use.
For example in line 3785 ZDbcCache.pas:
stBytes: if FColumnLengths[ColumnIndex] <= 0 or ...

Here's a bit about it:
https://database.guide/use-set-textsize ... ql-server/

Michal

[marsupilami]

Hello Michal,

I added some changes to Zeos Trunk and Zeos 8. Could you please check if these changes fix your problem ith the default value for TEXTZSIZE (2147483647)? If this fixes your problem, we can go on to add better handling for TEXTSIZE.

Best regards,

Jan