Passing huge amount of data as hex (0x123AB ...) parameter of clr stored procedure in sql server
I am posting this question after this question , since the thread is not getting more answers.
I'm trying to figure out if a large amount of data like "0x5352532F ..." can be passed as a parameter to a CLR stored procedure.
This is done in order not to send data directly to the CLR stored procedure, instead of sending ti to a temporary DB field and from there pass 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 a CLR stored procedure (not a path, full bits that make up the file). Sort of:
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 the stored proc that writes to the server path (on the file_remote_path) binary data that I pass as (file_contents).
2) is there a risk of corruption when taking this approach (or is it the same approach that sql server uses behind the scenes)?
3) how to convert file content to hexadecimal representation "0x23423 ..."
a source to share
What is your goal? Are you trying to move a file from the client file system to the server file system? If so, you can look at the web service file transfer mechanism.
Do you want to store data in a 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 can choose to save it as a file and store the string path in the database there, or store the contents of the file in a column VARBINARY(MAX)
.
If you want to get data in a database, you don't need a CLR procedure. You can store it directly in the database, or you can code a SQL stored proc to do this.
Assuming you keep the approach of sending this message to the CLR procedure:
1) is it possible, if so, how?
Of course, why not. The code you wrote looks like a good example. The stored proc will have to convert the string to bytes.
2) whether there is a corruption risk of adopting such an approach.
I'm not sure what you mean here. Will SQL Server randomly replace characters in your string? No. Could you accidentally apply some kind of limit? Yes, it is possible; the maximum size NVARCHAR(MAX)
is 2 ^ 31-1, or 2,147,483,647 characters. But I doubt you will have that PDF size. Can you lose connection between the file on disk and the database path to it? Yes, although I FILESTREAM
must take care of this for you.
3) how to convert file content to hexadecimal representation "0x23423 ..."
There are many examples on the Internet on how to do this. Here's one:
How can I convert a byte array to a hex string and vice versa?
a source to share