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

wangming
Member
Registered: 2016-07-17
Posts: 24

Server-side transaction

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: Server-side transaction

Use a batch with automatic transaction.

Offline

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

wangming
Member
Registered: 2016-07-17
Posts: 24

Re: Server-side transaction

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)

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: Server-side transaction

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

Offline

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

wangming
Member
Registered: 2016-07-17
Posts: 24

Re: Server-side transaction

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');

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Server-side transaction

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)

Offline

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

wangming
Member
Registered: 2016-07-17
Posts: 24

Re: Server-side transaction

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;

Offline

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

wangming
Member
Registered: 2016-07-17
Posts: 24

Re: Server-side transaction

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

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Server-side transaction

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)

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: Server-side transaction

ExecuteInlined = Execute with parameters inline.

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Server-side transaction

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 sad  So I always recomend to use Execute...

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: Server-side transaction

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...

Offline

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

wangming
Member
Registered: 2016-07-17
Posts: 24

Re: Server-side transaction

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)"}

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,183
Website

Re: Server-side transaction

?? can't reproduce it here.

Offline

Board footer

Powered by FluxBB