Passing Binary Data to a Stored Procedure in SQL Server 2008

Question

I'm trying to figure out a way to store files in a database. I know it's recommended to store files on the file system rather than the database, but the job I'm working on would highly prefer using the database to store these images (files).

There are also some constraints. I'm not an admin user, and I have to make stored procedures to execute all the commands. This hasn't been of much difficulty so far, but I cannot for the life of me establish a way to store a file (image) in the database.

When I try to use the BULK command, I get an error saying "You do not have permission to use the bulk load statement." The bulk utility seemed like the easy way to upload files to the database, but without permissions I have to figure a work-a-round.

I decided to use an HTML form with a file upload input type and handle it with PHP. The PHP calls the stored procedure and passes in the contents of the file. The problem is that now it's saying that the max length of a parameter can only be 128 characters.

Now I'm completely stuck. I don't have permissions to use the bulk command and it appears that the max length of a parameter that I can pass to the SP is 128 characters.

I expected to run into problems because binary characters and ascii characters don't mix well together, but I'm at a dead end...

Thanks

I've been stuck with this problem for a couple days now and I've looked around for answers and even had asked another similar question. — Joe Majewski
– Joe Majewski, Commented Mar 16, 2010 at 16:09

Santiago Cepas · Accepted Answer · 2010-03-16 16:55:02Z

0

Here is an example I've found in David Hayden's blog.

It's a c# example, but the steps should be similar in PHP:

Convert your uploaded file to a byte array
Execute dynamic TSQL on the server

answered Mar 16, 2010 at 16:55

Santiago Cepas

4,1322 gold badges28 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Joe Majewski Over a year ago

Thanks for the example. I'm going to a meeting to figure out this whole mess, as right now I don't think I have the correct permissions to get it working.

user1945782 Over a year ago

This answer should have had more detail - the linked article has gone now.

Dmitry Over a year ago

The link is not valid anymore

Krohnus Melavea May 20 at 19:53

link is dead. downvoted

Marcus Adams · Accepted Answer · 2010-03-17 22:47:37Z

-1

In general, we don't pass binary data in SQL. We upload the file to the server, then load the image from the server into the database.

Load the image into the database from a file:

UPDATE images
SET image = LOAD_FILE('images/myimage.jpg')
WHERE image_id = 1234

Get the image back out to a file:

SELECT image
INTO DUMPFILE 'images/myimage.jpg'
FROM images
WHERE image_id = 1234

answered Mar 17, 2010 at 22:47

Marcus Adams

54k9 gold badges98 silver badges145 bronze badges

1 Comment

Krohnus Melavea May 20 at 19:53

does not answer the question. op did not ask if it was practice, he asked how to do it

Collectives™ on Stack Overflow

Passing Binary Data to a Stored Procedure in SQL Server 2008

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related