HELP! ISSUE UPDATING DATABASE RECORDS

[Former Community Member]

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.

[Former Community Member]

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

[Former Community Member]

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()?

[Former Community Member]

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

[Former Community Member]

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

[Former Community Member]

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

[Former Community Member]

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.

[Former Community Member]

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

[Former Community Member]

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.

[Former Community Member]

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

[Former Community Member]

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.       

[Former Community Member · Accepted solution]

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

[Former Community Member]

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

[Former Community Member]

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.

[Former Community Member]

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