Pages: 1

#1 Re: mORMot 1 » Problem with empty strings in result set using TQuery and ToDataSet » 2019-04-04 17:59:40

NickX

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.

#2 Re: mORMot 1 » Problem with empty strings in result set using TQuery and ToDataSet » 2019-04-04 13:51:20

NickX

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

#3 mORMot 1 » Issue with Remote Query » 2019-04-04 13:14:33

NickX
Replies: 0

I've been using TQuery with a remote connection and getting some strange results.  String columns are being truncated in some records.  I've traced it through to TSQLDBProxyStatementAbstract.IntFillDataCurrent in SynDB.pas which resets the column width when it comes to a record with an empty value in that column. I've changed it in my copy and that seems to correct the issue.

      ftUTF8, ftBlob: begin
        Len := FromVarUInt32(Reader);
        if not IgnoreColumnDataSize then
          if Len>fColumns[F].ColumnDataSize then
            fColumns[F].ColumnDataSize := Len;
// Changed by NC to remove next 6 lines to leave the column size untouched on reading an empty string
{        if Len = 0 then
        begin
          fColumns[F].ColumnDataSize := 0;
//          fDataCurrentRowValues[F] := nil;
        end
        else   }
          inc(Reader,Len); // jump string/blob content
      end;

#4 Re: mORMot 1 » Problem with empty strings in result set using TQuery and ToDataSet » 2019-04-04 13:07:52

NickX

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;

#5 Re: mORMot 1 » Problem with empty strings in result set using TQuery and ToDataSet » 2019-01-26 12:38:19

NickX

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.

#6 Re: mORMot 1 » Problem with empty strings in result set using TQuery and ToDataSet » 2019-01-26 12:07:54

NickX

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

#7 mORMot 1 » Problem with empty strings in result set using TQuery and ToDataSet » 2019-01-25 17:47:53

NickX
Replies: 8

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;

Pages: 1

Board footer

Powered by FluxBB