Interacting with a database

[Aditya_S_]

I am interested to connect my form to a database to query some information for validations. I have already created a connection through Administrative Tools>>ODBC and added an new OLE DB connection.

However, I would like to retreive data by providing parameters. Like select firstname from names where id = textField1.rawValue;

How would I accomplish that ? Also where would I find reading material on it ?

Aditya

[Former Community Member]

There is a blog entry on Stefan Cameron's blog that describes this:

http://forms.stefcameron.com/2006/09/29/selecting-specific-database-records/

Paul

[Aditya_S_]

Thanks Paul !

Does the form have to be Reader Extended for the script to work ?

Aditya

[Former Community Member]

Yes ....Only LiveCycle Server can turn on that bit

Paul

[Aditya_S_]

Can a PDF connect to a database from any machine ? if not, how can I make it happen ?

I am trying to email a form around which will perform database validations before submissions.

Aditya

[Former Community Member]

It has to be on the same network as the Database (this is a DSN restriction). If you want to do this through the web then you will have to use Web Services to accomplish this.

Paul

[Aditya_S_]

The link has information in Formcalc. Where would I find documentation in Javascrip ?

Aditya

[Aditya_S_]

Paul - I could connect to the db using an ODBC connection. However, I am having trouble trying to filter the information. The form has a field named ID and I would like to run a query against the id in the database.

The form doesn't seem to pick up the query command. Could you please suggest what am I missing. Below is the code used.

xfa.sourceSet.DataConnection.resolveNode("#command").query.commandType.value = "text";

xfa.sourceSet.DataConnection.resolveNode("#command").query.select.value = "SELECT * FROM acp where acpid = " + id.rawValue;

xfa.sourceSet.DataConnection.open();

[Former Community Member]

I am not sure but I think your SQL is incorrect.

You need it to be:

SELECT * FROM acp where acpid = 'string'

You do not have your value that you are supplying in quotes. To do that you will need an expression something like this:

"SELECT * FROM acp where acpid = '" + id.rawValue + "'";

It will be difficult to see on this screen but I added a single quote after the equal sign and wrapped a single quote (in double quotes) before the semicolon.

If that doesnot work .....hot Ctrl-J to bring up the java console and see if there are any errors. What kind of DB are you using?

Paul

[Aditya_S_]

I am using MySQL. I don't get any errors because it is not reading the command.

I entered "test123" instead of the querry at the end of the command and it still didn't throw any error. No matter what I use it doesn't read the command.

xfa.sourceSet.DataConnection.resolveNode("#command").query.commandType.value = "text";

xfa.sourceSet.DataConnection.resolveNode("#command").query.select.value = "test1234";

xfa.sourceSet.DataConnection.open();

Aditya

[Former Community Member]

Can you export your dbase and send me the file as well as your form and I will try it here.

Paul

[Former Community Member]

By the way ..are you using Reader or Acrobat?

Paul

[Aditya_S_]

I am using Acrobat 9.0. I have attached the db in csv format. If it doesn't work can you try it on a local db and let me know the odbc config settings.

Aditya

[Former Community Member]

The SQL file was dropped as it is not a valid file extension for the BBS ...cna you rename it to a txt file and re-attach?

Thanks

Paul

[Aditya_S_]

Here you go ...

[Former Community Member]

Ok I have it working .....there were a couple of small command changes, but the biggest issue is that the sourceSet is protected (cannot dynamically change it) since version 8. I changed your code to clone these nodes and used the cloned information to set the parms that you wanted.

If you back your target version to 7.0.5 then your code will work as is.

Anyways I have attached my modified sample. You will have to fix up the DSN as I have created my own. Also in the DSN connection make sure you choose the delayed open option so that the DB is only opened when you want in your code.....and don't forget tp close the connection when you are done.

Paul

[Aditya_S_]

I cannot access it. Can you reload it.

[Aditya_S_]

Paul - What do you mean by it works ? Do the values in the acpid and history field update  ?

I pointed the string to the textfield1 value so when I enter 99, for example it will give me the history for the value 99.

var char = "SELECT * FROM acp where acpid = " + TextField1.rawValue;

The app alert works fine and it gets the value correctly but the history and the acpid fields don't update.

Aditya

[Aditya_S_]

I got it to work. Thank you very much for helping !!

I just didn't set the cursor type to dynamic in the DSN settings.

Aditya

[Aditya_S_]

Paul - As per your post on August 4th "It has to be on the same network as the Database (this is a DSN restriction)."

All this while I was testing with a user DSN. However, when I create a File DSN on another machine (on the same network) and share the folder holding the DSN it works fine without the "Delayed Open" checked. When I check it I get no values for the parameters.

FYI - The DSN is on the LiveCycle server which is a different machine to the db but all the three machines are on the same network.

Am I missing anything ?

Aditya

[Former Community Member]

I have never used a file DSN. I assume that it is a file that holds the DSN information ...right? I always create a system DSN and have not had those issues.

Paul