Problem with deleting record in Oracle

ASiwon · 2018-09-27 09:55:41

Hello,

today I found problem with using mORMot's Delete function for object which is mapped to the table in Oracle database. I have model class which looks like:

  TBkLogZmiana = class(TSQLRecord)
  strict private
    FRodzajId: TBkLogZmianaRodzajKey;
    FZmianaId: TBkLogTabelaKey;
  published
      /// <summary>
      /// Identyfikator rodzaju zmiany.
      /// </summary>
    property RodzajId: TBkLogZmianaRodzajKey read FRodzajId write FRodzajId;
      /// <summary>
      /// Identyfikator informacji o zmianie w tabeli bazy danych.
      /// </summary>
    property ZmianaId: TBkLogTabelaKey read FZmianaId write FZmianaId;
  end;

this class is mapped to the table:

create table U_BTK.T_LOG_ZMIANY
(
  lz_id     INTEGER not null,   -- mapped to ID
  lz_lt_id  INTEGER not null,  -- mapped to ZmianaId
  lz_lzr_id INTEGER not null,  -- mapped to RodzajId
);

When I try to execute the code:

  Client.Delete(TBkLogZmiana, FormatUTF8('%<? and %=?', [ZmianaId, RodzajId], [SomeValue, 1]));

in log file is generated error:

20180927 08495920  - SQL   				SynDBOracle.TSQLDBOracleStatement(01BD2780) delete from U_BTK.T_LOG_ZMIANY WHERE ZmianaId<32569 and RodzajId=1
20180927 08495920  - EXC   				ESQLDBOracle {"Statement":{"SQL":"delete from U_BTK.T_LOG_ZMIANY WHERE ZmianaId<? and RodzajId=?","SQLWithInlinedParams":"delete from U_BTK.T_LOG_ZMIANY WHERE ZmianaId<32569 and RodzajId=1","CurrentRow":0,"TotalRowsRetrieved":0,"Connection":{"Connected":true,"ServerTimeStampAtConnection":"2018-09-27T09:59:45","TotalConnectionCount":1,"TransactionCount":1,"InTransaction":true,"RollbackOnDisconnect":true,"LastErrorMessage":"","LastErrorWasAboutConnection":false,"Properties":{"ClientVersion":"oci.dll rev. 11.2.0.1","EnvironmentInitializationMode":7,"InternalBufferSize":131072,"RowsPrefetchSize":131072,"BlobPrefetchSize":4096,"StatementCacheSize":30,"UseWallet":false,"IgnoreORA01453OnStartTransaction":false,"Engine":"Oracle","ServerName":"GIDP2","DatabaseNameSafe":"","UserID":"bk_app_server","DBMS":"dOracle","DBMSEngineName":"Oracle","BatchSendingAbilities":["cCreate","cUpdate","cDelete"],"BatchMaxSentAtOnce":10000,"LoggedSQLMaxSize":0,"LogSQLStatementOnException":false,"ForcedSchemaName":"","UseCache":true,"RollbackOnDisconnect":true,"StoreVoidStringAsNull":false}},"StripSemicolon":true},"Message":"TSQLDBOracleStatement error: ORA-00904: \"RODZAJID\": niepoprawny identyfikator"} at 00FBEF99  stack trace API 00DBF05F 5005FF40

it looks like one part of the SQL query was translated to SQL and second part (where clause section) was not translated and this made error. I tried to execute the code in the following way:

  Client.Delete(TBkLogZmiana, FormatUTF8('%<? and %=?', ['lz_lt_id', 'lz_lzr_id'], [SomeValue, 1]));

but this change doesn't works. mORMot can't to read IDs of deleted record to notification mechanism and reports following error:

08:38:25	Warning	11	             mORMotDB.TSQLRestStorageExternal(78B68410) TSQLRestStorageExternal.AdaptSQLForEngineList: statement too complex -> would use SQLite3 virtual engine [SELECT RowID FROM BkLogZmiana WHERE LZ_LT_ID<:(83446693): and LZ_LZR_ID=:(1):]
08:38:25	Exception	11	             ESQLite3Exception {"ErrorCode":1,"SQLite3ErrorCode":"secERROR","Message":"Error SQLITE_ERROR (1) [SELECT RowID FROM BkLogZmiana WHERE LZ_LT_ID<? and LZ_LZR_ID=?] using 3.19.2 - no such column: LZ_LT_ID, extended_errcode=1"} at 01400CEA  stack trace API 0130395B 01303984 5005FF40 
08:38:25	Result	11	             {"TSQLDatabase(78FEB920)":{"FileName":":memory:","IsMemory":true,"UseCache":false,"TransactionActive":true,"BusyTimeout":0,"CacheSize":-2000,"PageSize":4096,"PageCount":19,"FileSize":77824,"WALMode":false,"Synchronous":"smFull","LockingMode":"lmNormal","MemoryMappedMB":0,"user_version":0,"OpenV2Flags":6,"BackupBackgroundInProcess":false,"BackupBackgroundLastTime":"","BackupBackgroundLastFileName":"","SQLite3Library":{"TSQLite3LibraryStatic(7F951850)":{"Version":"3.19.2 with internal MM"}}}}
08:38:25	Debug	11	             mORMotSQLite3.TSQLRestServerDB(78F41E90) TSQLRestRoutingREST.Error: {  "errorCode":400,  "errorText":"Bad Request"  }

I'm using mORMot revision 4235 (from SVN repository) from 5.September.2017 10:24:13.

ab · 2018-09-27 17:51:01

The field mapping translation doesn't work with such complex delete IIRC.

mORMot Open Source

#1 2018-09-27 09:55:41

Problem with deleting record in Oracle

#2 2018-09-27 17:51:01

Re: Problem with deleting record in Oracle

Board footer