How to convert bas64 to varbinary(max)

Question

I need to pass base64 string to sql query as varbinary. I tried to get the binary value of base64 using

let printableReport = atob(base64)

And passing the printableReport value to sql update query.

I tried many ways to pass the binary value to sql query as varbinary(max).

let qry = `update ${tableName} set PrintableReport = CONVERT(varbinary(MAX),'${printableReport}') where TransactionId = '${transactionId}'`

ERROR: "message":"'ðÊ\u0016¼«þWÚC>\u0001õ³àÌ¥Îqq(&¢©\r\u0016*\u001bêäª°Á0'Ljï.\u001c»@t·$ë. ' is an invalid name because it contains a NULL character or an invalid unicode character."

let qry = `update ${tableName} set PrintableReport = (select * from OPENROWSET(BULK '${filePath}', SINGLE_BLOB) as varbinary(MAX)) where TransactionId = '${transactionId}'`

let qry = `update ${tableName} set PrintableReport = (SELECT CAST(bulkcolumn AS VARBINARY(MAX)) FROM OPENROWSET(BULK '${filePath}',SINGLE_BLOB) AS x) where TransactionId = '${transactionId}'`

let qry = `DECLARE @pdf VARBINARY(MAX) SELECT @pdf = BulkColumn FROM OPENROWSET(BULK N'${filePath}', SINGLE_BLOB) AS Document; SELECT @pdf, DATALENGTH(@pdf) update ${tableName} set PrintableReport = (@pdf) where TransactionId = '${transactionId}'`

ERROR: "message":"Cannot bulk load because the file \"C:\projects\result_binary.pdf\" could not be opened. Operating system error code (null)."

Please suggest me the right way to achieve this.

This answer might help, https://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql — mcnnowak, Nov 22 '19 at 17:17

score 0 · Answer 1 · answered Dec 02 '19 at 11:30

I can update record with binary value, which is converted from base64. Here is the query:

let qry = `update ${tableName} set PrintableReport = CAST(N'' AS XML).value('xs:base64Binary("${printableReport}")', 'VARBINARY(MAX)') where TransactionId = '${transactionId}'`

How to convert bas64 to varbinary(max)

1 Answers1