Menu
I have PDF documents stored in my table as binary, the column that stores the bytes for the PDFs is type varbinary(max). I want to update one record with an updated document in SQL Studio, the way I am attempting to accomplish this is like below
UPDATE table
SET file_bytes=CONVERT(varbinary(max),'JVBERi0xLjYNCiW2JqDQo8PC9UeX...0YNCg==') --this is a base64 string
WHERE id='73c75254-ad86-466e-a881-969e2c6e7a04';
The query runs, but when I try to download the document (via the website), it throws an error message that reads PDF header signature not found.
Is this conversion even possible?
397
SqlException: 'Implicit conversion from data type varchar to varbinary(max) is not allowed.
varbinary [ ( n | max) ] Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes.
VarBinary is a variable width data type. The syntax for declaring Binary variable is varbinary(n) , where n defines the maximum size in bytes. The varbinary data type uses actual length of the data entered + 2 bytes as the storage.
VARBINARY(MAX) - Binary strings with a variable length can store up to 2^31-1 bytes. IMAGE - Binary strings with a variable length up to 2^31-1 (2,147,483,647) bytes.
It is possible by using the approach described here : https://blogs.msdn.microsoft.com/sqltips/2008/06/30/converting-from-base64-to-varbinary-and-vice-versa/
It's a two-step process, first you declare a variable :
declare @str varchar(max) = '/9j/4AAQSkZJRgABAQEAAAAAAAD/==';
Then you can use the variable in your SQL statement as follow :
INSERT INTO Documents (Name, Body, MIMEType)
VALUES('12446_photo.jpg', cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(max)'), 'image/jpeg');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
