Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » scout » Multiple SQL operations and commit
Multiple SQL operations and commit [message #818863] Mon, 12 March 2012 08:00 Go to next message
Jeremie Bresson is currently offline Jeremie BressonFriend
Messages: 817
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?

.
Re: Multiple SQL operations and commit [message #818888 is a reply to message #818863] Mon, 12 March 2012 08:39 Go to previous messageGo to next message
Ivan Motsch is currently offline Ivan MotschFriend
Messages: 100
Registered: March 2010
Senior Member
Yes, that is right.

Rule: Every call to the back-end is exactly one transaction.
More specific every invocation of new ServerJob().runNow or new ServerJob().schedule() is one transaction. See ThreadContext.getTransaction.
By default a sql failure and also any other service failure causes a complete rollback.

Fork scenariao: To have multiple transactions (fork) in one server call (ServerJob) simply create two sub ServerJobs.
...server call begin in main transaction
new MyServerJob1(){...}.runNow();//separate transaction 1
new MyServerJob2(){...}.runNow();//separate transaction 2
...server call end


Details:
Any service operation that fails, calls ITransaction.addFailure.
This may also be called by custom code.
At the end of a ServerJob the transaction is evaluated.
If it contains failures or was interrupted, it is rolled back, otherwise it is committed in two pahses. See ITransaction, ITransactionMember, ServerJob, DefaultTransactionDelegate.
Re: Multiple SQL operations and commit [message #818898 is a reply to message #818888] Mon, 12 March 2012 08:58 Go to previous message
Jeremie Bresson is currently offline Jeremie BressonFriend
Messages: 817
Registered: October 2011
Senior Member
Thanks.

I forgot one case:
In one process service, there is a call to an other server call:

//In the server:

public AnswerFormData store(AnswerFormData formData) throws ProcessingException {
  //Delegation to an other server service:
  IAnswerProcessService service = SERVICES.getService(IAnswerProcessServiceDetail.class);
  formData = service.storeFirstPart(formData);
  formData = service.storeSecondPart(formData);

  return formData;
}


Given your answer, I assume this will be part of one single transaction.
Previous Topic:Progress Monitor
Next Topic:Swing Plug-in: UIManager.getString("LoginDialog.cancel")
Goto Forum:
  


Current Time: Fri Dec 19 10:08:30 GMT 2014

Powered by FUDForum. Page generated in 0.01867 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software