#1 2017-06-08 03:55:54

houdw2006
Member
Registered: 2015-05-23
Posts: 48

It' not consistent for deleting all rows with TRestServer

It seems that it is not consistent when deleting all the rows in the same table connected with different TRestServers.

For example, I want to delete all the rows in the table TSQLPeople.

If the RestServer is an instance of  TSQLRestServerFullMemory or descent of TSQLRestServerFullMemory,  I have to call RestServer.Delete(TSQLPeople, '');  // the where condition is empty string.

If the RestServer is an instance of TSQLRestServerDB (for me, the backend DB server is MSSQL 2016), I have to call RestServer.Delete(TSQLPeople, '1=1');  // the where condition should be a True expression string.

From the view of SQL Syntax expectation, It seems to me here the  where condition like  '1=1' is more acceptable.

Offline

#2 2017-06-08 07:02:51

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

Re: It' not consistent for deleting all rows with TRestServer

You are right, for TSQLRestStorageInMemory, there is the DropValues method.
But no specific TSQLRest method for dropping all values...

My guess is that '' is acceptable, since it should generate a DELETE FROM TABLE statement (with no WHERE clause).
We may need to let '' be handled in all DB backends.

Are you sure RestServer.Delete(TSQLPeople, '') doesn't work on your DB?
Could you please try to find out why?

Offline

#3 2017-06-08 14:20:11

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: It' not consistent for deleting all rows with TRestServer

I am using mORMot 1.18.3675.

// fRemoteRestServer := TSQLRestServerDB.Create(fRemoteModel, True);
// trace into after calling RemoteRestServer.Delete(TSQLPeople, ''), at (6) the condition of (SQLWhere = '') meets the exit condition.

(1) mORMot 38807   function TSQLRestServer.Delete(

(2) mORMot 38817   result := EngineDeleteWhere(TableIndex,SQLWhere,IDs);

(3) mORMot 42430  function TSQLRestServer.EngineDeleteWhere(

(4) mORMot 42440  Rest := GetStaticTableIndex(TableModelIndex);
              Rest <> nil

(5) mORMotDB 1314 function TSQLRestStorageExternal.EngineDeleteWhere(

(6) mORMotDB 1322   if (IDs=nil) or (SQLWhere='') or
     (TableModelIndex<0) or (Model.Tables[TableModelIndex]<>fStoredClass) then
    exit;

Last edited by houdw2006 (2017-06-08 14:34:51)

Offline

#4 2017-06-08 15:55:32

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

Re: It' not consistent for deleting all rows with TRestServer

Offline

#5 2017-06-08 21:33:15

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: It' not consistent for deleting all rows with TRestServer

Thank you AB.

The modification makes some progress, but  still does NOT work.

tracing:

mORMotDB.pas  1351    if ExecuteInlined('delete from % where %',[fTableName,SQLWhere],false)=nil ....

I got an EOleDBException at  SynOleDB.pas 2396  procedure TOleDBConnection.OleDBCheck(aStmt:

From the SQL Server Profile, I got the following SQL statement:  delete from dbo.People where

It seems still need to distinguish the SQLWhere is empty or not before calling ExecuteInlined.

The following modification works:

      end;    // line 1348 of mORMotDB.pas
      exit;
    end else if SQLWhere = '' then begin
      if ExecuteInlined('delete from %',[fTableName],false)=nil then
        exit; end
      else if ExecuteInlined('delete from % where %',[fTableName,SQLWhere],false)=nil then
        exit;

Last edited by houdw2006 (2017-06-08 21:41:57)

Offline

#6 2017-06-08 22:18:12

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: It' not consistent for deleting all rows with TRestServer

I met another problem under Win64 (platform Win10, the backend DB is MSSQL 2016).

// aRecord: TSQLPeople;
// RemoteRestServer: TRestServerRemoteDB;
// The Win32 version works perfectly; but the same calling raise an EOleDBException under win64 (the other SQL Statement like delete or select works fine.)

calling: RemoteRestServer.Add(aRecord, True);

Tracing:

(1) mORMot.pas 35221  function TSQLRest.InternalAdd(Value: TSQLRecord;

(2) mORMot.pas 42378  function TSQLRestServer.EngineAdd(TableModelIndex:

(3) mORMotDB.pas 1252 function TSQLRestStorageExternal.EngineAdd(TableModelIndex:

(4) mORMotDB.pas 1266     result := ExecuteFromJSON(SentData,soInsert,0);

(5) mORMotDB.pas 1846 function TSQLRestStorageExternal.ExecuteFromJSON(

(6) mORMotDB.pas 1888      Query := fProperties.NewThreadSafeStatementPrepared(SQL,false);

// the generated SQL statement is an Insert statement, and everything is correct.

(7) mORMotDB.pas 1896      Query.ExecutePrepared;
   
    Raised an EOleDBException with message 'TOleDBConnection: Parameter Error.' // Parameter Error is in Locale language Chinese
   
// TSynLog Information as follows ('参数错误。' means 'Parameter Error.' in Chinese.):

20170608 22102846 ERROR "EOleDBException(02F735D0)":{"EOleDBException(02F735D0)":{"Message":"TOleDBConnection: 参数错误。"}} stack trace API 00931E8D 00D88C6C 00B4C0B2 00B47D60 00A0DAC9 009E21B8 00DAD572 00DE00AD 005F34D9 00629E0B 0062AD94 00410281 005F2BF6 005FA3A7 00629855 005F26D2 005FA59E 005FB898 00410281 005F2BF6 005FA3A7 005F959C 0053E826 00007FFB8C401C24 00007FFB8C40125E 00007FFB8C400FF5 00007FFB8392B022 00007FFB8393FAF8 00007FFB8C401C24 00007FFB8C4017BB

Last edited by houdw2006 (2017-06-08 22:32:19)

Offline

#7 2017-06-09 08:14:00

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

Re: It' not consistent for deleting all rows with TRestServer

Please check https://synopse.info/fossil/info/17520b8f88 about the missing WHERE parameter.

About OleDB/win64 I don't know what is happening here.
Perhaps some integer size issue when binding OleDB parameters in memory buffers?

Offline

#8 2017-06-09 14:32:16

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: It' not consistent for deleting all rows with TRestServer

The empty where condition works well for deleting the whole table now. @AB, Thank you very much. I have no idea what happens behind the OleDB.

Offline

#9 2017-06-19 02:29:43

houdw2006
Member
Registered: 2015-05-23
Posts: 48

Re: It' not consistent for deleting all rows with TRestServer

Hi, @AB. I have another feature request for the WhereCondition is empty. For most back-end database, there is a TRUNCATE command to delete all the rows from a table without logging and end the task more quickly. So can you add the TRUNCATE support for back-end database? Thank you!

Offline

#10 2017-06-19 06:52:55

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

Re: It' not consistent for deleting all rows with TRestServer

This is tempting, but there are some additional features at ORM level.
In fact, when you delete all rows, it not only delete the whole table data, but it can also (depending on configuration) trigger some notification events.

What we could do is to convert the DELETE FROM TABLE statement into a proper TRUNCATE, if applying.

Offline

#11 2017-06-19 14:58:12

keinn
Member
Registered: 2014-10-20
Posts: 100

Re: It' not consistent for deleting all rows with TRestServer

TRUNCATE is usefull when deleting some historical data.

Offline

#12 2017-06-19 19:08:45

turrican
Member
From: Barcelona
Registered: 2015-06-05
Posts: 94
Website

Re: It' not consistent for deleting all rows with TRestServer

Useful after TRUNCATE or masive delete of pages, VACUMM works well to free database pages.

Offline

Board footer

Powered by FluxBB