?
Solved

Insert into varbinary(max) failing

Posted on 2011-10-26
10
Medium Priority
?
971 Views
Last Modified: 2013-12-14
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.  
 
0
Comment
Question by:mfor101
  • 5
  • 5
10 Comments
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 37029962
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
0
 

Author Comment

by:mfor101
ID: 37029988
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.
0
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 37030016
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:mfor101
ID: 37030033

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.

0
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 37030072
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
0
 

Accepted Solution

by:
mfor101 earned 0 total points
ID: 37030107
I'm sorry but I think I have just found out the problem, nothing to do with the varbinary fields at all. The initial parameter taken from variable strId was set as "" and so caused the SP to fall over immediately as it wasn't an int as expected.

Talk about taking your eye off the ball and making untested assumptions!!

Sorry to have wasted your time, you did make me rethink using the file system rather than DB however.  

Thanks again
0
 

Author Closing Comment

by:mfor101
ID: 37055355
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.
0
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 37030177
Lol no worries, glad you sorted it anyway :)

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

Author Comment

by:mfor101
ID: 37030204
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.

 
0
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 37030284
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
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question