TSQLQuery仅针对大型字符串正确地流出了前1MB的数据 [英] TSQLQuery only streams first 1MB of data correctly for large strings

问题描述

（有关堆栈跟踪的编辑＃1和编辑＃2与发布结束时的解决方法）

(see Edit #1 with stack trace and Edit #2 with workaround at end of post)

在排除 TSQLQuery.FieldByName（）。AsString - > TStringStream破坏数据，我发现一个 TSQLQuery.FieldByName（）。AsBytes 只会精确地传送1MB的 varchar（max） code>数据正确。

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.

• 使用WireShark，我验证了数据是全部 Delphi应用程序正确地。


• 我验证了它始终向输出文件写出正确的字节数，但是任何字节


• 另外， TSQLQuery.FieldByName（）。AsString 和 .AsWideString 也表现出相同的行为。

• 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.

什么会导致 .AsBytes 为 TFileStream 提供正确的字节数，但 null 所有字节超过1MB？

What would cause .AsBytes to supply the correct number of bytes to the TFileStream, but null all bytes that exceed 1MB?

此测试用例创建两个输出文件。 Plus14.txt 是1MB + 14个字节。 Plus36.txt 是1MB + 36个字节。在这两种情况下，超过1MB的字节都是 null 字节值。我甚至尝试了一个16MB的字符串。第一个1MB的输出文件是正确的;接下来的15MB都是 null 字节。

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.

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

默认表单，这个 TSQLConnection 删除（和一个 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

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;

<<<编辑＃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） code>


• Data.SqlExpr.TSQLBlobStream.ReadBlobData


• Data.SqlExpr.TSQLBlobStream.Read（（no value），1048590）


• System.Classes.TStream.ReadBuffer （无值），1048590） 1MB + 14b


• Data.DB.TBlobField.GetAsBytes


• Unit1.save（'Plus14'）

• 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 + 14b
• Data.DB.TBlobField.GetAsBytes
• Unit1.save('Plus14')

当 FDBXRow_GetBytes re转换，值：TBytes 是1048590字节，最后14个字节设置 null 值。

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.

我设置 SQLConnection1.MaxBlobSize：= 2097152 ，现在所有字节都是流输出文件正确。所以问题只在 .MaxBlobSize = -1 时才会发生。

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.

解决问题的紧迫性已经消失现在我发现了一个解决方法。但是，如果可能，我仍然希望获得 -1 的工作，因为我的数据库中的值有时会超过50兆。所以任何建议或帮助仍然是赞赏。

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.

我向Embarcadero提交了错误报告（QC＃108475）。一旦该错误被确认/修正，我将会回报。

I filed a bug report with Embarcadero (QC #108475). I will report back once the bug has been acknowledged / fixed.

我今天发现使用此解决方法有时会导致一个 TClientDataSet 将文本'灾难性故障'抛出 EOleException 。显然一个 TClientDataSet 喜欢一个 MaxBlobSize：='-1'; 。因此，我升级了Embarcadero的错误报告。希望他们能尽快提供一个修复或更好的解决方法。

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.

推荐答案

我能够解决这个问题。可以这样设置 TSQLConnection 属性：

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';

但是通过使用 DBXRegDB code>单元来设置所有 TSQLConnection 组件。我不使用IDE的属性编辑器...解决方法可能需要从 DBXRegDB 单元的某些设置（我不知道肯定）。我将包含 DBXRegDB unit +说明如何使用它，以防万一。

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.

• 将 DBXRegDB 单位添加到 .dpr 文件。


• 添加 DBXRegDB 到表单的使用子句。


• 执行以下代码，传递您的表单上的 TSQLConnection 组件：

• Add the DBXRegDB unit to the .dpr file.
• Add DBXRegDB to the form's uses clause.
• Execute the following code, passing it the TSQLConnection component on your form:

。 b
$ b

.

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;

最后，将 TSQLQuery.MaxBlobSize 设置为'0'，这样它将自动从 TSQLConnection 中复制值。

Lastly, set the TSQLQuery.MaxBlobSize to '0', so that it will automatically copy the value from its TSQLConnection.

这里是$ $ c> DBXRegDB 单位，对于那些想要使用它的人。我从我在这里找到的东西改编：没有部署的DBX DBXDrivers.ini 。确保您不要将 BlobSize 设置为250 megs，否则您将失去内存错误。

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屋！