title: TSQLQuery only streams first 1MB of data correctly for large strings
body: <p>(see Edit #1 with stack trace and Edit #2 with workaround at end of post)</p>
  
  <p>While troubleshooting <a href="https://stackoverflow.com/questions/12210174/tsqlquery-fieldbyname-asstring-tstringstream-corrupts-data">TSQLQuery.FieldByName().AsString -&gt; TStringStream Corrupts Data</a>, I found that a <code>TSQLQuery.FieldByName().AsBytes</code> will only stream exactly 1MB of <code>varchar(max)</code> data correctly.</p>
  
  <ul>
  <li>Using WireShark, I verified that the data is <em>all</em> being handed to the Delphi app <em>correctly</em>.</li>
  <li>I verified that it <em>always</em> writes out the correct number of bytes to the output file, but any bytes that exceed <strong>exactly 1MB</strong> are null bytes.</li>
  <li>Additionally, <code>TSQLQuery.FieldByName().AsString</code> and <code>.AsWideString</code> also exhibit the same behavior.</li>
  </ul>
  
  <p>What would cause <code>.AsBytes</code> to supply the correct number of bytes to the <code>TFileStream</code>, but <code>null</code> all bytes that exceed 1MB?</p>
  
  <h2>Test Case</h2>
  
  <p>This test case creates two output files.  <code>Plus14.txt</code> is 1MB + 14 bytes.  <code>Plus36.txt</code> is 1MB + 36 bytes.  In both cases, the bytes more than 1MB are <code>null</code> byte values.  I even tried a 16MB string.  The first 1MB of the output file was correct; the next 15MB were all <code>null</code> bytes.</p>
  
  <h2>SQL Server</h2>
  
  <pre><code>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
  </code></pre>
  
  <h2>Delphi DFM</h2>
  
  <p>Default form, with this <code>TSQLConnection</code> dropped on it (and one <code>TButton</code>):</p>
  
  <pre><code>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
  </code></pre>
  
  <h2>Delphi PAS</h2>
  
  <p>The code for the <code>TButton.OnClick</code>:</p>
  
  <pre><code>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 &lt;&gt; 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;
  </code></pre>
  
  <h2>&lt;&lt;&lt; Edit #1 - Stack Trace >>></h2>
  
  <p>I traced through Embarcadero's code and found the place where the <code>null</code> bytes first appear.</p>
  
  <ul>
  <li><code>FMethodTable.FDBXRow_GetBytes</code></li>
  <li><code>Data.DBXDynalink.TDBXDynalinkByteReader.GetBytes(0,0,(...),0,1048590,True)</code></li>
  <li><code>Data.SqlExpr.TCustomSQLDataSet.GetFieldData(1,$7EC80018)</code></li>
  <li><code>Data.SqlExpr.TCustomSQLDataSet.GetFieldData(???,$7EC80018)</code></li>
  <li><code>Data.DB.TDataSet.GetFieldData($66DB18,$7EC80018,True)</code></li>
  <li><code>Data.SqlExpr.TSQLBlobStream.ReadBlobData</code></li>
  <li><code>Data.SqlExpr.TSQLBlobStream.Read((no value),1048590)</code></li>
  <li><code>System.Classes.TStream.ReadBuffer((no value),1048590)</code> <em>1MB + 14b</em></li>
  <li><code>Data.DB.TBlobField.GetAsBytes</code></li>
  <li><code>Unit1.save('Plus14')</code></li>
  </ul>
  
  <p>When <code>FDBXRow_GetBytes</code> returns, <code>Value: TBytes</code> is 1048590 bytes, with <code>null</code> values set for the last 14 bytes.</p>
  
  <p>I'm not sure what to try next.  Any help is greatly appreciated.</p>
  
  <h2>&lt;&lt;&lt; Edit #2 - Workaround >>></h2>
  
  <p>I set <code>SQLConnection1.MaxBlobSize := 2097152</code>, and now all bytes are stream to the output files correctly.  So the problem only seems to occur when <code>.MaxBlobSize = -1</code>.</p>
  
  <p>The urgency to fix the issue is gone now that I found a workaround.  However, I would still like to get <code>-1</code> to work if possible since the values from my database will sometimes exceed 50 megs.  So any suggestions or help is still appreciated.</p>
  
  <h2>&lt;&lt;&lt; Edit #3 - Bug Report >>></h2>
  
  <p>I filed a bug report with Embarcadero (QC #108475).  I will report back once the bug has been acknowledged / fixed.</p>
  
  <h2>&lt;&lt;&lt; Edit #4 - Escalated Bug Report >>></h2>
  
  <p>I found today that using this workaround will sometimes causes a <code>TClientDataSet</code> to throw an <code>EOleException</code> with the text '<strong>Catastrophic Failure</strong>'.  Apparently a <code>TClientDataSet</code> prefers a <code>MaxBlobSize := '-1';</code>.  Consequently, I escalated the bug report at Embarcadero.  Hopefully they will provide a fix or a better workaround for this soon.</p>