对于大字符串,TSQLQuery 仅正确传输前 1MB 的数据 [英] TSQLQuery only streams first 1MB of data correctly for large strings
问题描述
(请参阅使用堆栈跟踪编辑 #1 和使用帖子末尾的解决方法编辑 #2)
在排除故障时 TSQLQuery.FieldByName().AsString ->TStringStream 损坏数据,我发现 TSQLQuery.FieldByName().AsBytes
只能正确传输 1MB 的 varchar(max)
数据.
- 使用 WireShark,我验证了数据全部都被正确传递给了 Delphi 应用程序.
- 我确认它总是向输出文件写出正确数量的字节,但任何超过正好 1MB 的字节都是空字节.
- 此外,
TSQLQuery.FieldByName().AsString
和.AsWideString
也表现出相同的行为.
什么会导致 .AsBytes
向 TFileStream
提供正确数量的字节,但 null
所有超过 1MB 的字节?>
测试用例
这个测试用例创建了两个输出文件.Plus14.txt
是 1MB + 14 字节.Plus36.txt
是 1MB + 36 字节.在这两种情况下,超过 1MB 的字节都是 null
字节值.我什至尝试了一个 16MB 的字符串.输出文件的前 1MB 是正确的;接下来的 15MB 都是 null
字节.
SQL Server
使用 tempdb去创建程序 RunMe作为声明@s1 varchar(max), @s2 varchar(max)设置@s1 = '0123456789ABCDEF'set @s2 = @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 -- 128 字节set @s1 = @s2 + @s2 + @s2 + @s2 + @s2 + @s2 + @s2 + @s2 -- 1,024 字节set @s2 = @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 -- 8,192 字节set @s1 = @s2 + @s2 + @s2 + @s2 + @s2 + @s2 + @s2 + @s2 -- 65,536 字节set @s2 = @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 -- 524,288 字节设置@s1 = @s2 + @s2 -- 1,048,576 字节set @s2 = @s1 + '这是一个测试' -- 1MB + 14 字节set @s1 = @s1 + '紧急广播系统' -- 1MB + 36 字节选择@s2 作为Plus14,@s1 作为Plus36去将 RunMe 上的执行授予公开去
Delphi DFM
默认表单,上面有这个 TSQLConnection
(和一个 TButton
):
object SQLConnection1: TSQLConnection驱动程序名称 = 'MSSQL'GetDriverFunc = 'getSQLDriverMSSQL'库名 = 'dbxmss.dll'登录提示 = 假参数.字符串 = ('用户名=用户''密码=密码''SchemaOverride=%.dbo''DriverUnit=Data.DBXMSSQL''DriverPackageLoader=TDBXDynalinkDriverLoader,DBXCommonDriver160.'+'bpl''DriverAssemblyLoader=Borland.Data.TDBXDynalinkDriverLoader,Borla' +'nd.Data.DbxCommonDriver,Version=16.0.0.0,Culture=neutral,PublicK' +'eyToken=91d62ebb5b0d1b1b''MetaDataPackageLoader=TDBXMsSqlMetaDataCommandFactory,DbxMSSQLDr' +'iver160.bpl''MetaDataAssemblyLoader=Borland.Data.TDBXMsSqlMetaDataCommandFact' +'ory,Borland.Data.DbxMSSQLDriver,Version=16.0.0.0,Culture=neutral' +',PublicKeyToken=91d62ebb5b0d1b1b''GetDriverFunc=getSQLDriverMSSQL''库名=dbxmss.dll''VendorLib=sqlncli10.dll''VendorLibWin64=sqlncli10.dll''主机名=本地主机''数据库=tempdb''MaxBlobSize=-1''LocaleCode=0000''IsolationLevel=ReadCommitted''OSAuthentication=False''准备SQL=真''BlobSize=-1''错误资源文件=''操作系统身份验证=真''准备 SQL=False')VendorLib = 'sqlncli10.dll'左 = 8顶部 = 8结尾
德尔福 PAS
TButton.OnClick
的代码:
procedure TForm1.Button1Click(Sender: TObject);var qry: TSQLQuery;程序保存(字符串:字符串);var 数据:TBytes;fs:TFileStream;开始fs := TFileStream.Create(Format('c:\%s.txt', [str]), fmCreate);尝试数据:= qry.FieldByName(str).AsBytes;如果数据<>零然后fs.WriteBuffer(data[0], Length(data));最后FreeAndNil(fs);结尾;结尾;开始SQLConnection1.Open;qry := TSQLQuery.Create(nil);尝试qry.MaxBlobSize := -1;qry.SQLConnection := SQLConnection1;qry.SQL.Text := '设置无计数;执行运行我';qry.Open;保存('Plus14');保存('Plus36');最后FreeAndNil(qry);结尾;SQLConnection1.Close;结尾;
<<<<编辑 #1 - 堆栈跟踪 >>>
我跟踪了 Embarcadero 的代码,找到了 null
字节第一次出现的地方.
FMethodTable.FDBXRow_GetBytes
Data.DBXDynalink.TDBXDynalinkByteReader.GetBytes(0,0,(...),0,1048590,True)
Data.SqlExpr.TCustomSQLDataSet.GetFieldData(1,$7EC80018)
Data.SqlExpr.TCustomSQLDataSet.GetFieldData(???,$7EC80018)
Data.DB.TDataSet.GetFieldData($66DB18,$7EC80018,True)
Data.SqlExpr.TSQLBlobStream.ReadBlobData
Data.SqlExpr.TSQLBlobStream.Read((无值),1048590)
System.Classes.TStream.ReadBuffer((no value),1048590)
1MB + 14bData.DB.TBlobField.GetAsBytes
Unit1.save('Plus14')
当 FDBXRow_GetBytes
返回时,Value: TBytes
为 1048590 字节,null
值设置为最后 14 个字节.
我不确定接下来要尝试什么.非常感谢任何帮助.
<<<<编辑 #2 - 解决方法 >>>
我设置了 SQLConnection1.MaxBlobSize := 2097152
,现在所有字节都正确地流到输出文件.所以这个问题似乎只发生在 .MaxBlobSize = -1
.
现在我找到了解决方法,解决问题的紧迫性已经不复存在.但是,如果可能的话,我仍然想让 -1
工作,因为我的数据库中的值有时会超过 50 兆.因此,仍然感谢任何建议或帮助.
<<<<编辑 #3 - 错误报告 >>>
我向 Embarcadero (QC #108475) 提交了错误报告.一旦错误得到确认/修复,我会回来报告.
<<<<编辑 #4 - 升级的错误报告 >>>
我今天发现使用此解决方法有时会导致 TClientDataSet
抛出带有文本灾难性故障"的EOleException
.显然 TClientDataSet
更喜欢 MaxBlobSize := '-1';
.因此,我在 Embarcadero 上报了错误报告.希望他们能尽快为此提供修复或更好的解决方法.
我能够解决这个问题.可以像这样设置 TSQLConnection
属性:
sqlcon.Params.Values['MaxBlobSize'] := '250000000';//250 兆sqlcon.Params.Values['BlobSize'] := '-1';
但是我通过使用 DBXRegDB
单元设置所有 TSQLConnection
组件来设置比这些更多的属性.我不使用 IDE 的属性编辑器...DBXRegDB
单元中的某些设置可能也是解决方法工作所必需的(我不确定).我将包含 DBXRegDB
单元 + 说明如何使用它以防万一.
- 将
DBXRegDB
单元添加到.dpr
文件中. - 将
DBXRegDB
添加到表单的uses
子句中. - 执行以下代码,将其传递给表单上的
TSQLConnection
组件:
.
procedure SetupMSSqlConnection(const sqlcon: TSQLConnection; const hostname, port, maxcon, dbname, username, password: string);开始sqlcon.Params.Clear;sqlcon.DriverName := 'MSSQL_Con';sqlcon.VendorLib := sqlcon.Params.Values[TDBXPropertyNames.VendorLib];sqlcon.LibraryName := sqlcon.Params.Values[TDBXPropertyNames.LibraryName];sqlcon.GetDriverFunc := sqlcon.Params.Values[TDBXPropertyNames.GetDriverFunc];sqlcon.Params.Values[TDBXPropertyNames.HostName] := 主机名;sqlcon.Params.Values[TDBXPropertyNames.Port]:= 端口;sqlcon.Params.Values[TDBXPropertyNames.Database] := dbname;sqlcon.Params.Values[TDBXPropertyNames.UserName] := 用户名;sqlcon.Params.Values[TDBXPropertyNames.Password] := 密码;结尾;
最后,将 TSQLQuery.MaxBlobSize
设置为0",以便它会自动从其 TSQLConnection
复制值.
这里是 DBXRegDB
单元,供那些想要使用它的人使用.我从这里找到的东西改编了它:DBX without deployingDBXDrivers.ini.确保不要将 BlobSize
设置为 250 megs,否则会出现内存不足错误.
unit DBXRegDB;界面执行用途DBXCommon、DBXDynalinkNative、DBXMSSQL、表单、类;类型TDBXInternalDriver = 类(TDBXDynalinkDriverNative)上市构造函数创建(DriverDef:TDBXDriverDef);覆盖;结尾;TDBXInternalProperties = 类(TDBXProperties)上市构造函数创建(DBXContext:TDBXContext);覆盖;结尾;{ TDBXInternalDriver }构造函数 TDBXInternalDriver.Create(DriverDef: TDBXDriverDef);开始继承创建(DriverDef,TDBXDynalinkDriverLoader);InitDriverProperties(TDBXInternalProperties.Create(DriverDef.FDBXContext));结尾;{ TDBXInternalProperties }构造函数 TDBXInternalProperties.Create(DBXContext: TDBXContext);开始继承创建(DBXContext);值[TDBXPropertyNames.SchemaOverride] := '%.dbo';值[TDBXPropertyNames.DriverUnit] := 'DBXMSSQL';值[TDBXPropertyNames.DriverPackageLoader] := 'TDBXDynalinkDriverLoader,DBXCommonDriver160.bpl';值[TDBXPropertyNames.DriverAssemblyLoader] := 'Borland.Data.TDBXDynalinkDriverLoader,Borland.Data.DbxCommonDriver,Version=15.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b';值[TDBXPropertyNames.MetaDataPackageLoader] := 'TDBXMsSqlMetaDataCommandFactory,DbxMSSQLDriver160.bpl';值[TDBXPropertyNames.MetaDataAssemblyLoader] := 'Borland.Data.TDBXMsSqlMetaDataCommandFactory,Borland.Data.DbxMSSQLDriver,Version=15.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b';值[TDBXPropertyNames.GetDriverFunc] := 'getSQLDriverMSSQL';值[TDBXPropertyNames.LibraryName] := 'dbxmss.dll';值[TDBXPropertyNames.VendorLib] := 'sqlncli10.dll';值[TDBXPropertyNames.HostName] := 'ServerName';值[TDBXPropertyNames.Database] := '数据库名称';值 [TDBXPropertyNames.MaxBlobSize] := '250000000';值['LocaleCode'] := '0000';值[TDBXPropertyNames.IsolationLevel] := 'ReadCommitted';值['OSAuthentication'] := '假';值['PrepareSQL'] := '真';值[TDBXPropertyNames.UserName] := 'user';值[TDBXPropertyNames.Password] := '密码';值['BlobSize'] := '-1';值[TDBXPropertyNames.ErrorResourceFile] := '';值['操作系统身份验证']:= '假';值['准备 SQL'] := '真';值 [TDBXPropertyNames.ConnectTimeout] := '30';结尾;无功内部连接工厂:TDBXMemoryConnectionFactory;初始化TDBXDriverRegistry.RegisterDriverClass('MSSQL_Con', TDBXInternalDriver);内部连接工厂:= TDBXMemoryConnectionFactory.Create;内部连接工厂.Open;TDBXConnectionFactory.SetConnectionFactory(InternalConnectionFactory);结尾.
(see Edit #1 with stack trace and Edit #2 with workaround at end of post)
While troubleshooting TSQLQuery.FieldByName().AsString -> TStringStream Corrupts Data, I found that a TSQLQuery.FieldByName().AsBytes
will only stream exactly 1MB of varchar(max)
data correctly.
- Using WireShark, I verified that the data is all being handed to the Delphi app correctly.
- I verified that it always writes out the correct number of bytes to the output file, but any bytes that exceed exactly 1MB are null bytes.
- Additionally,
TSQLQuery.FieldByName().AsString
and.AsWideString
also exhibit the same behavior.
What would cause .AsBytes
to supply the correct number of bytes to the TFileStream
, but null
all bytes that exceed 1MB?
Test Case
This test case creates two output files. Plus14.txt
is 1MB + 14 bytes. Plus36.txt
is 1MB + 36 bytes. In both cases, the bytes more than 1MB are null
byte values. I even tried a 16MB string. The first 1MB of the output file was correct; the next 15MB were all null
bytes.
SQL Server
use tempdb
go
create procedure RunMe
as
declare @s1 varchar(max), @s2 varchar(max)
set @s1 = '0123456789ABCDEF'
set @s2 = @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 -- 128 bytes
set @s1 = @s2 + @s2 + @s2 + @s2 + @s2 + @s2 + @s2 + @s2 -- 1,024 bytes
set @s2 = @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 -- 8,192 bytes
set @s1 = @s2 + @s2 + @s2 + @s2 + @s2 + @s2 + @s2 + @s2 -- 65,536 bytes
set @s2 = @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 -- 524,288 bytes
set @s1 = @s2 + @s2 -- 1,048,576 bytes
set @s2 = @s1 + 'this is a test' -- 1MB + 14 bytes
set @s1 = @s1 + 'of the emergency broadcasting system' -- 1MB + 36 bytes
select @s2 as Plus14, @s1 as Plus36
go
grant execute on RunMe to public
go
Delphi DFM
Default form, with this TSQLConnection
dropped on it (and one TButton
):
object SQLConnection1: TSQLConnection
DriverName = 'MSSQL'
GetDriverFunc = 'getSQLDriverMSSQL'
LibraryName = 'dbxmss.dll'
LoginPrompt = False
Params.Strings = (
'User_Name=user'
'Password=password'
'SchemaOverride=%.dbo'
'DriverUnit=Data.DBXMSSQL'
'DriverPackageLoader=TDBXDynalinkDriverLoader,DBXCommonDriver160.' +
'bpl'
'DriverAssemblyLoader=Borland.Data.TDBXDynalinkDriverLoader,Borla' +
'nd.Data.DbxCommonDriver,Version=16.0.0.0,Culture=neutral,PublicK' +
'eyToken=91d62ebb5b0d1b1b'
'MetaDataPackageLoader=TDBXMsSqlMetaDataCommandFactory,DbxMSSQLDr' +
'iver160.bpl'
'MetaDataAssemblyLoader=Borland.Data.TDBXMsSqlMetaDataCommandFact' +
'ory,Borland.Data.DbxMSSQLDriver,Version=16.0.0.0,Culture=neutral' +
',PublicKeyToken=91d62ebb5b0d1b1b'
'GetDriverFunc=getSQLDriverMSSQL'
'LibraryName=dbxmss.dll'
'VendorLib=sqlncli10.dll'
'VendorLibWin64=sqlncli10.dll'
'HostName=localhost'
'Database=tempdb'
'MaxBlobSize=-1'
'LocaleCode=0000'
'IsolationLevel=ReadCommitted'
'OSAuthentication=False'
'PrepareSQL=True'
'BlobSize=-1'
'ErrorResourceFile='
'OS Authentication=True'
'Prepare SQL=False')
VendorLib = 'sqlncli10.dll'
Left = 8
Top = 8
end
Delphi PAS
The code for the TButton.OnClick
:
procedure TForm1.Button1Click(Sender: TObject);
var qry: TSQLQuery;
procedure save(str: string);
var data: TBytes; fs: TFileStream;
begin
fs := TFileStream.Create(Format('c:\%s.txt', [str]), fmCreate);
try
data := qry.FieldByName(str).AsBytes;
if data <> nil then
fs.WriteBuffer(data[0], Length(data));
finally
FreeAndNil(fs);
end;
end;
begin
SQLConnection1.Open;
qry := TSQLQuery.Create(nil);
try
qry.MaxBlobSize := -1;
qry.SQLConnection := SQLConnection1;
qry.SQL.Text := 'set nocount on; exec RunMe';
qry.Open;
save('Plus14');
save('Plus36');
finally
FreeAndNil(qry);
end;
SQLConnection1.Close;
end;
<<< Edit #1 - Stack Trace >>>
I traced through Embarcadero's code and found the place where the null
bytes first appear.
FMethodTable.FDBXRow_GetBytes
Data.DBXDynalink.TDBXDynalinkByteReader.GetBytes(0,0,(...),0,1048590,True)
Data.SqlExpr.TCustomSQLDataSet.GetFieldData(1,$7EC80018)
Data.SqlExpr.TCustomSQLDataSet.GetFieldData(???,$7EC80018)
Data.DB.TDataSet.GetFieldData($66DB18,$7EC80018,True)
Data.SqlExpr.TSQLBlobStream.ReadBlobData
Data.SqlExpr.TSQLBlobStream.Read((no value),1048590)
System.Classes.TStream.ReadBuffer((no value),1048590)
1MB + 14bData.DB.TBlobField.GetAsBytes
Unit1.save('Plus14')
When FDBXRow_GetBytes
returns, Value: TBytes
is 1048590 bytes, with null
values set for the last 14 bytes.
I'm not sure what to try next. Any help is greatly appreciated.
<<< Edit #2 - Workaround >>>
I set SQLConnection1.MaxBlobSize := 2097152
, and now all bytes are stream to the output files correctly. So the problem only seems to occur when .MaxBlobSize = -1
.
The urgency to fix the issue is gone now that I found a workaround. However, I would still like to get -1
to work if possible since the values from my database will sometimes exceed 50 megs. So any suggestions or help is still appreciated.
<<< Edit #3 - Bug Report >>>
I filed a bug report with Embarcadero (QC #108475). I will report back once the bug has been acknowledged / fixed.
<<< Edit #4 - Escalated Bug Report >>>
I found today that using this workaround will sometimes causes a TClientDataSet
to throw an EOleException
with the text 'Catastrophic Failure'. Apparently a TClientDataSet
prefers a MaxBlobSize := '-1';
. Consequently, I escalated the bug report at Embarcadero. Hopefully they will provide a fix or a better workaround for this soon.
I was able to work around the issue. It may be possible to just set the TSQLConnection
properties like this:
sqlcon.Params.Values['MaxBlobSize'] := '250000000'; // 250 megs
sqlcon.Params.Values['BlobSize'] := '-1';
But I set a lot more properties than these by using a DBXRegDB
unit to setup all TSQLConnection
components. I don't use the IDE's property editor... Some of the settings from the DBXRegDB
unit may also be required for the workaround to work (I don't know for sure). I'll include the DBXRegDB
unit + instructions how to use it just in case.
- Add the
DBXRegDB
unit to the.dpr
file. - Add
DBXRegDB
to the form'suses
clause. - Execute the following code, passing it the
TSQLConnection
component on your form:
.
procedure SetupMSSqlConnection(const sqlcon: TSQLConnection; const hostname, port, maxcon, dbname, username, password: string);
begin
sqlcon.Params.Clear;
sqlcon.DriverName := 'MSSQL_Con';
sqlcon.VendorLib := sqlcon.Params.Values[TDBXPropertyNames.VendorLib];
sqlcon.LibraryName := sqlcon.Params.Values[TDBXPropertyNames.LibraryName];
sqlcon.GetDriverFunc := sqlcon.Params.Values[TDBXPropertyNames.GetDriverFunc];
sqlcon.Params.Values[TDBXPropertyNames.HostName] := hostname;
sqlcon.Params.Values[TDBXPropertyNames.Port] := port;
sqlcon.Params.Values[TDBXPropertyNames.Database] := dbname;
sqlcon.Params.Values[TDBXPropertyNames.UserName] := username;
sqlcon.Params.Values[TDBXPropertyNames.Password] := password;
end;
Lastly, set the TSQLQuery.MaxBlobSize
to '0', so that it will automatically copy the value from its TSQLConnection
.
Here is the DBXRegDB
unit, for those who want to use it. I adapted it from something I found here: DBX without deploying DBXDrivers.ini. Make sure you do not set the BlobSize
to 250 megs or you will get out of memory errors.
unit DBXRegDB;
interface
implementation
uses
DBXCommon, DBXDynalinkNative, DBXMSSQL, Forms, Classes;
type
TDBXInternalDriver = class(TDBXDynalinkDriverNative)
public
constructor Create(DriverDef: TDBXDriverDef); override;
end;
TDBXInternalProperties = class(TDBXProperties)
public
constructor Create(DBXContext: TDBXContext); override;
end;
{ TDBXInternalDriver }
constructor TDBXInternalDriver.Create(DriverDef: TDBXDriverDef);
begin
inherited Create(DriverDef, TDBXDynalinkDriverLoader);
InitDriverProperties(TDBXInternalProperties.Create(DriverDef.FDBXContext));
end;
{ TDBXInternalProperties }
constructor TDBXInternalProperties.Create(DBXContext: TDBXContext);
begin
inherited Create(DBXContext);
Values[TDBXPropertyNames.SchemaOverride] := '%.dbo';
Values[TDBXPropertyNames.DriverUnit] := 'DBXMSSQL';
Values[TDBXPropertyNames.DriverPackageLoader] := 'TDBXDynalinkDriverLoader,DBXCommonDriver160.bpl';
Values[TDBXPropertyNames.DriverAssemblyLoader] := 'Borland.Data.TDBXDynalinkDriverLoader,Borland.Data.DbxCommonDriver,Version=15.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b';
Values[TDBXPropertyNames.MetaDataPackageLoader] := 'TDBXMsSqlMetaDataCommandFactory,DbxMSSQLDriver160.bpl';
Values[TDBXPropertyNames.MetaDataAssemblyLoader] := 'Borland.Data.TDBXMsSqlMetaDataCommandFactory,Borland.Data.DbxMSSQLDriver,Version=15.0.0.0,Culture=neutral,PublicKeyToken=91d62ebb5b0d1b1b';
Values[TDBXPropertyNames.GetDriverFunc] := 'getSQLDriverMSSQL';
Values[TDBXPropertyNames.LibraryName] := 'dbxmss.dll';
Values[TDBXPropertyNames.VendorLib] := 'sqlncli10.dll';
Values[TDBXPropertyNames.HostName] := 'ServerName';
Values[TDBXPropertyNames.Database] := 'Database Name';
Values[TDBXPropertyNames.MaxBlobSize] := '250000000';
Values['LocaleCode'] := '0000';
Values[TDBXPropertyNames.IsolationLevel] := 'ReadCommitted';
Values['OSAuthentication'] := 'False';
Values['PrepareSQL'] := 'True';
Values[TDBXPropertyNames.UserName] := 'user';
Values[TDBXPropertyNames.Password] := 'password';
Values['BlobSize'] := '-1';
Values[TDBXPropertyNames.ErrorResourceFile] := '';
Values['OS Authentication'] := 'False';
Values['Prepare SQL'] := 'True';
Values[TDBXPropertyNames.ConnectTimeout] := '30';
end;
var
InternalConnectionFactory: TDBXMemoryConnectionFactory;
initialization
TDBXDriverRegistry.RegisterDriverClass('MSSQL_Con', TDBXInternalDriver);
InternalConnectionFactory := TDBXMemoryConnectionFactory.Create;
InternalConnectionFactory.Open;
TDBXConnectionFactory.SetConnectionFactory(InternalConnectionFactory);
end.
这篇关于对于大字符串,TSQLQuery 仅正确传输前 1MB 的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!