Page 1 of 1
Problem with storing Line Feed
Posted: Sun 15 Nov 2015 18:18
by 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?
Re: Problem with storing Line Feed
Posted: Mon 16 Nov 2015 08:28
by 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.
Re: Problem with storing Line Feed
Posted: Thu 19 Nov 2015 16:59
by 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.
Re: Problem with storing Line Feed
Posted: Fri 20 Nov 2015 09:35
by 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.
Re: Problem with storing Line Feed
Posted: Fri 20 Nov 2015 18:42
by Dido
Thank you, Alex. Now works perfectly.
Re: Problem with storing Line Feed
Posted: Mon 23 Nov 2015 05:36
by AlexP
Glad to see that the issue was resolved. If you have any further questions, feel free to contact us.
Re: Problem with storing Line Feed
Posted: Thu 17 Dec 2015 20:41
by 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.
Re: Problem with storing Line Feed
Posted: Thu 24 Dec 2015 14:12
by azyk
Thank you for the sample. We will investigate this behavior and inform you about the results.