Server-side transaction

wangming · 2016-07-17 01:04:37

Server-side transaction, the client submits multiple SQL statements, error, how to solve, please help me

ab · 2016-07-17 05:20:29

Use a batch with automatic transaction.

wangming · 2016-07-24 01:35:35

I write tested, but also can realize the function of things, do them right, as well, can not do what the Chinese version of the data, China's software developers, this framework is indeed too difficult to learn, hard to chew all the information

function  TServiceRemoteSQL.MulExecute(const QSQL: TRawUTF8DynArray; aExpectResults: Boolean): Boolean;
var
  res,i: Integer;
  newCon: TSQLDBConnection;
begin
  Result := True;
  if fProps=nil then
    raise Exception.Create('Connect call required before Execute');
  newCon := fProps.MainConnection;
  try
    newCon.StartTransaction;
    for I := Low(QSQL) to High(QSQL) do
    begin
      fProps.ExecuteInlined(QSQL[i],aExpectResults);
    end;
    newCon.Commit;
  except
    newCon.Rollback;
    Result := False;
  end;
end;

Last edited by wangming (2016-07-24 01:37:35)

ab · 2016-07-24 08:32:55

As I wrote, you should better NOT use SQL statements, but the ORM and batch process.
See http://synopse.info/files/html/Synopse% … ml#TITL_28

wangming · 2016-07-31 01:03:29

Client query data once, when the server is stopped, always suggested that such an error, the server database engine is used fProps: TSQLDBConnectionProperties;

assert(OleDBCoinitialized>0,'You should call TOleDBConnection.Free from the same '+
    'thread which called its Create: i.e. call MyProps.EndCurrentThread from an '+
    'THttpServerGeneric.OnHttpThreadTerminate event - see ticket 213544b2f5');

mpv · 2016-07-31 09:22:47

This is limitation of the OleDB. Please, read this topic You must define a OnHttpThreadTerminate handler and release database connection there.

Last edited by mpv (2016-07-31 09:23:24)

wangming · 2016-08-16 12:29:44

Each time access to data, will increase the server's memory has been increased to go, if you do not query the data, the memory does not increase, please look at the help

function TServiceRemoteSQL.Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON;
var
  res: ISQLDBRows;
  fProps: TSQLDBConnectionProperties;
begin
  try
    fProps := Pub_ConnDataSQL.GetDBConnectionProperties;
    if fProps=nil then
    begin
      raise Exception.Create('Connect call required before Execute');
    end;
    res := fProps.ExecuteInlined(aSQL,aExpectResults);
    if res=nil then
      result := ''
    else
      result := res.FetchAllAsJSON(aExpanded);
  finally
    fProps := nil;
  end;
end;

wangming · 2016-08-16 12:31:29

I do not know why the increase, really do not understand

mpv · 2016-08-16 12:40:37

Try to execute a method 100 times. Is memory still increase? Memory will increase because of internal prepared statements cache but increasing must stop after a while.
In any case it is better to use Execute with parameters instead of ExecuteInlined

Last edited by mpv (2016-08-16 12:42:00)

ab · 2016-08-16 13:40:17

ExecuteInlined = Execute with parameters inline.

mpv · 2016-08-16 14:01:06

I know, but in most case developer first create the statement with inline parameters

 sql = FormatUTF8('select * from user where id = :(%):', [userID])

and when pass an sql to the ExecuteInlined, after this ExecuteInlined parse it and call Execute. A little bit overhead, but I saw such code many times So I always recomend to use Execute...

Last edited by mpv (2016-08-16 14:01:46)

ab · 2016-08-16 14:59:31

The overhead is minimal, in practice, and is nothing in comparison to the SQL execution itself.
Also note that there is a less error-prone syntax of FormatUTF8:

 sql = FormatUTF8('select * from user where id = ?', [], [userID]);

And in all cases, using the ORM with ? parameters would use this inlining trick, but will allow to cache the last JSON results, including inlined parameters.
So when the ORM is used, inlining will in fact enable caching, so may be able to speed up everything...

wangming · 2016-09-08 02:25:25

Example 14, often times out, the server and the client are on a computer, there will be, I do not know where to set bad，The following error message:
Server not available - {"EWinHTTP":"winhttp.dll error 12002 (timeout)"}

ab · 2016-09-08 08:33:18

?? can't reproduce it here.

mORMot Open Source

#1 2016-07-17 01:04:37

Server-side transaction

#2 2016-07-17 05:20:29

Re: Server-side transaction

#3 2016-07-24 01:35:35

Re: Server-side transaction

#4 2016-07-24 08:32:55

Re: Server-side transaction

#5 2016-07-31 01:03:29

Re: Server-side transaction

#6 2016-07-31 09:22:47

Re: Server-side transaction

#7 2016-08-16 12:29:44

Re: Server-side transaction

#8 2016-08-16 12:31:29

Re: Server-side transaction

#9 2016-08-16 12:40:37

Re: Server-side transaction

#10 2016-08-16 13:40:17

Re: Server-side transaction

#11 2016-08-16 14:01:06

Re: Server-side transaction

#12 2016-08-16 14:59:31

Re: Server-side transaction

#13 2016-09-08 02:25:25

Re: Server-side transaction

#14 2016-09-08 08:33:18

Re: Server-side transaction

Board footer