Issues with writing BLOB data in chunk (8k)

Question

I'm trying to write BLOB data on to a word file. Here is my code

Dim reportID As Integer reportID = table1.report_output_data_id

       Dim aSqlStr As String = "SELECT file_data FROM table2 WHERE report_output_data_id = " + Convert.ToString(reportID )
       Dim reader As SqlDataReader = CType(WebSession.DataObjectFactory.GetDataProvider("EGDatabase"), cDataProviderSQL).PopulateDataReader(aSqlStr)

       ' The size of the BLOB buffer.
       Dim bufferSize As Integer = 8192
       ' The BLOB byte() buffer to be filled by GetBytes.
       Dim outByte(bufferSize - 1) As Byte
       ' The bytes returned from GetBytes.
       Dim retval As Long
       ' The starting position in the BLOB output.
       Dim startIndex As Long = 0
       Do While reader.Read()
           ' Reset the starting byte for a new BLOB.
           startIndex = 0
           ' Read bytes into outByte() and retain the number of bytes returned.
           retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize)

           ' Continue while there are bytes beyond the size of the buffer.
           Do While retval = bufferSize
               Response.BinaryWrite(outByte)

               ' Reposition start index to end of the last buffer and fill buffer.
               startIndex += bufferSize
               retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize)
           Loop
           Response.BinaryWrite(outByte)
       Loop
       reader.Close()

I'm writing 8k at a time as I had out of memory issues earlier when data is large say 1GB. Instead of the above code, If I use

Response.BinaryWrite(table2.file_data)

Everything works fine.

So please tell me whats the issue in using sqldatareader?

The file size currently I'm considering is 31794 bytes

FYI: I'm using CommandBehavior.SequentialAccess

score 1 · Accepted Answer · answered May 04 '13 at 19:19

Junk data was getting added on to the file while writing the BLOB data.

For example for a file size 33959 bytes

Loop 1 will process 0 - 8192 bytes ( increments of 8k) Loop 2 8192 - 16384 Loop 3 16384 - 24576 Loop 4 24576 - 32768 Loop 5 32768 - 33959 ( actual file size)

retval = reader.GetBytes(0, startIndex, outByte, 0, bufferSize)

Here in the loop 5 we have only 1191 bytes. So in the outbyte array the first 1191 items will be replaced with the bytes read by reader at this point of time which is 1191 but the remaining items in the outbyte array will still hold junk values ( that is present from previous loops) as the outbyte array is 8k in size.

So used a new outbyte array instead of the default one(8k) if remaining data to be written is less than 8k. Here is the code changes.

Dim aSqlStr As String = "SELECT datalength(file_data),file_data FROM table2 WHERE report_output_data_id = " + Convert.ToString(reportID ) Dim reader As SqlDataReader = CType(WebSession.DataObjectFactory.GetDataProvider("EGDatabase"), cDataProviderSQL).PopulateDataReader(aSqlStr)

   ' The size of the BLOB buffer.
   Dim bufferSize As Integer = 8192
   ' The BLOB byte() buffer to be filled by GetBytes.
   Dim outByte(bufferSize - 1) As Byte
   ' The bytes returned from GetBytes.
   Dim retval As Long
   ' The starting position in the BLOB output.
   Dim startIndex As Long = 0
           Do While reader.Read()
               ' Get file size from BLOB buffer.
               fileSize = reader.GetInt32(0)
               If fileSize > bufferSize Then

                   ' Reset the starting byte for a new BLOB.
                   startIndex = 0
                   ' Read bytes into outByte() and retain the number of bytes returned.
                   retval = reader.GetBytes(1, startIndex, outByte, 0, bufferSize)

                   ' Continue while there are bytes beyond the size of the buffer.
                   Do While retval = bufferSize
                       Response.BinaryWrite(outByte)
                       ' Reposition start index to end of the last buffer and fill buffer.
                       startIndex += bufferSize
                       Dim aRemainingBytes = fileSize - startIndex
                       If Not aRemainingBytes < bufferSize Then
                            retval = reader.GetBytes(1, startIndex, outByte, 0, bufferSize)
                       Else
                            Dim outByteRemaining(aRemainingBytes - 1) As Byte
                            retval = reader.GetBytes(1, startIndex, outByteRemaining, 0, aRemainingBytes)
                            Response.BinaryWrite(outByteRemaining)
                       End If
                    Loop
                Else
                    Response.BinaryWrite(aReportOutput.GetRelatedPropertyValue("ReportOutputData.FileData"))
                End If
           Loop
           reader.Close()

Issues with writing BLOB data in chunk (8k)

1 Answers1