mORMot Open Source

israel_jbl

Member

Registered: 2019-08-29

Posts: 5

TSQLRest.MemberExists fails in PostgreSQL

Error: column «rowid» does not exists

Log:

20200517 13514453 EXC   EODBCException {"Statement":{"SQL":"SELECT RowID FROM AnimalMap WHERE RowID=? LIMIT 1","SQLWithInlinedParams":"SELECT RowID FROM AnimalMap WHERE RowID=588 LIMIT 1","CurrentRow":0,"TotalRowsRetrieved":0,"Connection":{"DBMSName":"PostgreSQL","DBMSVersion":"11.0.2","DriverName":"PSQLODBC35W.DLL","Connected":true,"ServerTimestampAtConnection":"2020-05-17T13:51:44","TotalConnectionCount":1,"TransactionCount":0,"InTransaction":false,"RollbackOnDisconnect":true,"LastErrorMessage":"","LastErrorWasAboutConnection":false,"Properties":{"Engine":"ODBC","ServerName":"","DatabaseNameSafe":"Driver=PostgreSQL Unicode;Database=peti;Server=localhost;Port=5432;UID=postgres;Pwd=***","UserID":"POSTGRES","DBMS":"dPostgreSQL","DBMSEngineName":"PostgreSQL","BatchSendingAbilities":["cCreate"],"BatchMaxSentAtOnce":4096,"LoggedSQLMaxSize":2048,"LogSQLStatementOnException":false,"ForcedSchemaName":"public","FilterTableViewSchemaName":false,"UseCache":true,"StatementCacheReplicates":0,"RollbackOnDisconnect":true,"StoreVoidStringAsNull":false,"ExecuteWhenConnected":[]}},"StripSemicolon":true},"Message":"TODBCStatement - TODBCLib error: [42703] ERROR: no existe la columna «rowid»;\nError while preparing parameters (1)\r\n"} [] at 8dc3a6 SynDBODBC.TODBCLib.HandleError (1990)  stack trace API 8dc3a6 SynDBODBC.TODBCLib.HandleError (1990) 8db67d SynDBODBC.TODBCStatement.ExecutePrepared (1799) 5b19be SynDB.TSQLDBStatement.ExecutePreparedAndFetchAllAsJSON (7162) 6aa480 mORMotDB.TSQLRestStorageExternal.EngineList (1308) 651f8e mORMot.TSQLRest.ExecuteList (35681) 64e9bb mORMot.TSQLRest.MultiFieldValue (34707) 64db61 mORMot.TSQLRest.OneFieldValue (34458) 64dc4c mORMot.TSQLRest.MemberExists (34468) 65d4e1 mORMot.TSQLRestServer.MemberExists (39038) a268ea Peti.backend.Dom.Animal.backend.Dom.Animal.TDomAnimal.AddOrUpdateMap (280) a2880e Peti.backend.Dom.Animal.backend.Dom.Animal.TDomAnimal.Update (900) 69a4a3 mORMot.CallMethod (57946) 40c838 System.ThreadWrapper (24896) 6a27f7 mORMot.TServiceMethodExecute.ExecuteJson (60594) 69c7ba mORMot.TServiceFactoryServer.ExecuteMethod (58576) 660b6b mORMot.ComputeResult (39937) 660f4c mORMot.TSQLRestServerURIContext.InternalExecuteSOAByInterface (39972) 6669ce mORMot.TSQLRestRoutingREST.ExecuteSOAByInterface (41205) 65fa10 mORMot.TSQLRestServerURIContext.ExecuteCommand (39692) 66747b mORMot.TSQLRestServer.URI (41431) 8ced23 mORMotHttpServer.TSQLHttpServer.Request (938) 6c7f41 SynCrtSock.THttpServerGeneric.Request (6047) 6cfc26 SynCrtSock.THttpApiServer.Execute (9287) 4d98e8 System.Classes.ThreadProc (15367) 40c862 System.ThreadWrapper (24944)

With this changes in mORMot.pas it works:

function TSQLRest.OneFieldValue(Table: TSQLRecordClass;
  const FieldName: RawUTF8; WhereID: TID): RawUTF8;
var Res: array[0..0] of RawUTF8;
begin
  if (WhereID>0) and
     MultiFieldValue(Table,[FieldName],Res,'ID=:('+Int64ToUtf8(WhereID)+'):') then ---------------> this line had 'RowID=', changed by 'ID='
    result := Res[0] else
    result := '';
end;

function TSQLRest.MemberExists(Table: TSQLRecordClass; ID: TID): boolean;
begin
  if fCache.Retrieve(Model.GetTableIndexExisting(Table),ID)<>'' then
    result := true else
    result := OneFieldValue(Table,'ID',ID)<>''; // try from DB ---------------> this line had 'RowID' in 2nd parameter, changed by 'ID'
end;

Any suggestions?

Thanks in advance for your great job.