Displaying All Records from an ODBC Data Connection

So far, I’ve covered two ways of connecting forms to ODBC data sources:

# “Connecting a Form to a Database”:http://blogs.adobe.com/formbuilder/2006/09/connecting_a_form_to_a_database.html — explains how to design a form which lets the user iterate through each record one at a time; and
# “Selecting Specific Database Records”:http://blogs.adobe.com/formbuilder/2006/09/selecting_specific_database_records.html — takes the first tutorial one step further by providing a means to specify which record(s) to view.

This time, in response to “Y. Gautham’s recent comments”:http://blogs.adobe.com/formbuilder/2006/08/importing_data_in_acrobat.html#comment-39853, I’ve decided to post a little tutorial on *displaying all records* from an ODBC data connection for reporting purposes (as opposed to editing).

h2. XML Schema vs ODBC Data Connections — What’s the difference?Those of you who have been using XML Schema data connections might think this is just as easy to do but it actually involves some scripting and some knowledge of the “Instance Manager”:http://blogs.adobe.com/formbuilder/2006/05/instantiating_subforms_at_runt.html. This is due to the differences in the way data is loaded between the two data connection types: XML data loaded via an XML Schema data connection is loaded completely. This allows you to use bindings like “$record.XMLSchemaDataConnection[ == * == ]” to bind all instances of a repeating data node to a subform and therefore cause a new instance of that subform to be generated for each instance of the repeating data. On the contrary, ODBC data connections are normally used to iterate through records and therefore only one record is loaded at any given time. Because of that, you can’t binding a repeating subform to a binding like “$record.ODBCDataConnection[ == * == ]” in order to get one instance per record because only one record will ever be loaded at any given time. (That’s not to say that the binding doesn’t work — it does, in fact, but you never get more than one record and therefore you never get more than one instance of the subform).h2. Where to start?At this point, I’m assuming you’ve already created an ODBC data connection using the Data View palette — similar to what you may have done when following my previous ODBC data connection tutorials.Since my goal is to display all records in an ODBC data connection, it would be nice to “hit the ground running” with some pre-canned script. The Data List Box (found in the Library palette’s Custom tab) object’s Initialize script will give me a good start because it’s already designed to iterate through records in a data connection and populate a list box with record data.h2. Modifying the Data List Box scriptIn order to list all data from all records from an ODBC data connection, I’ll need to use a container into which I can add items. One way to do this is to use a repeating subform inside a flowed container. This will let me use the repeating subform’s “Instance Manager”:http://blogs.adobe.com/formbuilder/2006/05/instantiating_subforms_at_runt.html in order to add a subform for each record I find.For my form (based on the Movie Database from my previous database tutorials), I decided to use the page subform as my record data container (and named it “MovieSF”) since it’s already in a flowed subform (that is, the root subform, named “form1″ by default). In the MovieSF subform, I then placed _title_, _showTime_, _category_ and _actor_ fields and specified *”None” as the binding* for *each field and the MovieSF subform*. Then, I specified that it should repeat for each data item using the Binding tab in the Object palette and specified no min, max or initial count. Finally, I placed the Initialize script from the Data List Box into form1’s Initialize event.h3. A note on bindingsNotice that I specified a binding of “None” (which means I explicitly specified that objects should not be bound to data nodes in any data connection) for the MovieSF, title, showTime, category and actor objects using the Binding tab in the Object palette. I did this to avoid binding problems later where you might end-up with an instance of the MovieSF subform for each record but no data in the fields and this is related to implicit bindings attempting to execute on each instance (or explicit binding if you explicitly specified which data node an object should be bound to).h3. Making the script look for the right dataThe first step in modifying form1’s Initialize script (based on the Data List Box’s Initialize script) is to make sure it’s looking for the right data nodes. This is a little difficult to explain without using a diff tool so you’ll have to check-out my sample form (linked later in this post) to see it in detail. I will, however, highlight the main point of modification for this step which is labeled “Find the data nodes” in the script:bc.. var oTitleDataNode = null;var oShowTimeDataNode = null;var oCategoryDataNode = null;var oActorDataNode = null;for (var nColIndex = 0; nColIndex < oRecord.nodes.length; nColIndex++){if (oRecord.nodes.item(nColIndex).name == "title"){oTitleDataNode = oRecord.nodes.item(nColIndex);}if (oRecord.nodes.item(nColIndex).name == "showTime"){oShowTimeDataNode = oRecord.nodes.item(nColIndex);}if (oRecord.nodes.item(nColIndex).name == "category"){oCategoryDataNode = oRecord.nodes.item(nColIndex);}if (oRecord.nodes.item(nColIndex).name == "actor"){oActorDataNode = oRecord.nodes.item(nColIndex);}}p. Notice how I've added a couple of objects and changed the contents of the For loop to look for each type of data node I need (and which is mapped using the MovieDataConn data connection).h3. Generating MovieSF instances for each recordThis is the brains of the script which generates a new MovieSF subform instance for each record found in the MovieDataConn data connection. This is done using the "_MovieSF" Instance Manager:bc.. while(!oDB.isEOF()){// Create a new instance and get a reference to it.var oNewMovie = _MovieSF.addInstance(0);// Populate the fields inside the new instance.oNewMovie.title.rawValue = oTitleDataNode.value;oNewMovie.showTime.rawValue = oShowTimeDataNode.value;oNewMovie.category.rawValue = oCategoryDataNode.value;oNewMovie.actor.rawValue = oActorDataNode.value;// Move to the next record in the data connection,// thereby causing all data node references to be// updated accordingly.oDB.next();}p. You should take note of how I save a reference to the new MovieSF subform instance into a local variable which I can then use to populate its fields using the appropriate data nodes. Also, if you're wondering why I'm specifying zero as the parameter for the _addInstance_ function, "here's why":http://blogs.adobe.com/formbuilder/2006/07/demystifying_im_addinstance.html (in short, because I don't want to merge the new instance of the MovieSF subform with data).h2. The goodsIf you're curious to see how my sample form works, here it is:"Download Sample [zip]":http://blogs.adobe.com/formbuilder/samples/DataBinding/DisplayAllDBRecords.zip*Minimum Requirement*: Designer 7.0, "Acrobat Standard 7.0":http://blogs.adobe.com/formbuilder/2006/08/importing_data_in_acrobat.html.Don't forget to create a System DSN named "FormBuilderDB" using the included SQL file.====~*Updated:* October 17, 2006~

17 Responses to Displaying All Records from an ODBC Data Connection

  1. Y.Gautham says:

    Hi Stefan,First of all my heartful thanks for the solution provided.I could get the instances created but the values are not being displayed. I find the blank instances.I am using Adobe Livecycle Designer 7.1 for designing the form. I have Adobe Reader 7.0.0 installed in my system. Is it a problem? Should I have a version which is higher than that?- Y.Gautham

  2. Y. Gautham,If I understand correctly, you’re getting the correct number of instances of the repeating subform (like “MovieSF” in my sample) yet all the instances contain fields with no data?This is most likely a data binding issue. Using the Binding tab in the Object palette, make sure that the repeating subform and all its fields have their Default Binding property set to None.

  3. Y.Gautham says:

    Hi Stefan,Fortune is not my way.I am unsuccessful in displaying the values. I have ensured that all the steps have been done properly.I am able to display instances but the values are not being displayed in the fields.Can you tell me is there any way to debug (like setting a break point etc) the form.Also I am facing a problem with your previous exercise where you have connected to a Database and placed some buttons for operations like first, next, delete etc.I am not receiving any error messages but the fields are not being updated with the values.Kindly provide me the version of the Adobe Livecycle Designer and the Adobe Reader you are working on.I am working on Adobe Livecycle Designer 7.1 and Adobe Reader 7.0.8Help required Urgent.

  4. Y. Gautham,I’m currently using (and have used to develop the data connection samples I’ve posted) Designer 7.1 and Acrobat Pro 7.0.8.Now that I’m writing this, I’m realizing that I should’ve specified that Acrobat Standard was a minimum requirement for these samples as Adobe Reader and Elements are unable, by default, to import data (which may explain the problems you’re having). I’ll update my “minimum requirements” for each post immediately.Please try these samples with Acrobat Pro (I’m assuming you have it since that’s how one usually acquires Designer) and let me know how you make-out.As far as debugging tools, I’m afraid there isn’t a system for setting break points, etc. The best you can do is use either “xfa.host.messageBox(‘message’)” — FormCalc or JavaScript — to display values in message boxes or, my personal favourite, use “console.println” — JavaScript only, though — to output strings to the Acrobat JavaScript Console.

  5. Varun Dixit says:

    Stefan,This has lot of insight. But I downloaded and tried looking at Sample, I could not find form1 where you are putting the initialize script for Data Drop Down List. The sample has just one subform which is MovieSF I can not find any FormCalc or JavaScript. Any pointers would be helpful, since this is important for me to get it right. Thanks in advance.

  6. Varun,”form1″ is the root subform, that is, the subform under which all other objects on the form are included.You won’t find it simply by looking at the canvas. Instead, you’ll need to use the Hierarchy palette to locate it. Choose the “Hierarchy” command under the top-level “Window” menu and then look at the top of the hierarchy tree that appears. You should find the “form1” root subform at the very top of the tree.Select it by clicking on it in the hierarchy tree and then look at its Initialize event using the Script Editor. You should find the script in there.

  7. Varun Dixit says:

    I am sorry for being a pain, but I got my instances working. Still the fields are empty and I see no data in them. There is one thing to watch out here, before I go into preview it says that connection to data source failed because environment is no trusted. I don’t know if they are related but still worth mentioning. Please let me know if there is any other thing I need to watch for. I am using Adobe LiveCycle 8.0.1291 trial version. Just an FYI. Thanks in advance.

  8. Varun,It sounds like you’re running into issues with changes to the scripting environment in Acrobat 8.0 with XFA 2.5 forms.Forms designed with Designer 8.0 are XFA 2.5 forms and when they’re run in Acrobat 8.0, the SourceSet model (“xfa.sourceSet”) is locked down for security reasons. (I won’t go into details here because I’m in the middle of preparing an extensive post on this topic and I’ll just repeat myself.)For now, I’ll just leave it at this: The sample from which you’re likely taking the script was designed for XFA 2.4 which doesn’t have this security problem. In your XFA 2.5 form, you’ll simply need to modify a copy of the data connection node you get from the SourceSet model and use it to load the data instead of using it straight from the SourceSet model.You should be able to get around this by changing this line:

    var oDB = xfa.sourceSet.nodes.item(nIndex);

    to this:

    var oDB = xfa.sourceSet.nodes.item(nIndex).clone(1);

    If you didn’t modify the script, then you shouldn’t need to modify anything else since it’ll keep working with the “oDB” variable as it did before but without the security exception.

  9. Varun Dixit says:

    Thanks for your reply. But that modification does not seems to help either. It still won’t display the data in the fields. Again, its returning the right number of instances from my query which to me means that it’s getting to the query but some how not grabbing the data. It still says Environment not trusted and when I went to look at Script debugger its giving me this,”GeneralError: Operation failed.XFAObject.open:11:XFA:form1[0]:initializeConnection for Source PropertyData failed because the environment is not trusted.”Again, Thanks in advance.

  10. Varun Dixit says:

    Stefan,I found the solution and it’s working now. I found the solutioin to it on Adobe Forums only. The solution said that the binding on the field should be set to “Normal” and not “None”. It works perfect for me.Thanks for all your help, But I am still confused as to why it would work set to “None” for you and not for me…

  11. Varun,I appears that you may have run into a bug in Acrobat 8.0 (that we know about and have fixed for the next release) where you’re unable to connect to a local DSN as a result of the connection not being trusted by Acrobat. The result is the error message you describe in your second last post (“…because the environment is not trusted”).Unfortunately, I don’t know of a work-around although you seem to have found one by setting the bindings on the subform instances to “normal” instead of “none” (and I can’t really explain why that seems to be fixing the issue).

  12. Ulric says:

    Greetings,I have a 3 page form to import data from an Access table. I am able to pull all the records, but it gives me all records for each page instead of all pages for each record. Is there a way to ‘collate’ the forms?

  13. Willie says:

    I too had a fun time getting this example to work, but I finally did. I next wanted to improve on it by displaying my repeated data in a Table, not just in a SubForm. I read the article on the Instance Manager which states that a table row is essentially a subform and that the instance manager works the same way in it. My attempts have proven unsuccessful. Can anyone provide a working example for displaying repeated data (from an ODBC connection) into a Table. Thanks, Willie

  14. Willie says:

    I determined how to display multiple rows of ODBC data in a table. I had the wrong syntax in my initialize event.Instead of using:var oRow = Wrapper.tblData._Row.addInstance(0); //Wrapper is the name of my SubFormorvar oRow = tblData._Row.addInstance(0);I was trying combinations of:var oRow = _tblData.addInstance(0);var oRow = _Row.addInstance(0);

  15. Willie says:

    Ok, using the examples found in this link, I can now load and display data from an ODBC data source into a Table object. I have combined this with the LiveCycle Designer 8.0 tutorials for an Order form. Basically, a database provides values for Part Numbers, Description and Unit Price. I have a user entered field for Quantity contained in the same Table object. I have other user entered fields outside of the Table object: Name, Address etc. I have also included the Submit by Email button. However, when testing this PDF, the XML generated (from clicking Submit) does not include any of the ODBC data, nor the user entered Quantity field. I have tried changing the binding of the Quantity field from Normal to None with no apparent difference. Is there any way that I can get the Submit button to generate XML for all values on the PDF, both ODBC and user entered? Thanks, Willie

  16. Ulric,Your question is unclear to me: Are you essentially saying that all records are repeating entirely for each page in your form? I don’t understand what you’re meaning by “collating” the forms — I thought you had 1 form with 3 pages, not 3 forms each with 1 page.

  17. Willie,I’m glad to see you figured-out how to display the records in a table.Based on your last comment, I’m guessing that the data connection provides a list of unique parts which you’re displaying in the table, each on its own row. In order to do this, you’ve basically defined a data connection to your database and you’ve modified the script I used in this post’s sample in order to display all the records from a database.If that’s the case, you should’ve ended-up with a table with a single row that contains 4 columns, each being a field of its own: part number, description, unit price and quantity. The binding type (as specified on the Object palette’s Binding tab) for each of the fields should be Normal and none of the fields should have a name equal to the name of a data node in the data connection you’ve defined (or else you’ll run into binding problems).If the data from some fields aren’t getting into the submitted XML data file, then it’s likely a problem with the bindings for those fields. They should all have a binding of “Normal”. You might also want to check that the table row subform’s binding is set to “Normal”. If it was set to “None” (which could’ve automatically happened if you added the table to the form after defining the data connection), this would also affect how the data gets submitted.Please let me know if this helps you at all.