It' not consistent for deleting all rows with TRestServer

houdw2006 · 2017-06-08 03:55:54

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.

ab · 2017-06-08 07:02:51

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?

houdw2006 · 2017-06-08 14:20:11

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)

ab · 2017-06-08 15:55:32

Please check https://synopse.info/fossil/info/9b812d081b

houdw2006 · 2017-06-08 21:33:15

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)

houdw2006 · 2017-06-08 22:18:12

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)

ab · 2017-06-09 08:14:00

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?

houdw2006 · 2017-06-09 14:32:16

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.

houdw2006 · 2017-06-19 02:29:43

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!

ab · 2017-06-19 06:52:55

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.

keinn · 2017-06-19 14:58:12

TRUNCATE is usefull when deleting some historical data.

turrican · 2017-06-19 19:08:45

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

mORMot Open Source

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

It' not consistent for deleting all rows with TRestServer

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Board footer