Pages: 1

#1 2021-02-25 05:09:39

xwg
Member
Registered: 2021-02-25
Posts: 9

zeos firebird BatchSend error

I have a rest service writed with mORMot1.18_lastest+zeoslib7.2.10+firebird3.0.7, compile with Lazarus2.0.12+fpc3.2.0 target linux-x64. I found a weird problem may crash service sometimes at batchsend , I can't figure out where is wrong, There is a test program to reproduce the error: download it

function write_TodayHQ_batch(fContent: variant): boolean;
var
  aTodayHQ: TSQLTTodayHQ;
  aBatch: TSQLRestBatch;
  iLoop : Integer;
  v: Variant;
begin
  Result := False;
  with _safe(fContent)^ do
  begin
    aBatch := TSQLRestBatch.Create(aRestServer, TSQLTTodayHQ, 200); //200 will error, change to 1000 everything ok
    try
      for iLoop := 0 to Count - 1 do
      begin
        v := Values[iLoop];
        with _safe(v)^ do
        begin
          aTodayHQ := TSQLTTodayHQ.Create(aRestServer, 'FCode=?', [U['Code']]);
          try
            aTodayHQ.FCode := U['Code'];
            aTodayHQ.FPClose := D['PClose'];
            aTodayHQ.FOpen := D['Open'];
            aTodayHQ.FClose := D['Close'];
            aTodayHQ.FHigh := D['High'];
            aTodayHQ.FLow := D['Low'];
            aTodayHQ.FVolume := I['Volume'];
            aTodayHQ.FAmount := D['Amount'];
            if aTodayHQ.ID > 0 then
              aBatch.Update(aTodayHQ)
            else
              aBatch.Add(aTodayHQ, True);
          finally
            aTodayHQ.Free;
          end;
        end;
      end;
      Result := (aRestServer.BatchSend(aBatch) = HTTP_SUCCESS);
    finally
      aBatch.Reset;
      aBatch.Free;
    end;
  end;
end;
20210225 03082838 trace mORMotSQLite3.TSQLRestServerDB(7faccc592c10) BatchSend {"TSQLRestBatch(7faccc5ca140)":{"Count":594,"SizeBytes":83991}}
20210225 03082838  +    mORMotSQLite3.TSQLRestServerDB(7faccc592c10).EngineBatchSend TSQLTTodayHQ inlen=83992
20210225 03082838  +            SynDBZeos.TSQLDBZEOSConnection(7faccc5c9dc0).StartTransaction
20210225 03082838  -            00.000.351
20210225 03082838 DB            SynDBZeos.TSQLDBZEOSStatement(7faccc529520) Prepare 968us update TTodayHQ set FCode=?,FPClose=?,FOpen=?,FClose=?,FHigh=?,FLow=?,FVolume=?,FAmount=? where ID=?
20210225 03082843 SQL           SynDBZeos.TSQLDBZEOSStatement(7faccc529520) ExecutePrepared 79.58ms wr=200 update TTodayHQ set FCode='SZ300326',FPClose=11.25,FOpen=11.2,FClose=11.33,FHigh=11.64,FLow=11.09,FVolume=0,FAmount=208098038 where ID=3116
20210225 03082844  +            SynDBZeos.TSQLDBZEOSConnection(7faccc5c9dc0).StartTransaction
20210225 03082844  -            00.000.034
20210225 03082845 SQL           SynDBZeos.TSQLDBZEOSStatement(7faccc529520) ExecutePrepared 14.06ms wr=200 update TTodayHQ set FCode='SZ300530',FPClose=18.34,FOpen=18.3,FClose=18.54,FHigh=19.28,FLow=18.06,FVolume=0,FAmount=28962745 where ID=3316
20210225 03082847  +            SynDBZeos.TSQLDBZEOSConnection(7faccc5c9dc0).StartTransaction
20210225 03082847  -            00.000.033
20210225 03082847 EXC           EZSQLException ("SQL Error: Dynamic SQL ErrorSQL error code = -104Unexpected end of command - line 1, column 27. Error Code: -104. Invalid token The SQL: EXECUTE BLOCK(P0_0 VARCHAR(; ") [] at   $0000000000841AB8  CHECKINTERBASE6ERROR,  line 902 of ../FPC/zeoslib7.2/src/dbc/ZDbcInterbase6Utils.pas  $0000000000841EED  PREPARESTATEMENT,  line 956 of ../FPC/zeoslib7.2/src/dbc/ZDbcInterbase6Utils.pas  $0000000000853670  PREPARE,  line 344 of ../FPC/zeoslib7.2/src/dbc/ZDbcInterbase6Statement.pas  $000000000050F44B  INTERNALBATCHSTOP,  line 1153 of ../FPC/mORMot/SQLite3/mORMotDB.pas  $0000000000669B5F  PERFORMAUTOMATICCOMMIT,  line 42979 of ../FPC/mORMot/SQLite3/mORMot.pas
20210225 03082860 EXC           EORMException {"Message":"TSQLRestStorageExternal.InternalBatchStop(TSQLTTodayHQ).BatchMethod=mNone"} [] at   $000000000050EB3D  INTERNALBATCHSTOP,  line 1057 of ../FPC/mORMot/SQLite3/mORMotDB.pas  $000000000066967F  ENGINEBATCHSEND,  line 43225 of ../FPC/mORMot/SQLite3/mORMot.pas
20210225 03082862 trace         mORMotSQLite3.TSQLRestServerDB(7faccc592c10) EngineBatchSend json=82 KB add=0 update=594 delete=0 TSQLTTodayHQ

Last edited by xwg (2021-02-25 05:12:08)

Offline

#2 2021-02-25 13:16:45

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

Re: zeos firebird BatchSend error

Could you check what is the SQL actually executed?
The EXECUTE BLOCK syntax may have issues.

Offline

#3 2021-02-26 04:49:21

xwg
Member
Registered: 2021-02-25
Posts: 9

Re: zeos firebird BatchSend error

yes, here is a wrong "EXECUTE BLOCK"

2021-02-26T11:37:24.5850 (20584:0000000000F30040) FREE_STATEMENT
	D:\TEST4\TEST.FDB (ATT_64, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1/64026)
	/mnt/d/test4/bin/fpc-linux64-Debug/project1:127

Statement 35:
-------------------------------------------------------------------------------
EXECUTE BLOCK(P0_0 VARCHAR(10) CHARACTER SET UTF8 = ?,P1_0 DECIMAL(18,4)=?,P2_0 DECIMAL(18,4)=?,P3_0 DECIMAL(18,4)=?,P4_0 DECIMAL(18,4)=?,P5_0 DECIMAL(18,4)=?,P6_0 DECIMAL(18,4)=?,P7_0 DECIMAL(18,4)=?,P8_0 BIGINT=?,P0_1 VARCHAR(10) CHARACTER SET UTF8 = ?,P1_1 DECIMAL(18,4)=?,P2_1 DECIMAL(18,4)=?,...

2021-02-26T11:37:24.5860 (20584:0000000000F30040) FAILED PREPARE_STATEMENT
	D:\TEST4\TEST.FDB (ATT_64, SYSDBA:NONE, UTF8, TCPv4:127.0.0.1/64026)
	/mnt/d/test4/bin/fpc-linux64-Debug/project1:127
		(TRA_88, READ_COMMITTED | REC_VERSION | NOWAIT | READ_WRITE)

-------------------------------------------------------------------------------
EXECUTE BLOCK(P0_0 VARCHAR(                   //error here
      0 ms

Last edited by xwg (2021-02-26 04:57:25)

Offline

#4 2021-02-26 08:39:16

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

Re: zeos firebird BatchSend error

I don't think this EXECUTE BLOCK is generated by mORMot.
We only generate such blocks for insertion, not update.
From the logs, we can see that mORMot is emitting standard UPDATE statements, not EXECUTE BLOCK.

Please check if it is indeed generated by ZEOS, and if it the case, report in their forum.

Offline

#5 2021-02-26 08:49:23

xwg
Member
Registered: 2021-02-25
Posts: 9

Re: zeos firebird BatchSend error

It look like it is zeoslib bug in ZDbcInterbase6Statement in line 373, when ArrayCount mod FMaxRowsPerBatch = 0, av occure.

if (ArrayCount <> FMaxRowsPerBatch) and (FBatchStmts[False].PreparedRowsOfArray <> (ArrayCount mod FMaxRowsPerBatch)) then
    PrepareFinalChunk(ArrayCount mod FMaxRowsPerBatch); //final block of batch

change to

if (ArrayCount <> FMaxRowsPerBatch) and (ArrayCount mod FMaxRowsPerBatch > 0) and (FBatchStmts[False].PreparedRowsOfArray <> (ArrayCount mod FMaxRowsPerBatch)) then
    PrepareFinalChunk(ArrayCount mod FMaxRowsPerBatch); //final block of batch

it look like ok

Last edited by xwg (2021-02-26 15:24:37)

Offline

#6 2021-02-26 09:11:35

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

Re: zeos firebird BatchSend error

@xwg

I guess this is not the proper place to post about Zeos issues.
Please use their forum instead.

Offline

#7 2021-02-27 10:29:59

EgonHugeist
Member
From: Germany
Registered: 2013-02-15
Posts: 190

Re: zeos firebird BatchSend error

@xwg
applied to 7.2-patches. Thank you. See: https://sourceforge.net/p/zeoslib/code-0/7344/
Regards, Michael

Offline

#8 2021-02-27 14:10:47

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

Re: zeos firebird BatchSend error

Thanks @Michael for all this very good support!
smile

Offline

Pages: 1

Board footer

Powered by FluxBB