Save PDF File to MS SQL Server DB

General
1

Does anyone have any example of saving a binary file (in my case a PDF) to a MS SQL Server database field of type Varbinary(Max)?

I’ve tried to research this, but can find anything that directly applies and now my head is spinning in all kinds of different directions. Am I right to believe that in general, I need to go from FolderItem to BinaryStream to MemoryBlock to Byte Array to VarBinary Field?

– Rob

2

You need a folderitem to read in the binarystream into a memoryblock. Then you write this into the VarBinary field.

3

This is what I have tried so far.

[code] Dim f as FolderItem = GetOpenFolderItem(“pdf”)

Dim bs As BinaryStream
bs = BinaryStream.Open(f,False)

Dim mb As New MemoryBlock(bs.read(bs.Length))

bs.Close

’ Save PDF to Database

Dim cmd as New OLEObject(“ADODB.Command”)

cmd.ActiveConnection = app.cnDatabase
cmd.CommandType = 4
cmd.CommandText = “qrInsertInvPDF”
cmd.Parameters.Refresh
cmd.Parameters("@invid").Value = 1
cmd.Parameters("@invpdf").Value = MB
cmd.Execute[/code]

I get an error on the Dim mb as New MemoryBlock, expects inetger but is getting string. How do I correctly put the BinaryStream into the MemoryBlock?

– Rob

4

The Constructor for MemoryBlock takes an integer indicating the number of bytes, so your code won’t work.

Instead just try:

Dim mb As MemoryBlock = bs.Read(bs.Length)
5

Thanks. Changing the DIM statement worked. It looks like I also needed to change the MemoryBlock containing the PDF to string format to save the file. This is the code I have for saving the file.

[code]
Dim f as FolderItem = GetOpenFolderItem(“pdf”)

Dim bs As BinaryStream
bs = BinaryStream.Open(f,False)

Dim mb As MemoryBlock = bs.Read(bs.Length)

bs.Close

’ Convert MemoryBlock to String Data

Dim pdfstr as String
pdfstr = mb

’ Save PDF String Data to Database

Dim cmd as New OLEObject(“ADODB.Command”)

cmd.ActiveConnection = app.cnDatabase
cmd.CommandType = 4
cmd.CommandText = “qrInsertInvPDF”
cmd.Parameters.Refresh
cmd.Parameters("@invid").Value = 1
cmd.Parameters("@invpdf").Value = pdfstr
cmd.Execute[/code]

Now I need to read the data from the database to a format I can work with. I want to do two things with the PDF from the database: 1) Display the first page in a canvas and 2) Display the file by launching it.

I already have a canvas that can display the first page of a PDF using MBS DynaPDF but that accepts a PDF via drag/drop. I’m just not sure how to do the same with the data from a VarBinary field.

– Rob

6

I’m not familiar with DynaPDF, but if it can display a PDF file, then you can just load the binary data from the DB, write it to a binary file and then display the first page of the file.