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

Answer 1

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:

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

Answer 2

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