Merge SQL fields into existing PDF

Question

Hello. I'm researching for the new VB project and hoping to get some answers. We have a lot of PDF documents and would like to save information filled in by the user in SQL database and then pre-populate the answers in multiple PDFs for printing. We'd also want user to digitally sign the pdf and store the signature in database. Ideally I'd like to create VB project with form that users would fill in and then information should "flow" into existing PDF. Then later I'd setup some reports based on user-filled information. We probably will purchase Adobe LifeCycle software to use with PDF. Please advise!

Answer 1

Partial answer:

You can store the PDFs as BLOBs in a varbinary(max) column in the database.

You can use SSIS Import Column  to upload and Export Column Transformation to download into the file system.

You can also use the OPENROWSET with single_blob BULK command to import/export.


There is no special software in SQL Server 2008 to deal with PDF-s, they are just binary objects.

In the file system however, you can do whatever processing you need independently of SQL Server.

So you can export it out from the database, change it, import it back.

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 2

Well - I'd need to generate the reports based on the information in those PDF. If I import PDF as an object - I won't be able to accomplish it, correct? I need to have data for those PDF stored in the table and have an ability to pull digital signatures from signed PDF(s) back into SQL - into fields in the table as well. Basically it'll be huge table with data that is "merged" to multiple PDF documents. I'd like for the user to type in information for the client, press a button and see all PDFs for this client. Then client can sign each PDF on the tablet, print those PDFs and I'd need to store the signatures for each PDF in SQL. Thanks

Answer 3

Well - I'd need to generate the reports based on the information in those PDF. If I import PDF as an object - I won't be able to accomplish it, correct?

It depends. You can do one operation with PDF-s (even though they are BLOBs) in SQL Server 2005/2008, full-text search (ifilter):

how to use word and pdf filter in full text search (SQLServer2k5)

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

Answer 4

Thanks. I don't have the access to that site at work - will have to check later from home. Let's say I decide to go this route and import PDF as an object. How would I know which PDF is for which client? PDF file has about 30 pages and generic name - not client-specific. Does it store each PDF as an image in 1 column cell in SQL? Sorry if the questions are stupid - I'm new to it..

Answer 5

Usually store info & object in a few columns like:

PathFileName varchar(256)
Customer varchar(128)
DateOrigin date
PDFobject varbinary(max)

The first 3 columns are regular SQL data types. Regular SQL queries.

The fourth one, object, import/export and FULL-TEXT search

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com