binary fields are not updated correctly

[hoedlmoser]

Hello,
I am using zeoslib 7.1.4-stable, protocol = "FreeTDS_MsSQL-2000" + "msdblibr.dll" and got some trouble with binary fields. If the binary/varbinary value is null, it will not be updated. The varbinary value will be truncated to the size it had before the update.

The table definition is

Code: Select all

create table BinTest (
  id_BinTest int identity(1, 1) not null primary key,
  Modified datetime not null default (getdate()),
  BinTest binary(32),
  VarBin varbinary(32)
)
go
insert BinTest (BinTest, VarBin) values (0x0, 0x0)
insert BinTest (BinTest, VarBin) values (NULL, NULL)
insert BinTest (BinTest, VarBin) values (NULL, 0x0)
go

Lazarus code

Code: Select all

procedure TfrmMain.Button1Click(Sender: TObject);
var
  q: tZQuery;
  buf: array[0..31] of byte;
begin
  Screen.Cursor:= crHourglass;
  FillChar(buf, SizeOf(buf), $FF);
  q:= tZQuery.Create(nil);
  q.Connection:= ZConnection1;  
  q.SQL.Text:= 'select * from BinTest';
  try
    q.Open;
    while not q.EOF do begin
      q.Edit;   // Inserts are working properly !!!
      q.FieldByName('Modified').AsDateTime:= Now;
      q.FieldByName('BinTest').SetData(@buf);
      q.FieldByName('VarBin').SetData(@buf);
      // This never raises:
      if q.FieldByName('BinTest').IsNull then raise Exception.Create('*F*!!!'); 
      q.Post;
      q.Next;
    end;
  finally
    Screen.Cursor:= crDefault;
    q.Free;
  end;
end;

And this is what the "Post" method does (copied from wireshark):

Code: Select all

UPDATE Foo."dbo".BinTest SET Modified='09.21.2020 09:11:19:454',BinTest=0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF,VarBin=0xFF WHERE id_BinTest=1
UPDATE Foo."dbo".BinTest SET Modified='09.21.2020 09:11:19:470',BinTest=NULL,VarBin=NULL WHERE id_BinTest=2
UPDATE Foo."dbo".BinTest SET Modified='09.21.2020 09:11:19:476',BinTest=NULL,VarBin=0xFF WHERE id_BinTest=3

Is there something wrong with the "SetData" method or is it not suitable in this case?

kind regards
hoedlmoser

[marsupilami]

Hello hoedlmoser,

are you still on an MS SQL Server < 2008, as suggested by this thread? Normally Zeos 7.1 is outdated. If you are on SQL Server 2008 or newer, please switch to Zeos 7.2 or Zeos 7.3. Otherwise we will have to see how we can fix your problem on Zeos 7.2...

Best regards,

Jan

[hoedlmoser]

Hello marsupilami,

Thanks for your answer. We are running an old 2K and a 2017. If switching to 7.2 solves the binary problem, i will try it.

As far as i remember, the metadata query has to be changed in this version with the old one. This works on 2K and 2017 as well.

I dont know what Fr0sT means by "Don't forget Zeos allows wildcard metadata querying and your simplifications are for just a single object."
If metadata from other database than the active one are needed, the query can be extended with database and even connected-host qualifiers, eg. "[host-ip].[master].[dbo].sysobjects join [host-ip].[master].[dbo].syscolumns"
I cant imagine any use of a "wildcard metadata querying".

kind regards
hoedlmoser

[marsupilami]

We are running an old 2K and a 2017. If switching to 7.2 solves the binary problem, i will try it.

We don't support Zeos 7.1 anymore. Soon we will release the successor of Zeos 7.2. So you should at least switch to Zeos 7.2. If there are problems with reading metadata, we will have to see how to solve them. But chances are good that the binary problem is solved in Zeos 7.2. You should make sure to use a current version of freetds though because they also solved a lot of problems in their library.

As far as i remember, the metadata query has to be changed in this version with the old one. This works on 2K and 2017 as well.

Your old post shows that there is a problem in querying old database servers. Starting with SQL Server 2005 Microsoft decided that schemas can be separate objects from users in their implemetation. See here in the middle:

Beginning with SQL Server 2005, the behavior of schemas changed. As a result, code that assumes that schemas are equivalent to database users may no longer return correct results. Old catalog views, including sysobjects, should not be used in a database in which any of the following DDL statements have ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION. In such databases you must instead use the new catalog views. The new catalog views take into account the separation of principals and schemas that was introduced in SQL Server 2005. For more information about catalog views, see Catalog Views (Transact-SQL).

So - to support modern SQL Servers correctly we have to use these new catalog views. Maybe we can implement something for older servers. I just don't know how I will set up one of those beasts to get a suitable test environment, so we can run tests on them. If the above statement from Microsoft is correct, I will have to find some copy of SQL Server 2000 and a copy of Windows XP to get this running... Is there any chance for you to move from SQL Server 2000 to any newer version?

I dont know what Fr0sT means by "Don't forget Zeos allows wildcard metadata querying and your simplifications are for just a single object."
If metadata from other database than the active one are needed, the query can be extended with database and even connected-host qualifiers, eg. "[host-ip].[master].[dbo].sysobjects join [host-ip].[master].[dbo].syscolumns"
I cant imagine any use of a "wildcard metadata querying".

Zeos inherited its internal API from JDBC. The function declaration for GetColumns is like this:

Code: Select all

function GetColumns(const Catalog: string; const SchemaPattern: string; const TableNamePattern: string; const ColumnNamePattern: string): IZResultSet;

As you can see, we allow for a schema pattern and a table name pattern. So it is perfectly correct to call GetColumns like this:

Code: Select all

RS := GetColumns('', 'dbo', 'MyTab%', '')

This would look for all columns of all tables where the name starts with 'MyTab'. And we have to support this as much as possible. It doesn't matter if we see a valid use case for this. Most probably there are users out there who use excatly this possibility for some tasks in their applications. We don't know and so we cannot break it.

Best regards,

Jan

[hoedlmoser]

thanks for your detailed answer. I changed the binary field definition to NOT NULL defaulting to 0x0, as workaround for that update issue.
To migrate all our stuff from 2K to 2017 would be a lifetime job for a one man show, so we can forget about it.

...any of the following DDL statements have ever been used: CREATE SCHEMA, ALTER SCHEMA, DROP SCHEMA, CREATE USER, ALTER USER, DROP USER, CREATE ROLE, ALTER ROLE, DROP ROLE, CREATE APPROLE, ALTER APPROLE, DROP APPROLE, ALTER AUTHORIZATION...

We never use any of the cited DDL statements and my dirty hack worked perfectly for us on 7.2 I dont remember the reason why i migrated back to 7.1 but ill give 7.2 another try, applying that hack.

thanks anyway
hoedlmoser

[EgonHugeist]

Hi hoedlmoser,

neither 7.1 nor 7.2 do support varbinary fields. 7.3 supports it. So my advice is: use 7.3.
@Jan we have no generic test for that. Could you add one to the suites?

[hoedlmoser]

Hi Michael,
thanks for the advice

kind regards
hoedlmoser

[marsupilami]

Hello hoedlmoser,

To migrate all our stuff from 2K to 2017 would be a lifetime job for a one man show, so we can forget about it.

That kinda sounds like the kind of challenges, I like

Regarding the metadata problem for SQL Server 2000: Egonhugeist has introduced some code to detect an SQL Server 2000 and make UncachedGetColumns work there. Could you please test the current version of Zeos 7.3?

Best regards,

Jan

[hoedlmoser]

Hi Jan,

sorry for the delay, i didnt expect any more posts on this thread. Of course i would test this 7.3 version if i only could find it.

best regards

hoedlmoser

[marsupilami]

Hello hoedlmoser,

you can find the current Zeos 7.3 development version on our SourceForge Subversion page: https://sourceforge.net/p/zeoslib/code-0/HEAD/tree/branches/.
If you have any problems concerning Zeos 7.3 please report back here on the forums or use the bug tracker.

Best regards,

Jan