Blob Field Truncation with Regional Settings with 3.10.8/7 driver

[bdiehl]

We are seeing truncation of TEXT field data return from a SQL Server database when the Regional Settings Advanced "Language for non-Unicode programs" to languages such as Turkish or Greek on an English version of Windows. The code below does not return the correct data from the TEXT field. The original TEXT data is 2879 characters of lower ASCII data. When the regional setting is changed the data return to the stream has the first 1012 characters and then repeats the first 1012 characters again and finally the first 821 characters are repeated. The returned data is less than the size of the original field value. We see this behavior in driver 3.10.7 and 3.10.8. We do not see the same behavior in 3.0.2.0 or before. Our application is build with Delphi 2005 Update 2 and the database is SQL Server 2005 SP2.

This is the test code we used:

procedure TWinForm.Button1_Click(sender: System.Object; e: System.EventArgs);
var
query: TSQLQuery;
field: TField;
blobField: TBlobField;
conn: TSQLConnection;
memStream: TMemoryStream;
strStream: TStringStream;
begin
query := nil;
field := nil;
conn := nil;
try
conn := TSQLConnection.Create(nil);
conn.DriverName := 'SQLServer';
conn.LibraryName := 'dbexpsda.dll';
conn.VendorLib := 'sqloledb.dll';
conn.GetDriverFunc := 'getSQLDriverSQLServer';
conn.Params.Clear;
conn.Params.Add('User_Name=puser');
conn.Params.Add('Password=puser');
conn.Params.Add('HostName=pserver\sqlexpress');
conn.Params.Add('Database=PMDB');
conn.LoginPrompt := False;
conn.Open;

query := TSQLQuery.Create( nil );
query.SQLConnection := conn;
query.SQL.Add('select license_data from prefer');
query.Open;
query.First;

if not query.IsEmpty then
begin
field := query.FieldByName('license_data');
blobField := field as TBlobField;

memStream := TMemoryStream.Create;
blobField.SaveToStream(memStream);

strStream := TStringStream.Create('');
strStream.LoadFromStream(memStream);
RichTextBox1.Text := strStream.DataString;
end;

query.Close;
conn.Close;
finally
strStream.Free;
memStream.Free;
field.Free;
query.Free;
conn.Free;
end;
end;

--Brian

[Jackson]

You should use the NTEXT data type instead of TEXT in such case.
DbxSda 3.0.2.0 works because it uses SQL OLE DB Provider by default, whereas DbxSda 3.10.8 uses SQL Native Client.
Unfortunately there is no possibility to change the provider. This is a restriction of the dbExpress technology.

[bdiehl]

We're nearing the release of our application so any changes to the datatype is out of the question. It still seems like this is a bug if the data is getting truncated as a result of this setting (Language for non-Unicode programs) regardless of the which SQL Server driver is being used. The data in this case is not multi-byte, it's just lower-ascii data in a TEXT field.

[Jackson]

We couldn't reproduce the problem.
Please send us (evgeniym*crlab*com) a complete small test project to reproduce the problem;
include your database backup file; don't use third party components.

[bdiehl]

I've forwarded a copy of the test application and database.

[Jackson]

This is behaviour of SQL Native Client which is used as a default OLE DB Provider in DbxSda 3.10.7 and higher.
You should specify collation for your column corresponding to your locale settings or change your locale settings corresponding to the column collation. There is a table to find the corresponding collation designator and the code page to match collation settings with an existing Windows locale in SQL Server 2005
Books Online, "Collation Settings in Setup" article, table of SQL Server 2005 collations(http://msdn2.microsoft.com/en-us/library/ms143508.aspx).