Populate Database from PDF form data

Is this a LiveCycle form? or a form built with Acrobat? Is this an ODBC registered DB?

This is a form built in Acrobat. Yes this is an ODBC registered Database.

Sarah

Ok, So you are alread using the ADBC object? And the Statement Object?

Whether or not the ADBC object can be used to write to the DB depends a great deal on how the DB is setup, and the ODBC driver that's used to connect to it. I cannot speak to any of these points that are outside of Acrobat.

However, if the DB is setup correctly, then you would write to it using the standard SQL methods for doing this, Update, Insert, and Delete. The proper use of these methods is somewhat DB specific. I would suggest getting a book on SQL for Oracle

if you do a web search for the ADBC object you'll find some info on using it, but not much.

This is a sample of the script I am using inside the PDF form.

var test = ADBC.newConnection("test", "username", "password");
indb = test.newStatement();

var fn = this.getField("FIRST_NAME").value;
var ln = this.getField("LAST_NAME").value;
var ad = this.getField("ADDRESS").value;

var insert = "INSERT INTO \"FORM\" VALUES \"fn\", \"ln\", \"ad\"";

indb.execute(insert);

The problem is that I cannot get the SQL statement to recognize the field values. If I use literal values it works great. I'm new to this and am unsure on how to save the field values in a way that is recognized by the SQL statements.

Thanks for all your help.

Sarah

You're doing excellent so far!!

The variable "insert" is a string. So what you need is some info on string handling in JavaScript.

The fn, ln, and ad variables are also stirngs so you can just concatonate them all together. JavaScript, the "+" symbol is the string concatonation operator

var insert = "INSERT INTO FORM VALUES '" + fn + "', '" + ln +"', '" + ad + "'";

Notice that I replaced all the internal double quotes with single quotes. I also removed the double quotes from the table name. Use whatever works best for your SQL. I just like the single quotes.

Still no dice but I think I am close. I modifed the SQL statement so I could be abosolutely sure that I had the correct values based off the info. you gave me.

var test = ADBC.newConnection("test", "username", "password");
indb = test.newStatement();

var insert = "INSERT INTO FORM VALUES"
+" SET FIRST_NAME='"+this.getField("FIRST_NAME").value+"'"
+" LAST_NAME='"+this.getField("LAST_NAME").value+"'"
+" ADDRESS="+this.getField("ADDRESS").value;

indb.execute(insert);

Now when I execute this statement I receive a

GeneralError: Operation failed.
Statement.execute:12:AcroForm:Button1:Annot1:MouseUp:Action1

I'm at a loss.

Sarah

You're doing everything correctly that I can see, at least as far as the Acrobat JavaScript is concerned, I don't know about your SQL. If it works when the values are hard coded, but fails when you put in the variables, then something is wrong.

Add this line right after "insert" is created.

console.println(insert);

This will print out the SQL statement to the Console Window. If you haven't used the console before, it's the Number 1 debugging and developement tool for Acrobat JavaScript. You can read more about it here:

http://www.acrobatusers.com/tech_corners/javascript_corner/tips/2006/javascript_console/

Here's a sample SQL that I used to insert a new entry into an Access DB.

"INSERT INTO ComputerBooks (Title, Author, Price) VALUES ('My Book', 'Thomp', 100.00)"

I want to thank you for all your help on this one.

This is what worked

var test = ADBC.newConnection("test", "username", "password");

indb = test.newStatement();

var fn = this.getField("FIRST_NAME").value;
var ln = this.getField("LAST_NAME").value;
var ad = this.getField("ADDRESS").value;

var insert = "INSERT INTO FORM (FIRST_NAME,LAST_NAME,ADDRESS) VALUES ('" + fn + "', '" + ln +"', '" + ad + "')";

indb.execute(insert);


Sarah

Hi,

I am not a programmer but see you have found a solution. I need to have an interactive pdf form automatically export data in a mysql database on a web host server.

1. Please advise if I have to hire a programmer and what type?

2. Is there any simple software converstaion to allow me to get the pdf for setup to connect and store the data from the pdf

3. Also, once the pdf is filled out, is there any way for the pdf to be automatically stored in a file server on the web host so the actually pdf doc can be restrieved as well all the data from the for fields would be stored in the mysql database?

Cheers,
E.

In order to make the PDF populate our Oracle Database I first had to setup an ODBC connection on the client workstation. You can do this through the Adminstrative Tools>Data Sources (ODBC). If you are using Acrobat 8 you must activate ADBC by creating a registry entry. You can reference the Developing Acrobat Applications Using Javascript (Interacting with Databases section). In previous releases of acrobat this is already enabled.Once this is done you can use javascript ADBC.newConnection to establish the connection to the Database that you just created with the Data Sources administrative tool. Reference the Javascript for Acrobat API Reference they have good examples.

Then it is just a matter of creating an sql statement and executing the statement within the form to populate the Database. You can reference the sample code I provided in the previous post. I have the javascript set up as a button.

As far as storing the PDF in the server we utilize custom PHP application to allow for attachments inside the database.

Hope this helps.

Sarah

Hi,

wow thanks for the quick response. :-) I will try it out.

so you mentioned "As far as storing the PDF in the server we utilize custom PHP application to allow for attachments inside the database.
"

so yes the interactive pdf can be stored as n attachment in the database? did you hire a php person do this? can you do this? is there software app to do this?

Cheers,
E.

We utilize PHP Runner to build PHP applications. I have not personally built a PHP app. but am in the process of learning how to do it.

This is what PHP Runner does

"PHPRunner creates set of PHP pages to access and modify any
local or remote MySQL database. Advanced security options
allow to build password-protected members only Web sites
easily."

Check out there website at www.xlinesoft.com

s

Hi,

Just to confirm this pdf form would be web based and not on any local servers. Your comment on odbc and such and registry is just to get the pdf connectig to the web server then the pdf I would do what with then? We would then want to upload that pdf form with the correct scripts and such to our web host and then people on the web would open that pdf. Please advise.

ODBC and the ADBC object in Acrobat JavaScript work great for local databases. It's possible for an ODBC driver to connect to a remote DB, but I don't know what the options are here.

I'd say go the PHP route and hire a PHP programmer. For an experienced PHP programmer it's very simple to write a PHP script that recieves and extracts the contents from a PDF form submit. It's also fairly simple to access a DB from PHP. so this is a two step process. Submit the data from the PDF to a PHP script, when move the data into the DB.

This can also work in the other direction, with the PHP script servering up the PDF and even prepopulating it.

A tool like PHPRunner can be a part of this, but I don't know that it would handle everything that you need. Whatever PHP Runner create would probably need to be modified and you'll need a PHP programmer for this. And also for designing the entire methodology.

I stumbled across this conversation on a google search and was hoping one of you may be able to enlighten me as to a direction to go. I need to process a massive amount of valuations for realestate clients. My clients have field based online web forms that must be populated with a variation of the same types of data on comparable properties, be it sold or active on the market. The source of the data that is to be populated is on the MLS (Multiple Listing Service). Common data that must be populated in my clients web based forms are property addresses, days on market, pricing, dates, housing styles, etc. I was wondering if it was possible for this data to be harvested from the mls (either directly off the page or through a pdf created from a print to pdf, via distiller off the mls) and then auto populated into these clients specific web based forms. I am not operating at the level of understanding you all are, but I can understand concepts enough to present to a consultant to build this for me. My hope is that you can help me define a solution so I know where to begin looking for a consultant to create for me. Thanks in advance for reading this.

This forum is for Acrobat JavaScript questions. So you're in the wrong place.

But I'll give it a shot. I don't know anything about how the MLS listings are setup on the web. It makes sense that it has an RSS feed and/or a web service you could pull data from. Look for both of these. However, MLS data is propietary to the realestate brokers and I'm sure they protect it as much as they can.

Baring these two options, RSS and Web Service, you can always write a bot that crawls the MLS site and filters for the data you're looking for.