We have an app where users can add entries. The first form contains of a table view with the entries they can add or change, then the user can press on a Add button and another form opens where the details of each entry can be filled in. Classic way to add/edit a database table.
Anyway, for some weeks, we encounter a strange problem. If the user adds an entry, it might happened to have changes in the previous entry (the last one). We log the actions using audit trail, and we saw add actions for a new entry and in the same second edit actions on the previous one. It happened on two tables. For one we had deletions (in case user press cancel in the details form, we delete the new created record), but the delete was not on the new record, but on the previous one.
Anyone encounter this before, or has any idea. Except the fact that I’m double checking my code to be sure that everything is OK, I was thinking that for some reasons the foundset.newRecord() do not work properly and actually a new record is not created and the cursor remains on the last record, that’s why I get the changes always on the last record which is already in the database.
And the worse thing is that locally we could never reproduce it. It happened only on the server and not always, but from time to time. Even on the server I couldn’t reproduce it, but it only appeared for some users. The only thing we have to prove for ourselves that it is as the users says is the log, and they are right.
How can I be fully sure that the foundset.newRecord() did its job and moved the cursor on the new created row? In case it fails, what value returns instead of the index of the new created record?
I think it is possible to check if a new record has been created.
From sample code :
var idx = foundset.newRecord(false); // add as last record
// foundset.newRecord(); // adds as first record
// foundset.newRecord(2); //adds as second record
if (idx >= 0) // returned index is -1 in case of failure
{
foundset.some_column = "some text";
application.output("added on position " + idx);
// when adding at the end of the foundset, the returned index
// corresponds with the size of the foundset
}
So in case of failure newRecord() returns -1. But when it returns 0? The foundset indexes starts with 1 right?
The three examples of newRecord() in your code also reset the selected record in the foundset to the new one, right?
I will add this check in my app and see if the problem will appear.
And another thing, I almost forgot about this. After I call newRecord() I also call and databaseManager.saveData() and then start a transaction. In case user cancel, I rollback the transaction and remove the record. Id this OK, or I saveData() can be avoid?
Hi again, I’ve made some other tests, and I found something that at least for me seems very strange:
This is my code for adding a record. I’ve add also those outputs to see exactly what happened.
If I call databaseManager.saveData(); as it is now, before loadRecords on the edit_form.foundset, the record_id will not the one that I just added, but the last record in the foundset before the new record was added. But that output in the for loop will print all records, including the new added on. If I move that for after saveData(), it will not display the new added record.
If I comment that databaseManager.saveData(); line, in the edit_form, I can’t access anything from the local foundset. For example, a relation with another table has the value null. So as far as I can see, I need to call databaseManager.saveData();, but maybe after loadRecords() … I try like this.
application.output("size before: " + foundset.getSize());
var index = foundset.newRecord();
if (index <= 0) {
return;
}
application.output("size after: " + foundset.getSize());
for (var i = 1; i <= foundset.getSize(); i++) {
application.output("id: " + foundset.getRecord(i).record_id + ", at " + i);
}
databaseManager.saveData();
application.output("flight_log_id " + flight_log_id + ", index " + index);
forms.edit_form.foundset.loadRecords(record_id);
Good point, the edit_form used a separate foundset. I see that if I use the DEFAULT foundset I don’t need the loadRecords() and neither the saveData() calls. and also I don’t need to delete the new record in case user press cancel in the edit_form.
But can I use databaseManager.startTransaction(); before calling newRecord() ? And then rollback or commit after user ends its changes?
Thanks.
… but I figure out that I need to use a separate foundset in other related forms because I have methods there that I call from other places as well, and also I need to save the record, otherwise, for the new record I can’t call those methods, because the record can be accessed from there.
Keep the database transactions open only for very short periods.
So set autosave to false.
This way you can use “in memory transactions” that you can save by using saveData and rollback using rollBackEditedRecords.
I only use database transactions in my global Save method.
This method is structured as follows :
globals.validateMethod (does the validation of all fields, unique checks, and so on )
databaseManager.startTransaction()
globals.onPreSave
databaseManager.saveData
globals.onPostSave
databaseManager.saveData
databaseManager.commitTransaction
so can I call setAutoSave(false) when the edit_form is displayed and then after all changes has done, when user press Save, start a transaction at that point and then save the data and commit?
because right now I start the transaction when the edit_form is displayed and I commit when the edit_form is hidden.
In case I start the transaction just before commiting, and not in the same time with setAutoSave(false), then any related foundsets with the one from the current form, will not rollback in case data is deleted from there.
But if I start the transaction in the begining, the changes made in the relations will work fine!
When you use the In-Memory transaction (i.e. setAutoSave(false) ) then a delete is still send straight to the database. That is the reason why you can’t roll back a deleted record.
I wrote about this issue in an article on the ServoyCamp website with a possible workaround.
Hans said earlier that a transaction have to be used only for a short while, which I agree. I assume the Connection object will be locked until the transaction will commit or rollback, and if this takes lot of time that’s bad, really bad.
OK now, how do I solve the case where I have a Save/Cancel form with all fields bound to the foundset, and to make this work I actually need to start a transaction when the form opens and commit it when the Save is pressed. But the time between the open of the form and the save action (or cancel) are decided by the user, and it will certainly be more then few seconds, usually minutes.
Well, to lock a Connection from the pool for few minutes is indeed a long time.
If my presumption is correct, what’s the way to fix data commit/rollback for Save/Cancel forms?
udrescu_bogdan:
If my presumption is correct, what’s the way to fix data commit/rollback for Save/Cancel forms?
Why not just use an ‘in memory’ transaction.
Just set autosave to false
At on Hide you can rollback edited records and put back on autosave.
On clicking save you can perform a data save.
The only solution I have now in mind is to bind each single UI field to a form variable and then set these values to the actual foundset within a transaction. This will work, indeed, but it’s to much work to change all forms I have and actually duplicate each single database field and then just to write a long list of foundset.field = formField … OK, it can be shorten, but still adding all those form variables is not a nice thing to do.
Do you have anything else in mind?
If I only set autosave to false when the form show, will that lock the actual Connection so that other threads won’t be able to use it, until I set the autosave back to true? I think yes, otherwise there is no meaning for this method if another process can acquire the same connection and update data on it or change the autosave value. So a Connection must be locked if startTransaction or setAutoSave are called.
Then, how is acquireLock works?
Will it lock that record only, and not the Connection itself?
What happen if the user just close the browser, before I can release the lock?
Will the lock be released automatically on the spot?
How can I use commit and rollback on that locked record so that it will behave as a transaction, but without locking the actual Connection, or actually lock it only for a short while at the end when the data is updated into the database?
Please excuse me if I don’t understand , but I just don’t see the answer to my question:
How do I avoid locking a Connection from the time the user open a form and the time he press on save or cancel. It’s only one record, I have to use transactions and autoSave to false, otherwise, how can I do commit or rollback?
If I look in the Servoy Admin → Database Servers, each time I open a form with a save/cancel button, because I use transaction there, the Active connections number increase. Well, if I have a maximum of 200 connections and in the same time 200 users open a form, all 200 connections will be locked, right? So the user 201 will have the app hanging until one of the first 200 users will either cancel or save the form.
How am I solving this? How do I offer a save/cancel form, but without locking the connection while user have that form open?
on open FID: databaseManager.setAutoSave(false);
on hide FID: revertEditedRecords(foundset.getSelectedRecord()); databaseManager.setAutoSave(true);
onActionEvent save button: databaseManager.saveData(foundset.getSelectedRecord());
yes, if you have in your FID, for example, a tabpanel, with some related data lines, and you delete one of those related lines, you CAN’T rollback that delete!