Databases: Inserting, Updating and Deleting Records

Since all of the previous tutorials I’ve posted with regards to data connections have dealt with searching for records and displaying the results, I thought I should post a little tutorial on how to use data connections for inserting, updating and deleting records as well.

You may recall that the tutorial on connecting a form to a database did demonstrate one method of inserting, updating and deleting records from a database. The problem with using bindings to fields in order to modify records in a data connection is that you must set those fields to the values for the current record and then you have to use the data connection object scripting methods like "addNew()", "update()" or "delete()". This can get really awkward if all you’re wanting to do is insert a new record, for instance, and just doesn’t cut it if you want to avoid having to load-in data when the form is opened (the only purpose for the data connection may be such that new records can be inserted or existing ones can be deleted yet displaying existing records is not required).

This small tutorial uses a basic ODBC data connection defined in the form and then modifies it, via script, in order to be able to execute SQL statements which either insert, update or delete records from any table in the database for which the data connection was setup to work with. It then uses a separate data connection to the same database for query purposes only since there’s no sense complicating things with an all-purpose data connection — especially when it comes to running "select" SQL statements in order to iterate through records returned by the query.

Key Concept

The key concept with this tutorial is the fact that the <query> node inside an ODBC data connection (as we saw in the tutorial on selecting specific database records) can be used to execute all sorts of SQL statements — not just "select" queries. This means that if you set the <select> node (inside the <query> node) to be an "insert" SQL statement and open the data connection, the result will be a new record in the database as per the insert statement’s parameters (and the same goes for "update" and "delete" SQL statements). In fact, the XFA 2.4 Specification states, on page 772, that "despite the name [of the <query> node], this element can also be used to delete, insert, and update records."

BOF and EOF Actions

One very important thing to note is that the "Beginning of File" and "End of File" actions on the <query> node’s <recordSet> child node (which describes how records in the data connection are navigated) must be set to "stayBOF" and "stayEOF", respectively, otherwise you may run into serious problems. That is, the result of reaching the beginning or end of the record set when opening the data connection must be to "stay" where you (the record set navigator) are when the SQL statement is one or more of "insert", "update" and "delete".

See the script in the "Database" script object in the sample form for more details on how to specify this. Use the Hierarchy palette to locate it under the root subform.

Sample Form

I’ve designed a form that should put this all into perspective for you as well as give you a very useful script object which you can place in your Custom Library tab and re-use in other forms.

In short, these are the steps I followed to design this form:

  1. I created the "RunSQLDataConnection" ODBC data connection to my "FormBuilder" database, specifying a short SQL query to the new "movie_comments" table (although any other table would’ve been just fine).
  2. I created the second "ListComments" ODBC data connection to my "FormBuilder" database, specifying an SQL query that exposes the "username", "title" (movie title obtained from a join on the movie table) and "comment" columns.
  3. I inserted the various buttons and fields and wrote the scripts.

I’ve included lots of comments through the scripts to detail what’s going on at each stage as well as why certain things are being done so please have look at the sample (note that you don’t have to have the data connections setup in order to open the form and look at the script) and let me know if you have any questions.

Download Sample [pdf]

Download FormBuilder Database Definition [sql]

Minimum Requirements: Designer 8.0, Acrobat Pro/Std 8.0

21 Responses to Databases: Inserting, Updating and Deleting Records

  1. Willie says:

    I am trying this example using an Oracle 10.2g database, and when the ExecSql script gets to the oDC.open(); statement I get a “GeneralError” exception. Any ideas on what I am doing wrong?Thanks, Willie

  2. Willie says:

    Ok, I now have access to a SQL database to test with in addition to my Oracle database. Keeping the same code, but altering the data connection to point to my sql database, it now works. Both data connections were created using Microsoft OLE DB provider for ODBC drivers and a system DSN. I “tested” both connections via the data link properties test connection button, and both passed. Any thoughts?Thanks, Willie

  3. Willie,That certainly is strange behaviour. Unfortunately, I’ve never encountered this before. I have heard of problems when not using the Microsoft OLE DB provider but that’s the one you say you’re using so I’m at a loss for suggestions.Are you trying this with Acrobat 8.0 (Pro or Standard) or an older version?

  4. Willie says:

    Stefan,I am using Adobe Acrobat Professional 8 (trial download version).Willie

  5. Willie,I would encourage you to have a look at Oracle’s ODBC FAQ to see if there might be an answer there.If not, then you could post a message to the Acrobat Forums or report the bug.

  6. jim says:

    Ive setup everthing up, but seem to not working properly. it keeps providing this error prompt.”script failed(language is formcalc;coontext is xfa[0].form1[0].button[0])script=xfa.sourceSet.dataconnection.update(), Error:accessor’xfa.sourceset.dataconnections.update()'”using SQL Server connection

  7. Jim,The first thing to check for in this case is if there are any syntax errors in your script. Based on what you wrote,

    Error:accessor’xfa.sourceset.dataconnections.update()’

    I see a syntax error in the “sourceset” object’s name. It should be “sourceSet”, with a capital “S” in “Set”.The problem could also be the “s” in the “dataconnections” object name. Are you certain its plural?

  8. Khader Meeran says:

    We are developing an application wherein we design forms using Adobe Acrobat LiveCycle and, then extract and import data to and fro from the application. We are using C#.Net and Oracle as backend. I would like to know a way to export, import data as well as manipulating controls such as show/hide controls, locking/unlocking controls from C#.NET. Can anyone sugget me with an example or material for this purpose.

  9. Carsten Sallaba says:

    Hi Guys,Situation:*-Database connection*-prepopulated filedI want to build a script where a user can select e.g.Actor and a subform should dynamically prepopulate the related movies in textfield. The user should be able to add or delete movies.Me code is based on the following examples:DisplayAllDBRecords.pdfandSelectSpecificDBRecords.pdftherefore I tried to combine them, without any success 🙁 It seems that the filter doesn’t work, and I don’t know how to call customized function in formcalcProblem:Does anybody have an example for this typical example?Hope anybody can help meThanks in advanceCarsten

  10. Khader,Are you asking how to import data into and export data from a LiveCycle form using Oracle as the back-end database, and then control the appearance and state of LiveCycle form fields using C#.NET?

  11. Carsten,I’m confused: Based on the description you gave of your problem, it seems that my tutorial on selecting specific database records is exactly what you’re wanting to do.There must be something more specific that that tutorial isn’t covering which is part of your list of requirements. Let me know and we’ll take it from there.

  12. Doug Einstadter says:

    I also tried to combine the scripts in the ‘how to connect to a database’ example with the drop box and select button in the ‘selecting a specific database record’ example and ran into problems. By selecting a specific record, the SQL query results in a record set with only one record. This means that hitting the ‘next’ button results in no movement – there is no ‘next’ record to display. Same with ‘previous’, ‘first’, and ‘last’. What I’d really like to do is have a ‘go to a specific record in the database’ script that keeps all the records loaded, but jumps to the one record selected. I’ve tried looping through using next() until I get to the selected record, but with a database of 300 records and some 500 fields linked to my form, this can take a long, long time. Any suggestions on how I can accomplish this more efficiently?

  13. Doug,I’m thinking the sample form in my tutorial on selecting specific database records is almost exactly what you need, given the large number of records in your database.I would suggest using that sample and coming-up with a more extensive way to specify information about the record(s) sought so that the query can return one specific record or a small subset of them.If the query only returns a single record, you could always disable the “next”, “previous”, etc. (i.e. the buttons that don’t apply).To determine if the query returned zero, one or more records, you can use the “isEOF” function of the Data Connection object which returns true if you’ve reached the end of the recordset.The following FormCalc script is a modification to the original script that’s in my “selecting specific database records” sample, in the drop down list’s Change event:

    var sOriginalBOF = oDataConn.#command.query.recordSet.bofActionvar sOriginalEOF = oDataConn.#command.query.recordSet.eofActionoDataConn.#command.query.recordSet.bofAction = “stayBOF”oDataConn.#command.query.recordSet.eofAction = “stayEOF”var nRecordCount = 0oDataConn.open()if (oDataConn.isEOF()) then    xfa.host.messageBox(“no records returned”)else    oDataConn.first()    nRecordCount = 1    oDataConn.next() // move past the first record to see if we reach EOF    if (oDataConn.isEOF()) then        xfa.host.messageBox(“one record only”)        oDataConn.previous() // move back to the one and only record    else        nRecordCount = 2    endifendifoDataConn.close()oDataConn.#command.query.recordSet.bofAction = sOriginalBOFoDataConn.#command.query.recordSet.eofAction = sOriginalEOFoDataConn.open()if (nRecordCount > 0) then    oDataConn.first()    // Set the category name in the movieData subform and make it visible.    movieData.presence = “visible”    movieData.movieCategory = sCategoryNameelse    movieData.presence = “invisible”endif

    This script changes the “BOF” and “EOF” Actions (what happens when the record pointer goes beyond the first record in reverse order or past the last record in forward order) such that the record pointer remains in that position (the default is to loop around to the last or first record, respectively). It then moves beyond the first record and checks for “EOF” status. If we’ve reached EOF, then there’s a single record, otherwise, there are more records.As for disabling buttons, you can do that by setting their “access” attribute to “readOnly”:

    dbNext.access = “readOnly”;

    To re-enable them, you simply set their access property to “open”.

  14. Doug Einstadter says:

    Stefan:Thanks for all your helpful suggestions. I finally got my form to work as desired. In the process I discovered several “quirks.” First, with radio button fields, if the field is blank, trying to update the record will fail; the field must contain a non-null or non blank (i.e., “”) value. Second, I came across what seems to be a limit to the length of a query which can be sent using the Database.ExecSQL() function you outlined in your form. My form has about 250 linked fields. When I tried to create an update query with all fields, it resulted in a GeneralError: Operation failed message. After some trial and error, I discovered that if I split the query into two pieces, it worked fine, but if I tried to send it as one long query, it fails.Do you know if this is a LiveCycle specific limitation or something related to JavaScript or the ODBC driver for Microsoft Access?

  15. duncan says:

    your answers are quite satisfactory thus continue posting. my request is idea on how to code for a command button in access form which will help do some calculation based on available records. please help

  16. Doug,Thanks for the report on the quirks you discovered.I asked a colleague of mine familiar with the inner-workings of data connections within Acrobat and he informed me that they don’t impose a limit on the length of the query string.It would seem, then, that the limitation is most likely imposed by the ODBC driver you’re using.

  17. Duncan,I’m afraid I’ll need a little more context in order to provide you with a good answer.Generally speaking, once the data from one or more records has been imported into fields on your form, you can then script simple or complex calculations based on the values of those fields (which are the values of the records).For instance, if you had three fields and you wanted to show their sum in a fourth field, you could use the following FormCalc script on the fourth field’s Calculate event:

    Field1 + Field2 + Field3

  18. Kristin says:

    I was wondering if you had any information on injections on the Javascript being used to insert records into the database. I will be inserting very sensitive information, so I want to make sure the Javascript in the form is secure for that reason. Thanks!

  19. Kristin,Unfortunately, I don’t have any specific information on that.Since this affects your form when it’s running (being filled) in Acrobat, I think your question would be better-suited for the Acrobat Forums

  20. Robert says:

    Stefan,I am about to give up! I have built my form and made a connection to my MS Access db. I can not get the “Submit” button to work or populate my db. I thought this would be easy in LiveCycle Design.I just want to have a simple form to collect people’s info so I can send them a sales packet. My form includes:Current DateFirst NameLast NameStreet AddressCityStateZip CodeE-Mail AddressPhone NumberSubmit ButtonReset ButtonSome basis static text about the business (Name, phone, address, etc.)What am I doing wrong?Thanking you in advance,Robert

  21. Robert,It’s not quite clear to me how you’re expecting the data from those fields to be inserted into your database.Are you using script from this tutorial to execute an SQL “insert” statement on the data connection you’ve defined in order to insert a record with the field data into your database?If not, the only other alternative is to use the “addNew” and “update” methods of the data connection object and bind your data fields to nodes in the data connection itself (by dragging and dropping data nodes from the Data View palette onto the appropriate data fields on your form):In the Initialize event of your “Submit” button, put the following statement (in JavaScript):

    xfa.sourceSet.DataConnection.addNew();

    where “DataConnection” is the name you gave to your data connection when you defined it.This will cause a new record to be created (but not inserted) and all the bound data fields will be emptied (rather than being populated with data from the first record).Then, in your “Submit” button’s Click event, put the following JavaScript statement:

    xfa.sourceSet.DataConnection.update();

    This will update the new record with the data entered into the data fields and insert it into the database.