TSQLQuery 只为大字符串正确传输前 1MB 的数据 [英] TSQLQuery only streams first 1MB of data correctly for large strings
问题描述
(请参阅带有堆栈跟踪的编辑#1和帖子末尾的带有解决方法的编辑#2)
故障排除时 TSQLQuery.FieldByName().AsString ->TStringStream Corrupts Data,我发现 TSQLQuery.FieldByName().AsBytes
只能正确传输 1MB 的 varchar(max)
数据.
- 使用 WireShark,我验证了数据全部被正确地传递给 Delphi 应用程序.
- 我确认它总是将正确数量的字节写入输出文件,但任何超过正好 1MB 的字节都是空字节.
- 此外,
TSQLQuery.FieldByName().AsString
和.AsWideString
也表现出相同的行为.
什么会导致 .AsBytes
向 TFileStream
提供正确数量的字节,但是 null
所有超过 1MB 的字节?p>
测试用例
这个测试用例创建了两个输出文件.Plus14.txt
为 1MB + 14 字节.Plus36.txt
为 1MB + 36 字节.在这两种情况下,超过 1MB 的字节都是 null
字节值.我什至尝试了一个 16MB 的字符串.输出文件的前 1MB 是正确的;接下来的 15MB 都是 null
字节.
SQL 服务器
使用 tempdb去创建程序 RunMe作为声明@s1 varchar(max), @s2 varchar(max)设置@s1 = '0123456789ABCDEF'设置 @s2 = @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 -- 128 字节设置 @s1 = @s2 + @s2 + @s2 + @s2 + @s2 + @s2 + @s2 + @s2 -- 1,024 字节设置 @s2 = @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 -- 8,192 字节设置 @s1 = @s2 + @s2 + @s2 + @s2 + @s2 + @s2 + @s2 + @s2 -- 65,536 字节设置 @s2 = @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 + @s1 -- 524,288 字节设置 @s1 = @s2 + @s2 -- 1,048,576 字节设置 @s2 = @s1 + '这是一个测试' -- 1MB + 14 字节设置@s1 = @s1 + '紧急广播系统' -- 1MB + 36 字节选择@s2 作为 Plus14,@s1 作为 Plus36去将 RunMe 上的执行权限授予公众去
德尔福 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''供应商库 = sqlncli10.dll''供应商LibWin64 = sqlncli10.dll''主机名=本地主机''数据库 = tempdb''MaxBlobSize=-1''区域设置代码 = 0000''IsolationLevel=ReadCommitted''OSAuthentication = 假''准备 SQL = 真''BlobSize=-1''错误资源文件 =''操作系统身份验证=真''准备 SQL = 假')VendorLib = 'sqlncli10.dll'左 = 8顶部 = 8结尾
德尔福 PAS
TButton.OnClick
的代码:
procedure TForm1.Button1Click(Sender: TObject);var qry: TSQLQuery;程序保存(str:字符串);var 数据:TBytes;fs:TFileStream;开始fs := TFileStream.Create(Format('c:\%s.txt', [str]), fmCreate);尝试数据 := qry.FieldByName(str).AsBytes;如果数据<>则为零fs.WriteBuffer(数据[0],长度(数据));最后FreeAndNil(fs);结尾;结尾;开始SQLConnection1.打开;qry := TSQLQuery.Create(nil);尝试qry.MaxBlobSize := -1;qry.SQLConnection := SQLConnection1;qry.SQL.Text := '设置无计数;执行运行我';qry.打开;保存('Plus14');保存('Plus36');最后FreeAndNil(qry);结尾;SQLConnection1.关闭;结尾;
<<<编辑 #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((无值),1048590)
1MB + 14bData.DB.TBlobField.GetAsBytes
Unit1.save('Plus14')
FDBXRow_GetBytes
返回时,Value: TBytes
为 1048590 字节,最后 14 字节设置了 null
值.
我不确定接下来要尝试什么.非常感谢任何帮助.
<<<编辑 #2 - 解决方法 >>>
我设置了 SQLConnection1.MaxBlobSize := 2097152
,现在所有字节都正确地流式传输到输出文件.所以问题似乎只发生在 .MaxBlobSize = -1
.
既然我找到了解决方法,解决问题的紧迫性就消失了.但是,如果可能,我仍然希望 -1
工作,因为我的数据库中的值有时会超过 50 兆.因此,任何建议或帮助仍然受到赞赏.
<<<编辑 #3 - 错误报告 >>>
我向 Embarcadero (QC #108475) 提交了错误报告.一旦错误得到确认/修复,我会报告.
<<<编辑 #4 - 升级的错误报告 >>>
我今天发现使用此解决方法有时会导致 TClientDataSet
抛出带有文本Catastrophic Failure"的 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.清除;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 deployDBXDrivers.ini.确保不要将 BlobSize
设置为 250 meg,否则会出现内存不足错误.
单元 DBXRegDB;界面执行用途DBXCommon、DBXDynalinkNative、DBXMSSQL、表单、类;类型TDBXInternalDriver = 类(TDBXDynalinkDriverNative)上市构造函数创建(DriverDef:TDBXDriverDef);覆盖;结尾;TDBXInternalProperties = 类(TDBXProperties)上市构造函数创建(DBXContext:TDBXContext);覆盖;结尾;{ TDBXInternalDriver }构造函数 TDBXInternalDriver.Create(DriverDef: TDBXDriverDef);开始继承 Create(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'] := 'False';值['PrepareSQL'] := 'True';值[TDBXPropertyNames.UserName] := 'user';值[TDBXPropertyNames.Password] := 'password';值['BlobSize'] := '-1';值[TDBXPropertyNames.ErrorResourceFile] := '';值 ['OS 身份验证'] := 'False';值['准备 SQL'] := 'True';值[TDBXPropertyNames.ConnectTimeout] := '30';结尾;变量内部连接工厂:TDBXMemoryConnectionFactory;初始化TDBXDriverRegistry.RegisterDriverClass('MSSQL_Con', TDBXInternalDriver);InternalConnectionFactory := TDBXMemoryConnectionFactory.Create;内部连接工厂.打开;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屋!