Problem with storing Line Feed

[Dido]

Hello,
i have problem with storing Line Feed (#10) in strings to SQL Server with UniDAC.
If you try to insert/update nvarchar/varchar type field with LF (#10), after saving LF is converted to CRLF (#13#10).
Example:
'12345'+Chr(10)+'67890' after insert/update is 12 bytes, instead 11 bytes. After storing has one extra byte.
How can i avoid it?

[AlexP]

Hello,

We can't reproduce the problem on the latest UniDAC version 6.2.8. Data is inserted correctly. Please provide the code abstract that leads to the specified problem.

[Dido]

I tested and with SQLite. The results are the same as SQL Server.
For test i create one DB in Direct Mode (SQLite) in UniConnection1,
and use this code:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var
  x: string;
begin
  x:=#10;
  UniConnection1.ExecSQL('CREATE TABLE TestTable (TestField TEXT NOT NULL)');
  UniConnection1.ExecSQL('INSERT INTO TestTable (TestField) values ('+QuotedStr(x)+')');
  UniQuery1.Close;
  UniQuery1.SQL.Text:='select length(TestField) from TestTable';
  UniQuery1.Open;
end;

If x=#13 or x=#10 then length of stored string is 2 bytes. (one extra byte)
If x=#9....#11...#12 or anything different from #13 and #10 then length of stored bytes is 1.

[AlexP]

If you insert symbols #13 or #10 directly in the query text, they are replaced with 2 symbols #13#10. This is due to the fact, that in the Classes module in the procedure TStrings.SetTextStr a new string is added instead of the symbol when detecting any of these symbols.
To solve the issue, you should use parameters as shown below.

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, Uni, SQLiteUniProvider;

var
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;
  x: string;
begin
  UniConnection := TUniConnection.Create(nil);
  try
    UniConnection.ProviderName := 'SQLite';
    UniConnection.Database := ':memory:';
    UniConnection.Connect;
    x:=#10;
    UniConnection.ExecSQL('CREATE TABLE TestTable (TestField TEXT NOT NULL)');
    UniConnection.ExecSQL('INSERT INTO TestTable (TestField) values (:p1)', [x]);
    UniQuery := TUniQuery.Create(nil);
    try
      UniQuery.Connection := UniConnection;
      UniQuery.Close;
      UniQuery.SQL.Text:='select length(TestField) from TestTable';
      UniQuery.Open;
      Writeln(UniQuery.Fields[0].AsString);
    finally
      UniQuery.Free;
    end;
  finally
    UniConnection.Free;
    readln;
  end;
end.

[Dido]

Thank you, Alex. Now works perfectly.

[AlexP]

Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.

[Dido]

Hello again,
now i have problems with parameters larger then 7999 bytes in SQL Server. If string is larger in SQL Server is truncated to 7999 without any error. In SQLite works correctly.
I think there is a problem and with Direct mode in SQL Server.

I write small sample problem:

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, Uni, SQLiteUniProvider, SQLServerUniProvider, ActiveX;

var
  UniConnection_SQLite: TUniConnection;
  UniConnection_SQLServer: TUniConnection;
  UniQuery: TUniQuery;
  x: string;
begin
 CoInitialize(nil);
  UniConnection_SQLite := TUniConnection.Create(nil);
  UniConnection_SQLServer := TUniConnection.Create(nil);
  try
    UniConnection_SQLite.ProviderName := 'SQLite';
    UniConnection_SQLite.SpecificOptions.Values['Direct'] := 'True';
    UniConnection_SQLite.Database := ':memory:';
    UniConnection_SQLite.Connect;

    UniConnection_SQLServer.ProviderName := 'SQL Server';
    UniConnection_SQLServer.Server := '127.0.0.1';
    UniConnection_SQLServer.Database := 'x';
    UniConnection_SQLServer.Port := 1433;
    UniConnection_SQLServer.Username := 'sa';
    UniConnection_SQLServer.Password := 'x';
    UniConnection_SQLServer.SpecificOptions.Values['Provider']:='prNativeClient';
    //UniConnection_SQLServer.SpecificOptions.Values['Provider']:='prDirect';
    UniConnection_SQLServer.Connect;

    x:=stringofchar('0',10000);
    UniConnection_SQLite.ExecSQL('CREATE TABLE TestTable (TestField TEXT NOT NULL)');
    UniConnection_SQLite.ExecSQL('INSERT INTO TestTable (TestField) values (:p1)', [x]);

    UniConnection_SQLServer.ExecSQL('DROP TABLE TestTable');
    UniConnection_SQLServer.ExecSQL('CREATE TABLE TestTable (TestField NVARCHAR(MAX))');

    // In Direct Mode this line does not work, with Native Client is truncated to 7999 bytes
    UniConnection_SQLServer.ExecSQL('INSERT INTO TestTable (TestField) values (:p1)', [x]);

    // In Direct Mode this line work correctly, with native client also
//    UniConnection_SQLServer.ExecSQL('INSERT INTO TestTable (TestField) values ('''+x+''')');

    UniQuery := TUniQuery.Create(nil);
    try
      UniQuery.Connection := UniConnection_SQLite;
      UniQuery.Close;
      UniQuery.SQL.Text:='select length(TestField) from TestTable';
      UniQuery.Open;
      Writeln('SQLite: '+UniQuery.Fields[0].AsString);

      UniQuery.Connection := UniConnection_SQLServer;
      UniQuery.Close;
      UniQuery.SQL.Text:='select len(TestField) from TestTable';
      UniQuery.Open;
      Writeln('SQL Server: '+UniQuery.Fields[0].AsString);
    finally
      UniQuery.Free;
    end;
  finally
    UniConnection_SQLite.Free;
    UniConnection_SQLServer.Free;
    readln;
  end;
end.

[azyk]

Thank you for the sample. We will investigate this behavior and inform you about the results.