How to load/import a pdf file into azure dB table as base64 column

Question

How to load/import a pdf file into azure dB table as base64 column

balasach82

SSCrazy Eights

Points: 9653
More actions
October 23, 2019 at 11:52 am

#3691287

How to load/import a pdf file into azure dB table as base64 column. Is there an direct SQL method/alternative to .net way.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2173 More actions · Answer 1

By 'base64' column you must mean varbinary(max)? I've never done it with a pdf file before but if it is possible in t-Sql then OPENROWSET would be my guess as to how:

https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver15

There are a few gotcha's (read the whole docs) if the files are large-ish. Are you sure you want to store files in the db? I never do it. I rename the file with a guid and insert the guid into Sql Server as a uniqueidentifier column.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Lowell SSC Guru Points: 323518 More actions · Answer 2

we store files in our databases and varbinary(max)

you would be adding an extra step to convert varbinary to base64/varchar(max). then if you need the file, you have to double convert it again from base64 to varbianry, and then varbinary to a file.

Are you sure that's what you want? why not just store the varbinary?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 3

I have to store the PDF in azure DB, which will be extracted from .NET code as pdf again. Going with the Azure blob approach is an overhead for me, since I have to finally store the PDF in Azure table. I am thinking of Varchar(max) to store. But, wondering if there is a direct way in SQL itself (either through, say, query, powershell, json SQL), it would be good.

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 4

balasach82

SSCrazy Eights

Points: 9653

October 30, 2019 at 10:53 am

#3693028

Anyone ..any idea?

Steve Collins SSCrazy Points: 2173 More actions · Answer 5

Did you try openrowset?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können