Eclipse Community Forums: Eclipse Scout » Multiple SQL operations and commit

Help

Home

Home » Eclipse Projects » Eclipse Scout » Multiple SQL operations and commit

Show: Today's Messages :: Show Polls :: Message Navigator

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?

.

Report message to a moderator

Re: Multiple SQL operations and commit [message #818888 is a reply to message #818863]

Mon, 12 March 2012 08:39

Ivan Motsch

Messages: 154
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.

Report message to a moderator

Re: Multiple SQL operations and commit [message #818898 is a reply to message #818888]

Mon, 12 March 2012 08:58

Jeremie Bresson

Messages: 1252
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.

Report message to a moderator

Previous Topic:	Progress Monitor
Next Topic:	Swing Plug-in: UIManager.getString("LoginDialog.cancel")

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 21:13:47 GMT 2024

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter