Link to home
Start Free TrialLog in
Avatar of mfor101
mfor101Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Insert into varbinary(max) failing

Hi,

I am trying to insert an image and 2 thumbnails into varbinary(max) fields via a stored procedure and I am getting an error returned. If I execute the the SP direct and provide nulls for the fields then the row is created so I suspect the SP and Table are Ok. I also list the byte array sizes when it fails so I can see the buffers have some data, eg.

buf1: 61896
buf2: 11611
buf3: 5174

The c# code is:

SqlCommand objCommand = new SqlCommand();
// Add user id and display order (ints)
objCommand.Parameters.Add("@picTenId", SqlDbType.Int).Value = strId;
objCommand.Parameters.Add("@picOrderId", SqlDbType.Int).Value = "1";

// create and add byte array buffers
byte[] bufM = new byte[0];
bufM = new FixedResizeConstraint(620, 410).SaveProcessedImageToByteArray(strImageF);
objCommand.Parameters.AddWithValue("@picMain", bufM);
byte[] bufT1 = new byte[0];
bufT1 = new FixedResizeConstraint(200, 132).SaveProcessedImageToByteArray(strImageF);
objCommand.Parameters.AddWithValue("@picThumb1", bufT1);
byte[] bufT2 = new byte[0];
bufT2 = new FixedResizeConstraint(116, 77).SaveProcessedImageToByteArray(strImageF);
objCommand.Parameters.AddWithValue("@picThumb2", bufT2);

// run the stored procedure
Int32 lngResult = utlData.Run_Procedure("hex_setPicture", objCommand);

The function adds a final 'result' output parameter to the command object, hopefully to return a new row Id. The function works well with inserts to other tables so I also don't suspect a big problem here.

The function returns -1 which is my error setting.

I'm a bit stumped as I've tried every thing I can think of - any ideas would be gratefully received.  
 
Avatar of DavidMorrison
DavidMorrison
Flag of United Kingdom of Great Britain and Northern Ireland image

Hi mfor101

before I start, just a little aside, the general wisdom with sql server is to not store images and documents etc into the db, rather just store a relative path. If this is an option then this would obviously solve your issue :)

If not then can I ask why you're using varbinary rather than the dedicated "image" datatype?


Thanks

Dave
Avatar of mfor101

ASKER

I've read many opinions on pros and cons for using DB to store images. In the current incarnation of this application we used the file system and it has been a real pain in many ways. We have decided to try and use DB in this version to see if it is any easier to use/manage.

I don't really want a long debate about this as we have made a decision already, it's just I have a problem making it work when it seems thousands of others don't! :-(

I think the image datatype is deprecated in SQL 2005.
That's fair, personally I think file storage in the db is a really bad idea but as you say the decision has been made :)

Yes the image type is deprecated, sorry I worded my initial statement badly, what I meant to say was have you tried it with the image type to see if it works?

Also what is the error you are getting? is it a SQL server error?


Thanks

Dave
Avatar of mfor101

ASKER


I haven't tried the image type as I assumed it was simply the same thing via a different name and that the only the name was being maintained for compatibility.

I'm not getting a visible error other than my 'execute' function returns -1 and no row is created in the database. I am assuming I am making an error in my preparation before execution, setting parameters etc.

Is there no way to step into the execute function in order to see the real error message?
If not then you could set up sql profiler to see what exactly is being sent to the server
ASKER CERTIFIED SOLUTION
Avatar of mfor101
mfor101
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mfor101

ASKER

The error was in a different part of the process from the one identified in the question. I found this myself during ongoing attempts to resolve the issue.
Lol no worries, glad you sorted it anyway :)

If you want more input over the DB vs file system debate let me know :)
Avatar of mfor101

ASKER

Thanks David,

Each user can supply up to 5 images (or none). One has to be indicated as the primary image and the others are displayed in a user specified sort order. These parameters can change as the user replaces an image with a new one, promotes/demotes images etc. In addition an external moderator checks all images before they can be displayed (decency etc) and thus the image is invalid until checked.

We can't manage this without a DB record for each image and we have had several problems managing the images in the current system via the file system. As the DB is on the same server as the site and it's not heavily hammered it seems there may be some simplification in keeping the images with the DB record we have to have anyway.

 
Ok all sounds sensible, the main issue with storing large objects such as images in the DB is that the storage engine just isn't designed to do it really and this causes fragmentation and a ton of un-needed IO.

Have you looked at FILESTREAM as a possible alternative?  http://technet.microsoft.com/en-us/library/bb933993.aspx

Thanks

Dave