Passing huge amounts of data as an hexadecimal (0x123AB...) parameter of a clr stored procedure in sql server

Question

I post this question has followup of This question, since the thread is not recieving more answers.

I'm trying to understand if it is possible to pass as a parameter of a CLR stored procedure a large amount of data as "0x5352532F...".

This is to avoid to send the data directly to the CLR stored procedure, instead of sending ti to a temporary DB field and from there passing it as varbinary(max) parmeter to the CLR stored procedure.

I have a triple question:

1) is it possible, if yes how? Let's say i want to pass a pdf file to the CLR stored procedure (not the path, the full bits that make up the file). Something like:

exec MyCLRStoredProcs.dbo.insertfile
@file_remote_path ='c:\temp\test_file.txt' ,
@file_contents=0x4D5A90000300000004000.... --(this long list is the file content)

where insertfile is a stored proc that writes to the server path (at file_remote_path) the binary data I pass as (file_contents).

2) is it there corruption risk of adopting this approach (or it is the same approach that sql server uses behind the scenes)?

3) how to convert the content of a file into the "0x23423..." hexadecimal representation

Answer 1

What is your goal? Are you trying to transfer a file from the client filesystem to the server filesystem? If so, you might want to look at a web service file transfer mechanism.

Do you want to persist the data into the database? If so, and you have access to SQL Server 2008, I recommend looking at the new FILESTREAM type. This type maintains the link between the database and the file system for you.

Alternatively, if you don't have SQL Server 2008, you get to choose between saving it as a file and maintaining a string path to it in the database or storing the contents of a file in a VARBINARY(MAX) column.

If all you want is to get the data into the database, you don't need a CLR proc. You can save it directly to the database, or you can code a SQL stored proc to do so.

Assuming you keep the approach of sending this to a CLR proc:

1) is it possible, if yes how?

Sure, why not. The code you wrote looks like a good example. The stored proc will need to convert the string into bytes.

2) is it there corruption risk of adopting this approach

I'm not sure what you mean here. Will SQL Server randomly replace characters in your string? No. Might you accidentally hit some sort of limit? Yes, possibly; the maximum size of NVARCHAR(MAX) is 2^31-1, or 2,147,483,647 characters. But I doubt you'd have a PDF that size. Might you lose the link between the file on disk and the database path to it? Yes, though FILESTREAM should take care of that for you.

3) how to convert the content of a file into the "0x23423..." hexadecimal representation

There are many examples on the Internet on how to do this. Here's one:

http://stackoverflow.com/questions/311165/how-do-you-convert-byte-array-to-hexadecimal-string-and-vice-versa-in-c