TransactionBegin and BatchStart -> right way?

cypriotcalm · 2017-05-23 05:03:52

Hi,

is it a right way to do it?

try
  if fClient.TransactionBegin(RecordTable) then
  begin
    fClient.BatchStart(RecordTable, 3000);
    
    // this loop takes 20 seconds
    for I to 3000 do
    begin
      fClient.BatchAdd() ;
    end;

    BatchResult := fClient.BatchSend(ResultIDs);     <----------------------  I am getting here TransactionBegin Timeout
    Assert(BatchResult = HTTP_SUCCESS);
    fClient.Commit;
  end;
except
  on E:Exception do
  begin
    fClient.Rollback;
    raise;
  end;
end;

Where can I set the timeout?

if I am using the code below I am getting an exception in SynDB

destructor TSQLDBConnectionPropertiesThreadSafe.Destroy;
begin
  inherited Destroy; // do MainConnection.Free
  fConnectionPool.Free;                                          <-------------- mORMot exception "MySQL should be connected", [FireDAC] connection must be active
  DeleteCriticalSection(fConnectionCS);
end;

the code which I am using

        if fClient.BatchStart(RecordTable, 3000) then
        begin
          for I := 0 to 3000 do
          begin
            ....
          end;
          // Daten in die MDB schicken
          BatchResult := fClient..BatchSend(ResultIDs);
          Assert(BatchResult = HTTP_SUCCESS);
        end;
      except
        on E:Exception do
        begin
          E.Message := fClient.LastErrorMessage + 'My custom message';
          raise;
        end;
      end;

If I have loosed the connection, how can be it active?!

Could you help me pleas? Thank you!

And now I am getting this exception if I kill the mysql connection

{
"errorCode":500,
"error":
{"msg":"did break MyRecTable BATCH process","EORMException":{
	"ClassName":"EORMException",
	"Address":"0398DD00",
	"Message": "TSQLRestStorageExternal.InternalBatchStop(MyRecTable ).BatchMethod=mNone"
}}
}

Last edited by cypriotcalm (2017-05-23 07:56:55)

ab · 2017-05-23 07:06:51

As documented, the transaction should NOT be made on client side.
The transaction is made automatically on the server side, using the AutomaticTransactionPerRow parameter of BatchStart.

Get rid of your manual transaction commands, which conflicts with your AutomaticTransactionPerRow = 3000 parameter.

cypriotcalm · 2017-05-23 07:57:54

ab wrote:

As documented, the transaction should NOT be made on client side.
The transaction is made automatically on the server side, using the AutomaticTransactionPerRow parameter of BatchStart.
Get rid of your manual transaction commands, which conflicts with your AutomaticTransactionPerRow = 3000 parameter.

Okay, thanks, and what about the 2nd one exception if I am executing the batch without transaction?

Why do I get the exception at ConnectionPool (see the code in the previous post)?

Last edited by cypriotcalm (2017-05-23 10:14:06)

cypriotcalm · 2017-05-23 10:09:14

ab wrote:

As documented, the transaction should NOT be made on client side.
The transaction is made automatically on the server side, using the AutomaticTransactionPerRow parameter of BatchStart.
Get rid of your manual transaction commands, which conflicts with your AutomaticTransactionPerRow = 3000 parameter.

What server do you mean? TSQLRestServerDB?

So, something like TSQLRestServerDB.TransactionBegin??

ab · 2017-05-23 10:13:37

If you put 10000 for AutomaticTransactionPerRow ?

cypriotcalm · 2017-05-23 10:19:42

ab wrote:

If you put 10000 for AutomaticTransactionPerRow ?

I start the batch with 3000 for AutomaticTransactionPerRow and do BatchAdd for about 250 000 elements. Then I call .BatchSend and it is being executed about a 1 minute. During the executing I go to the mysql database and KILL the connection. Of course the mORMot is interrupted with the exceptions. Everything is good until the point where the object of TSQLDBFireDACConnectionProperties will be freed. At freeing I get an exception at ConnectionPool.Clear.

Maybe I am explaining everything confused..... The problem is...

  fModel := CreateModel;
  fServer := TSQLRestServerDB.Create(fModel, ':memory:', False);
  fServer.AcquireExecutionMode[execORMWrite] := amBackgroundThread;

  fClient := TSQLRestClientDB.Create(fServer);
  fClient.AcquireExecutionMode[execORMWrite] := amBackgroundThread;

   ....   EXECUTING SOME BATCH ...... AND DURING THE QUERY I KILL THE CONNECTION IN THE MySQL DATABASE ....
   
  fClient.Free;
  fServer.Free;    <-------------------- here EXCEPTION "MySQL should be connected"
  fModel.Free;

Last edited by cypriotcalm (2017-05-23 11:22:41)

ab · 2017-05-23 11:50:57

Never kill the connection while it is still to be used by the ORM.

cypriotcalm · 2017-05-23 11:57:03

ab wrote:

Never kill the connection while it is still to be used by the ORM.

Okay, but what should happen if the network is down for a couple of minutes!? oder the MySQL service is broken and down? How can mORMot handle this?

mORMot Open Source

#1 2017-05-23 05:03:52

TransactionBegin and BatchStart -> right way?

#2 2017-05-23 07:06:51

Re: TransactionBegin and BatchStart -> right way?

#3 2017-05-23 07:57:54

Re: TransactionBegin and BatchStart -> right way?

#4 2017-05-23 10:09:14

Re: TransactionBegin and BatchStart -> right way?

#5 2017-05-23 10:13:37

Re: TransactionBegin and BatchStart -> right way?

#6 2017-05-23 10:19:42

Re: TransactionBegin and BatchStart -> right way?

#7 2017-05-23 11:50:57

Re: TransactionBegin and BatchStart -> right way?

#8 2017-05-23 11:57:03

Re: TransactionBegin and BatchStart -> right way?

Board footer