Databases
StackOverflow2013
Postsdbo
POCertain Files getting corrupted by SQL Server FileStream
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCertain Files getting corrupted by SQL Server FileStream
    primarykey
    data
    text
    <p>I am saving files to a SQL server 2008 (Express) database using FILESTREAM, the trouble I'm having is that certain files seem to be getting corrupted in the process.</p> <p>For example if I save a word or excel document in one of the newer formats (docx, or xslx) then when I try to open the file I get an error message saying that the data is corrupted and would I like word/excel to try and recover it, If I click yes office is able to 'recover' the data and opens the file in compatibility mode. </p> <p>However if i zip the file first then after extracting the contents I'm able to open the file without a problem. Strangely If I save an mp3 file to the database then I have the reverse issue, I can open the file no problem, but If I saved a zipped version of the mp3 I can't even extract the contents of that zip. When I tried to save a pdf or power-point file I ran into similar problems (the pdf i could only read if I zipped it first, and the ppt I couldn't read at all).</p> <p>Update: here's my code that I'm using to write to the database and to read</p> <p>To write to the database:</p> <pre><code>SQL = "SELECT Attachment.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() FROM Activity " + "WHERE RowID = CAST(@RowID as uniqueidentifier)"; transaction = connection.BeginTransaction(); command.Transaction = transaction; command.CommandText = SQL; command.Parameters.Clear(); command.Parameters.Add(rowIDParam); SqlDataReader readerFS = null; readerFS= command.ExecuteReader(); string path = (string)readerFS[0].ToString(); byte[] context = (byte[])readerFS[1]; int length = context.Length; SqlFileStream targetStream = new SqlFileStream(path, context, FileAccess.Write); int blockSize = 1024 * 512; //half a megabyte byte[] buffer = new byte[blockSize]; int bytesRead = sourceStream.Read(buffer, 0, buffer.Length); while (bytesRead &gt; 0) { targetStream.Write(buffer, 0, bytesRead); bytesRead = sourceStream.Read(buffer, 0, buffer.Length); } targetStream.Close(); sourceStream.Close(); readerFS.Close(); transaction.Commit(); </code></pre> <p>And to read:</p> <pre><code> SqlConnection connection = null; SqlTransaction transaction = null; try { connection = getConnection(); connection.Open(); transaction = connection.BeginTransaction(); SQL = "SELECT Attachment.PathName(), + GET_FILESTREAM_TRANSACTION_CONTEXT() FROM Activity" + " WHERE ActivityID = @ActivityID"; SqlCommand command = new SqlCommand(SQL, connection); command.Transaction = transaction; command.Parameters.Add(new SqlParameter("ActivityID", activity.ActivityID)); SqlDataReader reader = command.ExecuteReader(); string path = (string)reader[0]; byte[] context = (byte[])reader[1]; int length = context.Length; reader.Close(); SqlFileStream sourceStream = new SqlFileStream(path, context, FileAccess.Read); int blockSize = 1024 * 512; //half a megabyte byte[] buffer = new byte[blockSize]; List&lt;byte&gt; attachmentBytes = new List&lt;byte&gt;(); int bytesRead = sourceStream.Read(buffer, 0, buffer.Length); while (bytesRead &gt; 0) { bytesRead = sourceStream.Read(buffer, 0, buffer.Length); foreach (byte b in buffer) { attachmentBytes.Add(b); } } FileStream outputStream = File.Create(outputPath); foreach (byte b in attachmentBytes) { byte[] barr = new byte[1]; barr[0] = b; outputStream.Write(barr, 0, 1); } outputStream.Close(); sourceStream.Close(); command.Transaction.Commit(); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USJack
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCertain Files getting corrupted by SQL Server FileStream
      1. USPritesh
      1. VTFavorite
    2. VO
      singulars
      1. POCertain Files getting corrupted by SQL Server FileStream
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POCertain Files getting corrupted by SQL Server FileStream
      1. USCanMan
      1. VTFavorite
    1. COCould be that the corruption occurs in your client handling of the inserts? Eg. premature truncation, or use use a varchar() vs. nvarchar() argument type passed to SQL, or char vs. binary? Woudl you care to post the code you use to upload the content into the filestreams and the code used to access the filestream?
      singulars
      1. POCertain Files getting corrupted by SQL Server FileStream
      1. USRemus Rusanu
    2. COThanks for the reply, I edited my question with my code. I'm not sure what you mean by using the wrong type argument, the field in sql-server is of type Varbinary(max) FILESTREAM).
      singulars
      1. POCertain Files getting corrupted by SQL Server FileStream
      1. USJack
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload