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~