Hi,
I ran across something today that puzzles me. I am testing a new solution doing ‘automated’ inserts in Oracle. The inserts currently are going into a ‘TEST’ schema but I am actually getting the ID from our production schema (to simplify it when I move it all to the Production Schema). One insert that I tried used an ID that violated a foreign key constraint in the TEST database and so the insert naturally failed. (The test schema isn’t as up to date as the production schema so this could easily happen). What was strange though is I got no warning in Developer that it had failed (I have the standard global ‘OnErrorHandler’ method working). This is my code:
databaseManager.startTransaction();
forms.lpp_patient.patient_to_patient_physician.newRecord()
forms.lpp_patient.patient_to_patient_physician.physician_id = dataset.getValue(1,2)
forms.lpp_patient.patient_to_patient_physician.entryid = forms.lpp_patient.entryid;
forms.lpp_patient.patient_to_patient_physician.entrydate = new Date();
forms.lpp_patient.patient_to_patient_physician.role = '6'
databaseManager.saveData();
databaseManager.commitTransaction();
(The physician_id is what is incorrect). The error is recorded in the Servoy.log as follows:
2008-10-01 15:57:51,026 ERROR [AWT-EventQueue-0] com.servoy.j2db.util.Debug - Throwable
java.sql.SQLException: ORA-02291: integrity constraint (JOHN_TEST.PAT_PHY_ID_FK) violated - parent key not found
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
All insert/update failures in my experience have always given feedback with Servoy but this one just disappeared without a trace. Now this particular error by definition can never normally happen as the ID will come from the Production Schema and then be inserted and thus the ID will always be there. But it would be easy for my datamanagers to miss such an event in some other situation if this were to occur, especially when I am having them do this fairly intricate ‘auto’ inserts (going into about 7-10 different tables). Is there something else I should be doing to capture this error and bring it to the user’s attention?
John,
databaseManager.saveData() is a boolean method, it returns false if an error occurred.
Rob
Hi Rob,
My mistake for including that. I tried it various ways and that was the one I copied. However, whether I just do a start trans/commit trans or include a saveData command it always returns without an error (other than the Servoy log). I can test for whether it saved or not via saveData and it will return false and I can programmatically deal with it in some fashion. Perhaps I’ve been spoiled by being able to do relatively ‘clean’ inserts and updates and/or having smart datamanagers who don’t make many mistakes! But I’ve been used to Servoy putting up an error automatically if something breaks down and certainly when running the global method using ‘isException’. But in this case the error doesn’t trigger that method. All other ‘failures’ (database as well as others) that I have encountered however always seem to give an error via isException. And if I get the boolean from saveData that a) means more programming and b) I don’t think I can get the cause of the failure from that. As mentioned this error won’t actually ever happen by definition once I go fully to Production but it bothers me why this particular insert seems to fail without a message. I’m probably just missing something but I can’t seem to figure out what…
Oh also what I forgot to say is that if I leave out the whole ‘start trans/saveData/commit trans’ altogether then the global, solution setting error message does come up. But that can’t be the right way to trigger the messages can it? Shouldn’t one be able to access that whole message after a ‘saveData’ failure rather than just the boolean ‘false’? I could swear that I’ve had the global error trigger before in those situations but maybe not… As mentioned I haven’t had to deal with this very much (error messages from inserts/updates)…
Hi John,
I am using an Oracle database with all kinds of constraints.
I ran into a simmular problem a while ago and created a case for this. (case 83245)
I am using autosave off, transactions an saveData within the transaction.
Servoy explained it :
Hello,
In your sample solution, you are combining autosave=off and transactions.
When commitTransaction() is called, automatically saveData() is called first.
If not all changed record are saved (due to sql problems), commitTransaction() will return false and the transaction has not been commited.
After a failed saveData() or commitTransaction() you can get the failed records and examine the errors, see code snippet below.
So basically, you always need to check the return value of saveData() or commitTransaction() and examine the failed records.
The reason that this is not handled in the onError method is that there may be multiple outstanding records saved at once in the saveData() call and as a result multiple errors may occur.
Regards,
Servoy Support
We built a framework together with Sanneke from Servoy.
When we ran into this issue we made a new save method that deals with this issue.
I you want i can mail i to you.
Regards,
Hans Nieuwenhuis
Hi Hans,
I’d love to hear your method. As I mentioned due to the nature of these solutions it very rarely happens that an insert/update fails. But if it ever does I’d like to make sure my datamanagers know that if failed at least so that it doesn’t just slip past. I haven’t really used autosave off until now because I haven’t felt I needed to with what I do. But maybe I have to revisit that…
John
I’ll sent it by private mail.
Regards,
An example of a save method could be :
databaseManager.startTransaction()
if(!databaseManager.saveData())
{
var _failedArray = databaseManager.getFailedRecords()
for( var i = 0 ; i < _failedArray.length ; i++ )
{
var _record = _failedArray[i];
//send info about error to output in this case only exception
//or do something with the error record
application.output('Error in save : ' + _record.exception);
}
var _thePressedButton = plugins.dialogs.showErrorDialog('Error in Save', 'See output window','OK');
//do rollback
databaseManager.rollbackEditedRecords()
databaseManager.rollbackTransaction()
return;
}
else
{
application.output('Succesfull save');
}
if(!databaseManager.commitTransaction())
{
var _failedArray = databaseManager.getFailedRecords()
for( var i = 0 ; i < _failedArray.length ; i++ )
{
var _record = _failedArray[i];
//send info about error to output in this case only exception
//or do something with the error record
application.output('Error in commitTransaction: ' + _record.exception);
}
var _thePressedButton = plugins.dialogs.showErrorDialog('Error in Commit', 'See output window','OK');
//do rollback
databaseManager.rollbackEditedRecords()
databaseManager.rollbackTransaction()
return;
}
else
{
application.output('Succesfull commmitTransaction');
}
In our framework all possible errors should be detected before the save is done.
(in pre-save validation)
But when we missed something, we see it and can adjust the pre-save validation.
That is the reason that i only display the error and do a rollback.
Regards,
Hi Hans,
Thanks a lot for that. My datamanagers are using Servoy not for data entry but rather for reviewing and migrating data from one dataset to another, saving on time and data entry errors. Because of this I can pretty much eliminate any data entry errors as everything is tested (related) before inserting/updating automatically. However eventually I’d like to extend my solutions to handle some fairly complicated regular, ‘manual’ data entry and so this is very helpful. A few follow up questions on yours and Sanneke’s method(s) if you don’t mind:
-
I worked on getting it going in a somewhat dumbed down manner, partly because I’m lazy and partly because I don’t need some things in my situation like locking records (our datamanagers can’t really by definition ever be updating the same record). One thing that does confuse me though is why you/Sanneke error check on commit as well as on save? I didn’t know you could have an insert that saves successfully but doesn’t commit properly. Could you expand on that?
-
I had seen the function ‘getFailedRecords’ but I took it to be literally that (and only that): a way to retrieve the records that failed. I didn’t realize that you could use that to get the cause of the failure (exception). Even when I read your code I thought that ‘.exception’ must be a named parameter within the array returned by getFailedRecords. But I guess ‘.exception’ is a function of getFailedRecords would that be the right description? Of course eventually I actually read the documentation of getFailedRecords and the only example given is using ‘.exception’ in conjunction with it. Pays to actually read the docs I guess rather than just figure you know what something means! ![:oops:]( "Embarassed")
-
I’m curious how you deal with getting the ‘message’ of the failure back from the user to the developer or admin using application.output. Or is this just for use while developing with Developer? I figured that if I was doing this I should have a way for my datamanagers to be able to see a possible error and report it in a simple way back to me if it happens. So I went about this in a slightly different way which seems to work fine in my testing this weekend but I’d like your input if you notice any potential flaws.
- My global method checking a failure is only called after the ‘startTransaction’, ‘newRecord’ and then the actual data inserts are completed in the calling form method. In the form method at that point I run:
if(!databaseManager.saveData()) {//If the save fails find out why
globals.FailedSaveHandler('patient_physician','AddReferringMD')
return;
}
else {
databaseManager.commitTransaction();
}
(I actually just give as arguments to the method the name of the table I am saving to and the name of the form method that is calling the global failure method. At this point I’m not bothering to try and make it completely universal by coding in those arguments. It only takes a couple of seconds to type them in and I don’t have hundreds of methods and forms at this point.)
In my global method I decided to give the user the possibility of ‘saving’ the error message by copying it to the clipboard for possible pasting into an email or whatever. Here’s the global method which gets called if saveData is false:
var table_affected = arguments[0]
var cur_method = arguments[1]
var _failedArray = databaseManager.getFailedRecords()
for( var i = 0 ; i < _failedArray.length ; i++ )
{
var _record = _failedArray[i];
application.output('Error in save 1: ' + _record.exception);
var PressedButton = plugins.dialogs.showErrorDialog('Error','Error in save: ' + _record.exception.getMessage() + ' \
To save this error message to your clipboard for pasting into an email click "Save Message". \n \
Then go to your mail application and when you hit "paste" the message will be retrieved from your clipboard. \n \
Otherwise to simply dismiss this message click "OK"', 'Save Message', 'OK');
if(PressedButton == 'Save Message') {
application.setClipboardContent('The error occurred in method "' + cur_method + '" saving ' + _record + ' to ' + table_affected +'. \
This error occurred because: ' + _record.exception.getMessage())
}
}
databaseManager.rollbackEditedRecords()
databaseManager.rollbackTransaction()
As mentioned this seems to work fine but if you have any things that I might have missed or not understood I’d love to get your feedback. In any case many thanks for letting me know that ‘getFailedRecords’ is how to find the reason behind the failure and not just the records themselves! ![:)]( "Smile")
Hi John,
Your code looks okay to me. In the first sample i would include a
databaseManager.RollbackEditedRecords and a databaseManager.rollbacktransaction to cleanup.
Otherwise (in my case when using autosave off) the “E” and the transaction sign in the lower
right corner stay on.
The use of saveData and then commiting :
- I use this in combination with defferred constraints :
Normally, constraints are checked as data is inserted or updated in the row.
When a row is inserted, all the constraints are checked and the row either accepted
or rejected. You can, however, defer the constraint checking until the transaction commits.
So i can manipulate data without the constraints kicking in, but at the end the constraints are checked.
Maybe one of the Servoyans can explain what a saveData does when Transactions are used.
My idea is that when using transactions the saveData saves the data to the Servoy Server, but not to te database.
But i am not sure that that is correct ?? I can not find any information on that in the documentation
Regarding the error message :
Our users will tell us when an error occurs and we then check the Servoy log page
You could create a log file and write the errors into that.
Your DatabaseManagers could then check this file on a regular basis.
Regards,
Hans
Hi Hans,
Rob would you chime in on if one is using transactions and saveData whether one needs to test for failure on both saveData AND commit? It seems to me that if it is going to fail it will always fail on saveData, at least it always has in my experiments.
With this new ‘scrolling’ thing in the Forum when quoting code you might not have seen that I included the Rollback of edited records and transactions at the end of my global checking method. Or did you mean it would be better practice to attach those in the form method?
In terms of handling it if an error does occur I kind of like showing my datamanagers immediately what happened and why it failed. And copying it to the clipboard allows them to send it to me without having to open a file or anything. Keep 'em nailed to their keyboard! ![:D]( "Very Happy")
saveData saves your new/changed records to the database.
only if you have a database transaction then those changes in the database can be rollbacked at the database level when you call rollbackTransaction()
john: _record.exception is a property of a record where it has the exception stored in that explains the reason why this particular record failed.
OK. So if I understand this correctly:
-
With autosave off and using transactions/saveData… In other words, in SQL terms Servoy is simply doing an update/insert within a transaction; Servoy does no extra step or ‘quasi save’. If I have one record in a transaction/save and the save succeeds then no reason to check anything else just commit the transaction, if the save fails then rollback. If I have a series of saves and I want to rollback the whole series if any single one fails then enclose the whole series in one transaction. In that case keep track of the save failures for information but carry on until the end or until you would be trying to create child records of failed parent saves. In either of those cases at that point rollback the transaction (all saves) and gather the information as needed.
-
I understand now that .exception is a property of record (and if I had read the documentation properly of getFailedRecords I should have seen that as that property is explicitly pointed out). And that is very cool; it’s given me all kinds of things to play with! ![:D]( "Very Happy")
But how would I know that otherwise, i.e. that .exception is a property of record? In other words how could I figure out what the properties are of the records returned by getFailedRecords? Are there other properties as well? Within the method editor I don’t see how I can figure that out.
in the code completion of 4.1 we will have “exception” and also “foundset” on record (those are 2 special properties on record)
so now with code completion you will see them and you can use them easier.