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 — explains how to design a form which lets the user iterate through each record one at a time; and
- Selecting Specific Database Records — 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, I’ve decided to post a little tutorial on displaying all records from an ODBC data connection for reporting purposes (as opposed to editing).
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. 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).
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.
Modifying the Data List Box script
In 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 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.
A note on bindings
Notice 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).
Making the script look for the right data
The 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:
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);
}
}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).
Generating MovieSF instances for each record
This 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:
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();
}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 (in short, because I don’t want to merge the new instance of the MovieSF subform with data).
The goods
If you’re curious to see how my sample form works, here it is:
Minimum Requirement: Designer 7.0, Acrobat Standard 7.0.
Don’t forget to create a System DSN named “FormBuilderDB” using the included SQL file.
Updated: October 17, 2006
Posted by Stefan Cameron on October 12th, 2006
Filed under Data Binding,Instance Manager,Scripting,Tutorials
Both comments and pings are currently closed.
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
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.
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.8
Help required Urgent.
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.
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.
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.
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.
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:
to this:
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.
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]:initialize
Connection for Source PropertyData failed because the environment is not trusted.”
Again, Thanks in advance.
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…
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).
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?
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
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 SubForm
or
var oRow = tblData._Row.addInstance(0);
I was trying combinations of:
var oRow = _tblData.addInstance(0);
var oRow = _Row.addInstance(0);
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
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.
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.
Hello Stefan,
I hope this is an OK place to post these questions. With some trouble I have been able to get this to work with a SQL statement against an MS Access database – retrieving a case list by case worker username. What I’d really like to do on this – and other forms like it – is use an Environment Variable (NWUSERNAME) which matches the username in the database as the parameter (I don’t want case workers to look at other people’s lists).
Access will allow you to do this – Environ$(‘NWUSERNAME’) – but when I copy the SQL statement from Access to Designer it doesn’t seem to fly through ODBC. I’ve seen this is a security issue (with good reason…) elsewhere with AcroForms and assume that is the problem here. I’ve also seen some talk about trusted functions and think that’s maybe how I get around it (these would be intranet-only forms) with a folder level .js, but have no idea how I would do that.
Also, how would I merge/freeze data retrieved this way on preSave (just break/delete the Data Connection?)?. I opened the form on a test box without no DSN and the data did actually show (!?) – after a message about the connection missing. Thanks so much for your time…
Chris
P.S. all users have Acrobat Standard 7.x. I’m using Designer 7.1
Chris,
I’m not surprised that you aren’t able to use environment variables in data connection SQL statements. It would make sense that we prevent you from using them for security purposes.
As far as “trusted functions” are concerned, there’s a way to write “trusted” JavaScript for use within Acrobat. Of course, this would imply that you get a copy of the JS file onto every user’s machine because the file must reside in a special folder which I think is the following:
I’m not certain how to make use of scripts in this folder but it might be what you’re looking for.
Hey Stefan (or anybody else),
Chris again. What if I were to use the ODBC login name? Do you know how I would call that in the SQL statement? Or maybe there’s another approach? Oh and my second question about merging/freezing on preSave… Again thanks so much. I am trying to find the answers on my own but no luck so far…
Chris
Chris,
I doubt you would be able to get at the username used for the DSN via the SQL statement. In essence, you’re trying to ensure that a particular case worker doesn’t look at another case worker’s list of items from the database by locking them down to viewing their list. Short of using the “trusted” JavaScript file I suggested in my previous comment, you could hard-code the username into the form and make specialized forms for each case worker (multiple copies of the same form yet each copy would have a different username set as a Form Variable) or you could add a password to the records in the database and request the username and password within the form itself such that a case worker would be asked to enter their username and password prior to the form retrieving records from the database. Form Fragments would work great for my first suggestion since the contents of the form could be made-up of fragments to avoid having to create actual copies of the form for each username.
As for your question about merging/freezing, I would suggest you include a hidden checkbox which you would set to “checked” once the data has been retrieved from the database. Assuming the case worker saves the form and re-opens it, your script could then check the value of the checkbox prior to opening the data connection to load the data. If the checkbox’s value is “checked” (1 by default), then simply do nothing as the data that was saved with the form will be reloaded into the form.
Hi Stefan,
I’ve read about connecting to a database and displaying all the records. I have also been able to create an ODBC connection where I can display all the records from an Excel file by using your scripts and would like to thank you for all the supports.
I would like to know if it is possible to do the following:
1. Create a browse button in a form where the user can search for a CSV or excel or xml file
2. Display all records from the file in an adobe pdf forms (Where the fields are editable)
The database connection i am planning to use is an XML schema which I can embed within the form.
Thanking you in advance.
Best regards,
parwez
Hi Stefan,
Thanks for the great info on these forums.
This “Environment is not trusted” issue keeps coming up when trying to connect Livecycle to databases, in my case Access.
Has Adobe recognised this as an issue and is there a solution/workaround ?
Thanks for any update !
Parwez,
You’re very welcome!
You could use the xfa.host.importData() statement on the Click event of a button to open a dialog from which the user could choose a data file to import however I’m not sure if that supports the CSV format and this functionality won’t work in the free Adobe Reader unless you use LiveCycle Reader Extensions to extend your PDF to allow Reader to import data.
This will get data from the user’s desktop into your form. From there, you can use data bindings and scripts to display all of the records in the data file.
Xancholy,
It’s likely seen as a security issue when connecting to a third-party application and so it won’t be going away anytime soon. I don’t know of a workaround.
Actually Debbie Freshwater’s solution in post#8 here:
http://forms.stefcameron.com/2008/07/03/acrobat-pro-9-with-designer-82-now-available/
worked fine to connect an access database without the “Environment not trusted” irritation.
Yay Debbie. Thanks
Hi Stefan, can you please help, i have your form working, but what I need to do is create 4 fixed instances on the page (the first 4 entries in my excel file), followed by any other instances that the excel file contains. I tried to manually bind 4 rows and using a new connection to the excel file, but then one connection is in USE so it will not use the other connection and I get an error. Please see my image here: http://docs.google.com/Doc?id=dcj7tdvs_6hpdrkqds&hl=en_GB I want to avoid having 4 blank lines, Can you help?
Quick question:
How do I add DUPLICATE items to my access db file from LiveCycle. I have the classic “purchase” example set up on my machine. I have connected to the database and can add to and update the database, but I cannot seem to update two records with the same call sign. Then, of course, I would need to know how to script it so when searching for said call sign, the latest o most recent one populates. Any thoughts on this would be greatly appreciated?
Thanks!!
that “o” is really supposed to be an “or”
Very quick question: Using Acrobat 9 & LiveCycle I’m only retrieving the first row from an ODBC source (MS Access 2007). How to retrieve multiple records ? XML data imports of multiple records work just fine, not so for OLEDB/ODBC from Access. Thanks in advance for some fresh advice (notwithstanding all the related posts along these lines over the past 2-plus years on your great blog).
Ebrahim,
I’m not sure I understand your question. Are you wanting the first 4 rows to be on a page of their own?
Hi Stefan,
No Stefan thats not what I want to do:
My pdf form must have 4 rows no matter what, if it does not have 4 rows then the form will not look right. These 4 rows need to remain on the page no matter what. They need to retrieve data from my excel table (which can have 0-infinite rows) I need my 4 rows in my pdf to pick up the first 4 excel rows, and then I any additional rows in my excel document to be output to the pdf document. If there is 0-3 entries in my excel table, then I still want to output 4 rows, even if some of them are empty. I hope this makes sense!
I have been stuck on this for what seems forever now, so I really hope you an help.
Brad,
Perhaps you could use my sample on inserting new records into a database to add the duplicate and then use my sample on selecting specific records in a database to set a query on the data connection that sorts records by creation date (assuming you capture the date when you create records in the first place).
Lee,
The difference between XML data and ODBC data connections is that with ODBC, only a single record is loaded at a time in the bound fields. That’s why I show, in this post’s sample, how to iterate through all available records in the connection in order to display them all.
Hello Stefan,
I found out my original problem, now for the next. I am no expert on coding so be easy on me.
The initialise takes place on the main form, and will populate a subform, now what happens if I want two subforms, and both have different data and each grows/shrinks independantly. How can I tell your script to do this. I tried the following:
I copied your code and used the line
var oNewPerson = _Subform1.addInstance(0);
in the first set of code
then i repeated the same code but this time used
var oNewPerson = _Subform2.addInstance(0);
Only the first list is working.
I also put the code into each subforms initialise code, but that did not show any results.
Ebrahim,
You would have to first add a new subform into an existing flowed subform and name it “Subform2”. Then you would set its “Object palette > Binding tab > Repeat Subform for Each Data Item property” and set its related “min property” to zero. Then your “_Subform2.addInstance(0);” code should work.
Hello Stefan,
I have tried maybe now over 20 hours to get this working with various different setting and it just does not work for me.
– I can add a new subform quite easily to the existing flowed subform (i assume you mean “form1” which is the base form).
– I then create a new data connection with a different mysql query (because i want different data in the new sub form).
– Next I copy your code and paste it into a new subform called subform2. I also change the connection property from “Dataconnection” to “DataConnection2” to match my new data connection name.
– After this I change the add instance variable to match the name of my new subform and also update all the other variables and field names. I really double checked to see I am not missing anything.
– When I preview the document, the file does not load and seems to constantly load in an “everlasting loop”. I have no idea why this is happening. If i save the livecycle file and open it in adobe reader, nothing happens. I can see the file in my task manager process list. But the file is not actually opened.
please can you update your example so that it covers 2 or more subforms. I have wasted so much hours over this and I simply am getting no closer.
Dear Stefan,
Got your form to work (thank you).
Have two questions.
1. Am trying to add entries to the subform (text box, drop down list, and a pair of radio buttons. I can only get things to work with the four entries of yours. Every attempt to add my own entries or rename yours, fails. How does one add drop down lists and radio buttons?
2. Am trying to move this subform from form1 to the actual subform since I will be having another subform with similar lists but different information required to be entered. Will need to do data filtering prior to the display of the subform. while(xfa.sourceSet.nodes.item(nIndex).name != sDataConnectionName) fails to work at the subform level.
Any help would be appreciated.
Patrick
Had a duh moment and know can add fields. Am using drop down boxes, for the moment, so question one is answered.
Still looking for advice on question 2.
Patrick Rodgers,
For #2, I’m not sure what you mean by “fails to work at the subform level”. If you’re having trouble finding the data connection, you can access it directly by specifying the data connection’s name as a property on the xfa.sourceSet object.
For example, if your data connection was called “myDataConnection”, you could access it like this:
Hello Stefan,
I read through your blog and I am little lost on how to make this work. I have been using LiveCycle Designer 8.2 for about a year now and am looking to improve on the Data collection area.
I did make a Data connection to my form, that works good, but with this form I need for the XML data to be imported in to Excel to send to our client. I have that figured out also.
If I do not do a data connection I import my data into excel and it shows everything from the PDF form, if I do it so the form HAS a data connection it will populate the Excel sheet with out having to clean up the spreadsheet and make it the way our client wants it.
My problem is with a data connection in the PDF Form it will only pull information from the first row and not the rows that you can add with the add rows button. If you could please help me out it make my life much easier when it come’s to collecting the data.
Thank You
@Todd,
The first question to ask is whether you’re using Acrobat or Reader when you add the data connection to the form. Seeing only the first record is often a sign that you’re using Reader instead of Acrobat and therefore you can’t use data connections unless you extend the PDF to allow data import in Reader.
Otherwise, you need a script that will loop through all records available via the connection and add instances of table rows with pertaining data. I have a sample of that in this tutorial).