Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

...I enjoy spending time on it for personal growth (I learn from the questions I don't answer, and I reinforce/stay sharp on the topics I do answer), and to give back to humanity at large...
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming Forum

Our application stores image binary

thread183-1687059
Read More Threads Like This One
BobRodes (Instructor)
28 Jun 12 16:08
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#):

CODE

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 System.Data.SqlDbtype.VarBinary, dataBuffer.Length, 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 (MIS)
28 Jun 12 16:43
I don't use Image Data Type, so I'm not sure this is the issue. BOL states when updating Image values:

Quote:


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: What should I know before I post?

BobRodes (Instructor)
28 Jun 12 16:48
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 (Instructor)
28 Jun 12 17:01
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 (Programmer)
29 Jun 12 12:14
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 (Instructor)
29 Jun 12 14:47
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 (Programmer)
11 Jul 12 0:16
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 (Programmer)
11 Jul 12 10:27
Image will be removed in a future version of SQL Server (http://msdn.microsoft.com/en-us/library/ms187993.a...). You should use VarBinary(Max) instead. Make that change, then retest.

Craig Berntson
MCSD, Visual C# MVP, www.craigberntson.com/blog
BobRodes (Instructor)
11 Jul 12 12:17
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.

Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft SQL Server: Programming

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close