14 Replies Latest reply on May 13, 2010 5:12 AM by DubDubDubDesigns

    HELP! ISSUE UPDATING DATABASE RECORDS

    DubDubDubDesigns

      Hi All

       

      Thank you for looking at my post.

       

      I have a form which interacts with a SQL database.

       

      The process starts with one member of the team partially completing the form and submitting this.

       

      xfa.sourceSet.DataConnection.addnew(); on form initiaise function

      then

      xfa.sourceSet.DataConnection.update(); on the submit button.

       

      This process works great.

       

      The next member of the team then gets the information by entering the requirement in two textboxes.

       

      var nIndex = 0;

      while(xfa.sourceSet.nodes.item(nIndex).name != "DataConnection")

      {

      nIndex++;

      }

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

      oDB.nodes.item(1).query.setAttribute("text", "commandType");

      oDB.nodes.item(1).query.select.nodes.item(0).value = "Select * from repairform where (A4 = '" + TextField11.rawValue + "') & (A5 = '" + TextField12.rawValue + "')";

      oDB.open();

      oDB.close();

       

      This then populates the form with the record relating to the search term including the partially completed information from the previous team member.

       

      The user then poplulates the rest of the form and suibmits

       

      xfa.sourceSet.DataConnection.update();

       

      This is where is fall down....

       

      On the final submission the form seems to update first record in the database not the one that is currently showing.

       

      I am relatively new to this so it may be me doing something stupid.

       

      Please help.

        • 1. Re: HELP! ISSUE UPDATING DATABASE RECORDS
          pguerett Level 6

          When you close the DB connection when you retrieve the record that will kill the pointer to your record. I do not recommend leaving the connection open, but I would retrieve the record again whne you go to update it, then after the update, close the DB conenction.

           

          Hope that helps

           

          Paul

          • 2. Re: HELP! ISSUE UPDATING DATABASE RECORDS
            DubDubDubDesigns Level 2

            Hi Paul

             

            Thanks for the quick response and all you help so far (including your seminar on acrobatusers.com)

             

            I am confused.... wont calling hte record again before updating the information overwrite what the user has just entered before updating?

             

            Do I just use the original code used to search for the record but insert the xfa.sourceSet.Datasconnection.update(); in between the oDBopen() and oDBclose()?

            • 3. Re: HELP! ISSUE UPDATING DATABASE RECORDS
              pguerett Level 6

              Yes your right ....sorry a brain cramp there. If you insert the update at that time has the user added the changes yet? You coudl leave the DB open but close it after you do the update. My only concern there is that if they abandon the changes then the DB conenction is still open. I woudl assume it would be closed when the form closes but they coudl leave the form open.

               

              Paul

              • 4. Re: HELP! ISSUE UPDATING DATABASE RECORDS
                DubDubDubDesigns Level 2

                Hi Paul

                 

                Thanks once again.

                 

                Sorry I am new to this stuff.

                 

                So do I move the oDBclose(); function to after the final button update

                 

                e.g.

                xfa.sourceSet.DataConnection.update();

                oDBclose();

                 

                or is there a better way to do it.

                 

                I could send you the form if you think that might be easier.

                 

                Thanks Again

                • 5. Re: HELP! ISSUE UPDATING DATABASE RECORDS
                  pguerett Level 6

                  The form is useless without the database so don't bother sending it.

                   

                  Now that I think about it a bit more the oDB object will not exist when you try and update the record. You are creating it in that intitial data retrieval script and when that ends so does the oDB object. So when you do the database update you are in fact going against a different connection and that is why the 1st record is getting updated. So we need to create the oDB object in a global context instead of a local one. You can do that by putting the initialization code for the oDB object in a scripting object. DO NOT PUT IT INTO A FUNCTION. Just put the code at the top of the scripting object. You will want to do upto the cloning of the dataconnection. Then in your retrieval area you can simply use the sriptobjectname.oDB to access the parts that you want. Now we when we are ready we can update and close the oDB connection in a different script.

                   

                  Does that make sense?

                   

                  Paul

                  • 6. Re: HELP! ISSUE UPDATING DATABASE RECORDS
                    DubDubDubDesigns Level 2

                    Hi Paul

                     

                    I do not fully understand but here goes (please point out where I am wrong)

                     

                    I put the following code into the inialise coding area of any field capable of carrying a global script e.g. checkbox

                     

                    var nIndex = 0;

                    while(xfa.sourceSet.nodes.item(nIndex).name != "DataConnection")

                    {

                    nIndex++;

                    }

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

                     

                    do i need to set a global binding for the field containing this script?

                     

                    its the scriptobjectname.oDB where i get lost?

                     

                    what would I put in my final scipt to update??  xfa.sourceSet.DataConnection.update();   or something different?

                     

                    Sorry and thank you for your continued help.

                    • 7. Re: HELP! ISSUE UPDATING DATABASE RECORDS
                      pguerett Level 6

                      No ...you missed the point. The Global binding has nothing to do with this.

                       

                      I am referring to the scope of the object. Normally when you create an object in a script (oDB) it is available to be used for the duration of the script. As soon as the script ends the object is cleaned up and the memory that is used by that object is released...hence no more object.

                       

                      We need to create the object in such a way as to make sure that the object stays araound for the duration of the form. To do this we create a scripting object. Rigt mouse click on the hierarchy root node (usually form1) and choose the Insert Scripting Object option. This will create a variables node with an unnamed scripting object in your hierarchy. Now rename the scripting object to myScriptingObject. There is no representation of the scripting object on the canvas so you can only access it through the hierarchy view. When you select the scripting object the scripting editor will become active (you do not script against events in a scripting object). Now you can add the code to the scripting object. Typically you create functions inside the scripting objects and call those functions from the form. If you define objects outside of the function then they are global and are available for the duration of the form. I included a sample form (that will not work becuase you do not have the DB) but it uses this technique. Have a look at how the var is created in the scripting object, then I create an InitDB function that is called when the get specific Record button is pressed..

                       

                      Hope that helps

                       

                      Paul

                      • 8. Re: HELP! ISSUE UPDATING DATABASE RECORDS
                        DubDubDubDesigns Level 2

                        Hi Paul

                         

                        Sorry, I now understand the global sripting

                         

                        My only issue now is updating the record.

                         

                        I can get the form to grab the information using the global variable

                         

                        I am just not sure how to update the record as xfa.sourceSet.DataConnection.update(); does not work (well it still updates the first record).

                         

                        Do I still have to close the oDB from the first call to grab the specific record and then re-open odb when I want to update or leave it open the whole time?

                         

                        Do I have to create an SQL command (such as the select command given when searching for a record) to update the records manually?

                         

                        e.g. UPDATE formname SET etc etc

                         

                        as this would be a long process with 122 data fields.

                         

                        any thoughts to finish this process off would be greatly appreciated.

                        • 9. Re: HELP! ISSUE UPDATING DATABASE RECORDS
                          pguerett Level 6

                          Now that oDB is global ....you would remove the close command from the retrieval of the recored, then do the update on the oDB object (as that is the one pointing to the proper record). Then close the oDB connection.

                           

                          Make sense?

                           

                          paul

                          • 10. Re: HELP! ISSUE UPDATING DATABASE RECORDS
                            DubDubDubDesigns Level 2

                            Hi Paul

                             

                            Im really sorry..... I do understand what I need to do...

                             

                            But I cannot see for the life of me what I need to put to update the oDB.

                             

                            I have tried myScriptObject.oDB.update();

                             

                            and a few other failed versions.

                             

                            can you advise as to the script i need to use to update the oDB object that is pointing to the current record.

                             

                            Also are there any requirements within the SQL database such as indexes, primary keys etc or the setting such as dynamic cursors etc that I need to set?

                             

                            thanks.... Im sure this will also be helpful to others as lots of people appear to have read this post so far.       

                            • 11. Re: HELP! ISSUE UPDATING DATABASE RECORDS
                              pguerett Level 6

                              You shodul be able to use myScriptObject.oDB.update(). It might be time to have a look at the form ...can you send it to LiveCycle8@gmail.com?

                               

                              Paul

                              • 12. Re: HELP! ISSUE UPDATING DATABASE RECORDS
                                DubDubDubDesigns Level 2

                                Thanks Paul I have emailed the form and and export of the SQL database, which only hold sample data for testing.

                                 

                                Let me know your thoughts

                                 

                                Thanks

                                • 13. Re: HELP! ISSUE UPDATING DATABASE RECORDS
                                  DubDubDubDesigns Level 2

                                  Hi All

                                   

                                  For those of you with similar issues following this thread.

                                   

                                  Paul took a look at my form and the following rectified the issue

                                   

                                  I found a bug that is destroying our global object and hence when we do the update command the object is not there anymore. It has to do with garbage collection. The fix was put into a later version of XFA than what your form is designed for.

                                   

                                  First thing is that the code you have on the Update button is not correct. You shoudl do a myScriptObject.oDB.update() then a myScriptObject.oDB.close() then your success message.

                                  Second you need to change the target version. Under the File menu choose Fprm Properties then the Deafult tab. There is a setting for a target version on that page. Yours is set for 8.1 -change it to 9.

                                  Third you need to remove a processing instruction from the XML source file. In designer there is a tab for Design View and PreView. Inbetween those two there is a possibility to have an XML Source View. If that tab is not there then right click on the Design View and check off the XML Source. Now the tab shoudl be visible. Click on it and it will show the XML source of your template. Right click anywhere in teh XML view and turn on line numbers. Scroll down to line 6382 ....theline shodul look like this:

                                   

                                  <?originalXFAVersion http://www.xfa.org/schema/xfa-template/2.6/?>

                                   

                                  Once you find it ....remove it. Then click on Design view. You will be asked if you want to save your changes.....click Yes.

                                   

                                  Now try your form again. You can add a command to the buttons to validate that the object still exist.

                                   

                                  app.alert(myScriptObject.oDB)

                                   

                                  If it exists you will get a message box that indicates that XFA Object if it does not exist you will get an error in the java console indicating oDB is not an object.

                                   

                                  Let me know how this turns out.

                                   

                                  Paul

                                   

                                   

                                  Thanks Paul for all your help on this one. Now works perfectly.

                                  • 14. Re: HELP! ISSUE UPDATING DATABASE RECORDS
                                    DubDubDubDesigns Level 2

                                    Hi Paul

                                     

                                    Just one more thing on this issue.

                                     

                                    Can any of what we are doing here be used with Adobe Reader or does every person require acrobat to access and update the sql database?

                                     

                                    Thanks