MySQL and Drop Down Menu

If your form is going to interact with a remote DB, and it's going to need to do this with Reader then there is only one way to go and that's to use the "doc.submitForm()" function. If your form is operating under "special" circumstances, i.e., only from Acrobat Profressional, with a local database, or local network, then you have more options. But if its a general form to be used by anyone who has Reader, there is no other choice.

All "doc.submitForm()" does is move data in and out of form fields. That's it. when this function is called it takes all the form field data, packs it into the specified data format, and sends it to a server script using an HTTP Put. The server script then sends back data in one of the formats recognized by Acrobat. When Acrobat gets the response it tries to map the data in the return data into the form.

This simple transfer mechanism can be used for all kinds of stuff. For example, Setup a form with serveral hidden fields. Set the binding on these fields to Normal and the Binding on all the other fields to None. The submit ignores all fields that do not have a binding. These hidden fields are now the mechanism for transfering data in and out of the form. One of them could be a command, another for transferning data, and another as a status field.

You fill out the fields with the appropiate information and do a submit. The server script parses the information out of the data, builds an appropiate response, and sends it back. Code on the form waits for the return data by polling the status field. When the data comes back the script grabs the data out of the hidden fields and does something with it. For example, using it to popuplate your list fields.

Obviously this requires quite a bit of code on both the PDF and server sides. And it gets worse. If you want to have 2 modes of operations, one for form setup operations such as whats described above, and one for real data submission, then you'll have to also write code to dynamically switch the field binding.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/]http://www.adobe.com/devnet/acrobat/[/url]

Good information. I hope Adobe will consider adding some native functionality to communicate with MySQL.

I have a successful php script written for output now. We use an accounting software called StarBuilder that uses MySQL (as I'm told) for it's information database.

I'm designing a .pdf time card that can be used in the field and pulled up via the internet, but then outputs a .dif file that can be imported on an accounting machine via starbuilder. That side is taken care of and works thanks to PHP::Pear packages.

So the real issue remaining is getting that same MySQL database's values to pull into my drop down. It sounds to me like, basically, I'll be using PHP to grab the MySQL data, translate it to, perhaps, XML, and then create a data connection to the XML in the .PDF form?

I know this doesn't use doc.submitForm() but it sounds similar in functionality. The problem I'm having with that function is that I'm not sure where to use it. On a button in the form? In FormCalc, what's the usual syntax? I'm sorry if it seems like I haven't tried to grasp this function but I've run Google searches for a long time on it and I can't get any definitive information on it. It's as though it's assumed we all know how to use it in the documentation.

Anyway, if this function does allow for a more simple and easy method of MySQL data grabbing, I would love to know your thoughts. Thank you for your time, it's a very informative response thomp. Wishing you the best.

You've got the right idea. The best format for returning data to a LiveCyle (XFA) form is XDP. Manually export a data only XDP from your form first. Then use this as a template in the PHP for returning data to the form.

You'll find loads of info about the "submitForm" function in the Acrobat JavaScript Reference. It's a JavaScript function and should be used from JavaScript, not FormCalc. From a LiveCycle form, i.e., an XFA scripting context it's called with "event.target.submitForm()"

The "submitForm()" function can be called from a large number of contexts. Typically it's used on a submit button. But you can use it anywhere you need to force a data exchange, just as long as its not used where it could cause timing or other operational problems. The only problem issue is detecting return data and determining success or failure of the submit. This is a rather soft area. But for general purpose use there is no other way to transfer data in and out of a PDf.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/]http://www.adobe.com/devnet/acrobat/[/url]

Okay, I'm feeling like I'm pretty close here, however, everything I read on the JavaScript Reference regards this function as something that submits what's in the current form TO something else, be it a database, another form, whatever.

However, the issue is pulling data from a MySQL that is getting its information from a program called Starbuilder.

So you are suggesting I create an XDP file and use the code generated by this file to help template a php script that will actually feed the data in the actual PDF file?

I've actually only been using Livecycle since the fall and am entirely self taught in Livecycle, java, and php since then, so if there's something elementary that I'm not grasping, I do apologize. I do learn fast though, scout's honor.

Here's a quote from one of my earlier responses that explains the action of "submitForm()".

All the action is on the server side. The server script has to parse the incoming data from the "submitForm()" call, then put together an HTTP response with a body that Acrobat can understand. Usually this means data formatted in XDP, FDF, XFDF, or PDF.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/]http://www.adobe.com/devnet/acrobat/[/url]

Oh believe me, you don't need to quote yourself. I come back to this and study your words pretty carefully every time I have a go at this issue. There are just some things that fall into place as I go along with the implementation.

I just realized why none of this was working out for me.

"Obviously this requires quite a bit of code on both the PDF and server sides. And it gets worse. If you want to have 2 modes of operations, one for form setup operations such as whats described above, and one for real data submission, then you'll have to also write code to dynamically switch the field binding."

This form is entirely packed with fields and layout, some that feed each other, some that feed into a php script that later dumps into the .dif, etc. It's a finished form aside from this last step, so I'll email you the form and some ditch effort hopes. I do really appreciate this.

If you have tons of list fields that need to be populated from a remote DB you do have a problem. But there might be a ray of hope. There is a mechanism for mapping list entries to a data source. It works with a local DB, a WebService, and an XML schema. The local DB and WebService require that the form either runs in Acrobat Pro or that the form has special Reader Rights Enabling. But XML is something that can be transfered in the return data from a "submitForm()" call. Add an XSL to the form as the data source and then set the binding for the list entries on the drop down to a tag in the schema.

Whenever a data file is imported it will fill the list items. I've tried this with a DB and WebService, but never with XML data returned from a server script, but I think it should work. If this works it means that you don't have to use hidden fields for data transfer or swap binding on the fly.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/]http://www.adobe.com/devnet/acrobat/[/url]

I understand what you're saying up until "Add an .XSL file to the form as the data source".
My confusion here is probably something silly but, if I add an XML Schema, it prompts me for an .xsd file, which I have, and it asks me to transform the information using an .XSLT, which it seems to me would structure the data, but I see nowhere to import/attach/reference the actual .xml (or actual data).

If I add sample XML data, then I can browse to a simple .xml file that I have, but nothing actually appears in the drop down and I suspect that this is due to a structuring issue.

I can be more thorough but if I can get some guidance here I may not have to.

Sorry, to many TLAs. You are correct, and xsd file, or a sample XML

The idea here is to create a data source for the form. In the case of an XML source it's just a data structure, not an actual source. An XSLT is unnecessary. But by defining this structure, Acrobat then expects all imported data to be in this XML gramar. In the binding tab for the drop down you can relate the list entries to entries in the data structure.

Like I said before, I don't know if the list filling methodology will work with and XML data source, but it's worth a try.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/]http://www.adobe.com/devnet/acrobat/[/url]

I appreciate your patience in all of this Thom. I'm pretty sure that I have something else fouled up and conceptually I have the right idea, so I'm investigating my XML structure, since I'm weaker at this than either PHP or Livecycle.

I have a PHP script that takes MySQL values and converts them to an XML that holds its data like this code.


For testing, I'm using the Sample XML data connection option. It pulls into the form correctly and the binding looks like it's working on face value. I have the List item as ccname and list value as ccvalue, with the entire drop down feeding from ccinf.

When I test it, no values are in there. Are my expectations correct?

Ok, so I did an example to test out the methodology I suggested above. And IT WORKS!!

Here are the steps:
1. I used your data but placed it in a custom XML gramar. I also added in some extra entries to make the XML a bit more diverse. This is important, don't use the existing XDP format. Make up your own.

2. Then I created a form with a dropdown and a couple of form fields.

3. Added the custom XML as the data source.

4. Set the binding of all the form fields that are not connected to the XML to "None"

5. On the Binding tab for the drop down click on the "Specify Item Values" link. This text turns green and becomes an active link when the form has a DataSource.

6. This displays the "Dynamic Properties" dialog.

7. In the "Items" input select the parent tag of the Name/value pairs that will be the entries in the list

8. Next select the actual name and value tags.

9. Save the form and open it in Acrobat 8 or later.

10. From the forms menu item select "Forms -> Manage Form Data -> Import Data" and select the xml file.11. In my sample the items in the XML file populated the drop down list items.

This should also work for XML returned from a server script, although I haven't tested this.

Hope this helps,

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

By custom XML grammar, do you mean you altered the grammar to make it functional?

The only other thing I haven't done out of your instructions was the custom XDP format recommendation. Is this because of the <?templateDesigner StyleID aped3?> tags? I read that was an XML hitch.So I made a new blank document in LCD and did everything you did except that I instead just went to the XML source tab and removed all <?templateDesigner StyleID aped3?> entries.This caused new behavior. If I import the data, then click the arrow for the drop down, I get a Windows error noise but no error window to explain what happened. I feel like this is closer, but I'm sure your recommendation is for more than just this.

Is there a way to take this default XDP that I already have and rework it to a state that will work? Maybe particular tags that need to be searched and replaced? This form is so functional now that rewriting the base code might really turn ugly.

If it sounds like I'm in over my head now, it's safe to tell me to just go write this in PHP Thom, haha, but your time has been greatly appreciated.

You are going to have to write it in PHP eventually;) This is just getting the form ready to accept the data that way you want. Once the form can populate the list items from an imported XML, it aught to be able to do the same thing from XML returned by a PHP script.

Here's the XML file that I used

It worked perfectly. I think you are over thinking the problem.

I don't know all the details about XFA data binding, but my feeling is that if you use the XDP format, then Acrobat tries to fit the data into the Normal Binding model (which doesn't include filling in list entries) instead of mapping it through the XML dataSource.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

" I think you are over thinking the problem. "

You have no idea how true that is. You know what my problem was? My XML source values needed one more parent element like your element.After that, this works great. It appeared like that data binding model worked without the one more parent element from within LCD, but ..
Now I'm just mad at myself. If you were local, I'd go buy you a pizza and a beverage of your choice. My appreciation to you, Thom, is insurmountable. In fact, you can't even surmount it.

I'm sorry, it was working when I made that last post. I closed down LCD, reopened the file for another test, imported the data into Adobe Acrobat 9 to test again, and it didn't work.

I can't get this work again, and best case scenario I get an error "SOM expression returned list when single result was expected". This is where your custom XDP format is recommended.

Added:
Got up to get a cup of coffee, restarted LCD and everything was fine. Suppose that was it. Restart Livecycle, must have reset a small fluke.

Now it's a matter of causing an initialization import of the XML file.

Acrobat can be erratic sometimes. I've had similar issues, where a feature stopped working until Acrobat was restarted. Since there is no scripting for simply importing the data, it has to be a problem with Acrobat. The important thing of course is that it works in Reader after the form has been enabled.

The correct time to import the list data is after Acrobat/Reader has loaded the form. The Initialize event on the list element itself is a favorite for this sort of thing. However, the Initialize event can be fired for any number of reasons and I imagine that you only want the list to be initialized once.

There are other events that occur when the form is first loaded. For example "DocReady", and you can also store state information in the form that will allow a script to know that the form has already been initialized. I don't know which event/strategy would be best. You'll have to do some testing. Place "console.println()" statements into each event of interest and then watch the console as the form is loaded and used. This will give you a good feeling for how it all works.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]

Am I right when I assume this is where you'd suggest using SubmitForm()?

Whenever I use this method, it causes not only a submission, but a load of the script that I send it to. This is important because they still need the form to be up and running after the submission has been made to the script.

Is there a way to cause a submitform that acts "behind" the pdf, then retrieves without leaving the PDF as well? I really studied the Java API Reference but could not dig anything up in regard to that.

I continue to badger you because everywhere else I go, the common answer is "Just write the PHP and have it generate the entire pdf with the variables."

Which I've tried to do and for some reason it's not working.

Yes, you can do a "submitForm()" behind the scenes that does not visibly affect the view of the current document. It's all in the data returned in the HTTP response. It should only contain your custom XML in the body. And of course the header fields need to be set appropiately. The most important is the MIME type, and make sure the requesting URL has #FDF on it so that Acrobat expects return data. It doesn't matter that it's not actually FDF data.

I'll repeat again that I haven't tested out the specific methodology you are using. So I don't really know for sure how well it will work, or the exact details. However, there's nothing in the documentation that indicates it can't be done. In fact, it specifically states that custom XML can be returned to Acrobat from a submit. You just have to work out the details.

Thom Parker
The source for PDF Scripting Info
[url=http://www.pdfScripting.com]pdfscripting.com[/url]

The Acrobat JavaScript Reference, Use it Early and Often
[url=http://www.adobe.com/devnet/acrobat/javascript.php]http://www.adobe.com/devnet/acrobat/javascript.php[/url]