12

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?

esausilva
  • 1,964
  • 4
  • 26
  • 54
  • The website should be adding the correct file / mime-type headers, shouldn't it? – Aaron Bertrand Sep 17 '14 at 17:51
  • Yes, the documents I upload via the webservice are downloading fine. This is what the website has `Response.ContentType = "application/pdf" Response.AppendHeader("Content-Disposition", "attachment; filename=" & packetName.Replace(".", "_")) Response.BinaryWrite(mergedPdfBytes) Response.End()` – esausilva Sep 17 '14 at 17:57
  • I think you need to put that code in the question (not the comment), and also describe what the webservice does different from the database update. Also how did you come up with that `'JVBER...'` string, and should it be `N'JVBER...'`? Also are you sure you want to store PDF files in a VARBINARY(MAX) column? Most have better luck on the file system or using Filestream or FileTable... – Aaron Bertrand Sep 17 '14 at 18:00
  • A windows application converts the PDFs to base64 and passes that to the webservice, the service takes the PDF base64 in a byte array and then is just an insert command in a stored procedure. The webservice code looks like this `cmd.Parameters.Add("@file_bytes", SqlDbType.Binary).Value = fileData.Bytes;` .... this application is already in production, so changing the data type is not an option – esausilva Sep 17 '14 at 18:11
  • So try changing your update to use the `N` prefix on the string - I bet it's Unicode (and you lose that when you don't prefix it correctly). – Aaron Bertrand Sep 17 '14 at 18:12
  • Added the `N` prefix and still the same thing – esausilva Sep 17 '14 at 18:35
  • Ok, sorry, you've exhausted my expertise here. (I learned over a decade ago to not jam files into SQL Server. You will probably learn this lesson too.) – Aaron Bertrand Sep 17 '14 at 18:37
  • I might need to create a little web interface to do this :( , and this is what I was hoping to avoid since this is just a one time thing...thanks – esausilva Sep 17 '14 at 19:03

2 Answers2

18

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');
Marc-André Gosset
  • 181
  • 1
  • 3
  • 4
    You don't need the variable. `cast('/9j/4AAQSkZJRgABAQEAAAAAAAD/==' as xml).value('xs:base64Binary(.)', 'varbinary(max)')` Since every character in base64 is considered text in xml, the whole string becomes a single text node. – Markus Jarderot Jan 22 '21 at 09:49
3

Convert a base64 to varbinary

SELECT
   Id,
   AttachmentBase64, --the base64 value we want converted to varbinary
   CAST(AttachmentBase64 AS xml).value('xs:base64Binary(.)', 'varbinary(max)') AS AttachmentBinary
FROM Attachments

Convert varbinary column to base64

SELECT
   Id,
   AttachmentBinary, --the varbinary value we want converted to base64
   CAST('' AS XML).value('xs:base64Binary(sql:column("AttachmentBinary"))', 'varchar(max)') AS AttachmentBase64
FROM Attachments
Ian Boyd
  • 246,734
  • 253
  • 869
  • 1,219