mORMot Open Source

NickX

Member

Registered: 2019-01-25

Posts: 7

Problem with empty strings in result set using TQuery and ToDataSet

I'm having trouble with a very basic program.  I've created a remote server using OLE DB for SQL Server and then in the client I create a TQuery, pass the query and the use ToDataSet to link to a grid.

The result set just returns 1 row from a table.  Some fields have values, some are null and some contain emptry strings.  The empty strings are producing random strings in the dataset.  When accessed programatically from the TQuery they return the correct value.

If I change the client to directly access the database it works fine.  Here's some example code:

This works:

var
  Q: TQuery;
  Props: TSQLDBConnectionProperties;
  DataSet: TDataSet;
begin
  Props := TOleDBMSSQLConnectionProperties.Create('servername','databasename','user','password');
  Q := TQuery.Create(UseConnection);
  Q.SQL.Text := 'SELECT ID, EmptyColumn, StringDataColumn FROM TestTable WHERE ID = 1';
  Q.Open;

  ShowMessage(Q.FieldByName('EmptyColumn').AsString);  // Shows a result of ''

  DataSet := ToDataSet(Self, Q);

  Q.Free;
  ShowMessage(DataSet.FieldByName('EmptyColumn').AsString);  // Shows a result of ''
  DataSet.Free
end;

This doesn't:

var
  Q: TQuery;
  Props: TSQLDBConnectionProperties;
  DataSet: TDataSet;
begin
  Props := TSQLDBWinHTTPConnectionProperties.Create('localhost:8092','remote','user','pass');  // Server is using TOleDBMSSQLConnectionProperties
  Q := TQuery.Create(UseConnection);
  Q.SQL.Text := 'SELECT ID, EmptyColumn, StringDataColumn FROM TestTable WHERE ID = 1';
  Q.Open;

  ShowMessage(Q.FieldByName('EmptyColumn').AsString);  // Shows a result of ''

  DataSet := ToDataSet(Self, Q);

  Q.Free;
  ShowMessage(DataSet.FieldByName('EmptyColumn').AsString);  // Shows a result of random data
  DataSet.Free
end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,242

Website

Re: Problem with empty strings in result set using TQuery and ToDataSet

Which version of the code are you using?
Which compiler?

Try minimal debug:
Is Q content OK?
Is error in ToDataSet?

See also https://synopse.info/forum/viewtopic.php?id=4831

NickX

Member

Registered: 2019-01-25

Posts: 7

Re: Problem with empty strings in result set using TQuery and ToDataSet

I'm using version 1.18.4990.
I've tested in RAD Studio 10.1 and 10.2 and it is not working in those.  I've just tried in an old Delphi 7 environment and it is fine in there.

The Q content is fine it's only in the DataSet, so the problem would seem to be in the ToDataSet routine.  Although it works okay if I use a direct connection to the database, it's only when using a remote connection that it occurs.

On tracing through the code it seems the problem relates to empty string columns being created as ftWideMemo data type instead of ftWideString (and of course in Delphi 7 they are just ftMemo).

NickX

Member

Registered: 2019-01-25

Posts: 7

Re: Problem with empty strings in result set using TQuery and ToDataSet

I've done some further testing.  I added some extra columns to my TestTable - type text, ntext, varchar(max) and nvarchar(max).  And if they are set to an empty string they produce the same error in the ToDataSet even when accessing directly using OLE DB MS SQL.

NickX

Member

Registered: 2019-01-25

Posts: 7

Re: Problem with empty strings in result set using TQuery and ToDataSet

I made a change to SynCommons which seems to correct the problem and hasn't caused any other issues that I can see.

function Utf8DecodeToRawUnicode(P: PUTF8Char; L: integer): RawUnicode;
var short: array[0..256*6] of WideChar;
    U: PWideChar;
begin
  result := ''; // somewhat faster if result is freed before any SetLength()
// Lines below removed by NC to correct issue with empty strings
//  if L=0 then
//    L := StrLen(P);
  if L=0 then
    exit;

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,544

Website

Re: Problem with empty strings in result set using TQuery and ToDataSet

This is not correct and broke existed software. See documentation for function in interface section - "- if L is 0, L is computed from zero terminated P buffer"

NickX

Member

Registered: 2019-01-25

Posts: 7

Re: Problem with empty strings in result set using TQuery and ToDataSet

Except if the result set contains an empty string there is no zero terminated buffer, it then tries to read the next column.

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,544

Website

Re: Problem with empty strings in result set using TQuery and ToDataSet

Utf8DecodeToRawUnicode not only used in VCL DataSet creation but in many other places. So fix must be done somewhere higher

NickX

Member

Registered: 2019-01-25

Posts: 7

Re: Problem with empty strings in result set using TQuery and ToDataSet

I agree but I don't have the time to try and identify where.  This fixes it for me for the moment as I need to move on with my project.  Hopefully someone with more in depth knowledge of the intricacies of the framework can find a more long term solution.