Multiple SQL operations and commit [message #818863] |
Mon, 12 March 2012 08:00 |
Jeremie Bresson Messages: 1252 Registered: October 2011 |
Senior Member |
|
|
I am using an Oracle Database.
First case
I am wondering when the commit occurs when I am using several SQL insert and update calls within the same ProcessService function:
public AnswerFormData store(AnswerFormData formData) throws ProcessingException {
//First SQL Statement
SQL.update("update answers set name = :YourName where answer_id = :AnswerNr", formData);
//Other SQL Statements
for (Long choiceId : formData.getChoices().getValue()) {
SQL.insert(" insert into answers_choices (answer_id, choice_id) " +
" values (:AnswerNr, :ChoiceId) ", formData, new NVPair("ChoiceId", choiceId));
}
}
What happened if one of the other SQL calls throws an Exception?
With a quick Test, I figured out that the commit operation seems to occur at the end of the Process Service call.
If one SQL call throws an Exception, there is a rollback (isn't it?). This is consistent with the client state, because if something goes wrong, I expect the form to stay open because of the ProcessingException.
Second case
Does it also mean that if I want to do the same inserts statement from multiple service calls in the client, I will have a commit for the First Part and not for the second?
@Override
public void execStore() throws ProcessingException {
IAnswerProcessService service = SERVICES.getService(IAnswerProcessService.class);
AnswerFormData formData = new AnswerFormData();
exportFormData(formData);
formData = service.storeFirstPart(formData);
formData = service.storeSecondPart(formData);
}
In the server:
public AnswerFormData storeFirstPart(AnswerFormData formData) throws ProcessingException {
//First SQL Statement
SQL.update("update answers set name = :YourName where answer_id = :AnswerNr", formData);
}
public AnswerFormData storeSecondPart(AnswerFormData formData) throws ProcessingException {
//Other SQL Statements
for (Long choiceId : formData.getChoices().getValue()) {
SQL.insert(" insert into answers_choices (answer_id, choice_id) " +
" values (:AnswerNr, :ChoiceId) ", formData, new NVPair("ChoiceId", choiceId));
}
}
Could you confirm this?
Could you also provide some input on how transactions, commit and rollback are handled?
.
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.03482 seconds