Our application stores image binary

[BobRodes]

Our application stores image binary data in a table called Blob. I'm trying to update the Blob field, and running into the above error. Here's the code I'm using (C#):

command.CommandText = @"
        UPDATE  dbo.Blob
        SET     Binary = @Binary
        WHERE   BlobGuid = @BlobGuid";

command.Parameters.Add("@BlobGuid", System.Data.SqlDbType.UniqueIdentifier).Value = blobGuid;
command.Parameters.Add("@Binary", System.Data.SqlDbType.Image).Value = dataBuffer;
command.ExecuteNonQuery();

This throws the error "String or binary data would be truncated. The statement has been terminated." While I understand that this means that I'm trying to put data into a field that doesn't have room for it, I can't see how in this case. dataBuffer.length (dataBuffer is a byte array) in the sample is 780831, which is the size of the image file, and the Image field holds 2^31 - 1 bytes, which is more than enough. What's more, I don't have any trouble adding the file to a new Blob record.

The initial code used System.Data.SqlDbType.VarBinary instead of Image. I first tried [tt]System.Data.SqlDbtype.VarBinary, dataBuffer.Length[/tt], with the idea that VarBinary's default size was the old-fashioned 8.000 bytes, but that had the same problem.

Can anyone enlighten me?

An unforeseen consequence of the information revolution has been the exponential propagation of human error.

 

[SQLBill]

I don't use Image Data Type, so I'm not sure this is the issue. BOL states when updating Image values:

Specify relatively short amounts of data in an UPDATE statement just like the char, nchar, or binary data is specified.

The key there is 'relatively short amounts of data'. Maybe the warning isn't that there isn't enough space, but that the amount of data is too much for the UPDATE statement itself.

Try using WRITETEXT.

Or I might be completely wrong

-SQLBill



The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[BobRodes]

Thanks for your answer Bill. I did notice that WRITETEXT thing. However, if it's not too long for the INSERT, why is it too long for the UPDATE? Maybe because it has to shuffle stuff around because it's segmented on the disk or something? If so, perhaps the easy way to handle it is to simply delete the old blob and add a new one with the same primary key value?

An unforeseen consequence of the information revolution has been the exponential propagation of human error.

 

[BobRodes]

Looking again, same primary key value wouldn't work well because of constraints. Just add a new blob and tie it into the photo table, etc. ?

An unforeseen consequence of the information revolution has been the exponential propagation of human error.

 

[gmmastros]

What version of SQL server are you using? I think it was SQL2008 that add FILESTREAMs. This allows you to basically store your images on the file system instead of in the database. I suspect this will make for a smaller database, faster performance and also be simpler to use. I encourage you to do a little research on this to evaluate it for your purposes.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[BobRodes]

Thanks George. We're using 2008, and that decision isn't in my hands. But I will bring it up. I thought that was what we were doing, but the code doesn't mention FILESTREAMs.

An unforeseen consequence of the information revolution has been the exponential propagation of human error.

 

[JoeAtWork]

Using VB and ADO you could insert/update a blob field by chopping up the data into chunks and sending each chunk one at a time with ADO command (I think it was AppendChunk or something like that). There is probably some ADO.NET equivalent.

 

[craigber]

Image will be removed in a future version of SQL Server (

http://msdn.microsoft.com/en-us/library/ms187993.aspx).

You should use VarBinary(Max) instead. Make that change, then retest.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[BobRodes]

Already using VarBinary(max). Sorry for the lack of clarity.

@Joe: yeah, I remember that from the VB6 days too. I was hoping to avoid chunking and looping. I did manage to solve the problem by deleting the old blob and inserting a new one, since the blob is in its own table. This solution was the simplest with respect to manipulating the blobs, however it did add the complication of having to replace a foreign key in the photo table with the new primary key.

An unforeseen consequence of the information revolution has been the exponential propagation of human error.