Saving PDF into db table as BLOB

Hello all knowing ones,

I am looking for a generic method to insert PDF files into a MS SQL table. My reason for inserting the whole PDF is that access is needed from different locations and not all have access to the file system. This is for my help documentation system.

Thanks,

Adam

First, you need to make a table in the MS SQL database that contains three columns: [code]Table: PDF_Files
Name Data Type Info


ID int auto_increment
Filename varchar(255)
FileBytes varbinary(max)[/code]
Once you have that you put the following script in a button on the actionPerformed action:path = fpmi.file.openFile('pdf') # This will open an open file dialog to select the pdf if path != None: bytes = fpmi.file.readFileAsBytes(path) fpmi.db.runPrepStmt("INSERT INTO PDF_Files (Filename, FileBytes) VALUES (?,?)", [path,bytes])
Now, to get the data out and display it in the PDF Viewer compnent of the Reporting Plugin, you can do the following:result = fpmi.db.runQuery("SELECT Filename, FileBytes FROM PDF_Files WHERE ID = 1") if result != None: event.source.parent.getComponent("PDFViewer").setBytes(result[0][1]) event.source.parent.getComponent("FileLabel").text = result[0][0]
Hope this helps.

Adam - a few loosely related points:

  1. Travis showed how you can allow users to upload files in FactoryPMI. You will probably want to initially populate your database table more directly.

  2. I couldn’t find an easy way to upload BLOB files (varbinary(max)) graphically in SQL Server Management Studio Express. In the MySQL Query Browser you click a small folder icon in the field that you’re editing and it prompts an upload window. They also have a preview icon to open the BLOB as a file. Perhaps a different front end supports that.

  3. The Reporting Plugin PDF Viewer component is useful because it natively reads pdf files on any platform with consistency. Alternatively, the Free ActiveX plugin uses the version of Adobe Acrobat located on the Windows client. It can be a little more feature rich and error prone (depends on externally installed software).

  4. The way I envision these components (PDF Viewer, Document Viewer, PDF ActiveX plugin) to work is with the option to use a file directly instead of via a file path - like the Sound Player component. That way you can simply bind the property to a simple SQL query with a dynamically generated WHERE clause. I entered this as a feature request. Keep in mind that you can accomplish the same results with script.

  1. The articles below discuss using BLOBs with SQL Server. You can ignore the Visual Studios examples. It contains useful information including a batch upload query in Part 2.

databasejournal.com/features … Server.htm

databasejournal.com/features … Part-2.htm

databasejournal.com/features … Part-3.htm

And there’s a Part 4.

Nathan and Travis,

I am attempting to “preload” the database with the pdf’s. The user will not have the need to do this, at least for this application. my main goal, as stated before, is to have files available to all users (help files) from all sites, especially ones that do not have access to the file system.

I already am doing this for reports, but I did not know if there is an easy method to save a file to the db.

OK. in execution of the VIEWING script, I am getting an error in the attribute “setBytes”.

Does it make a difference that I am using FPMI version 3.1.6? In a previous similar function we used a temp file to view BLOBs from db. I like this direct method better.

Thanks for the help,

Adam

The setBytes function was available from version 1.1.13 of the Reporting Plugin. If you don’t have that version you can upgrade or use the setFilename function. Here is how you can use the setFilename function:result = fpmi.db.runQuery("SELECT Filename, FileBytes FROM PDF_Files WHERE ID = 1") if result != None: event.source.parent.getComponent("FileLabel").text = result[0][0] filename = fpmi.file.getTempFile("pdf") fpmi.file.writeFile(filename, result[0][1]) event.source.parent.getComponent("PDFViewer").setFilename(filename)

OK. Problem resolved. I do have an older version of the report writer. What was done was to use a temp file to load the BLOB into, then use that file to load into the PDF Viewer. This is working great, and is also customizable for all of my HELP files.

Thanks Travis for your help.

Adam