#1 mORMot 1 » TSQLRest.MemberExists fails in PostgreSQL » 2020-05-17 19:23:45

israel_jbl
Replies: 0

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.

#2 mORMot 1 » RTREE concurrence error » 2020-02-10 19:25:11

israel_jbl
Replies: 1

I got this error:

20200210 12185508 EXC   EModelException {"Message":"TSQLMapBox has 7 fields: RTREE expects 2,4,6..10 boundary columns"} [] at
6c7774 mORMot.TSQLModelRecordProperties.SetKind (33997)  stack trace API
6c7774 mORMot.TSQLModelRecordProperties.SetKind (33997)
6c7021 mORMot.TSQLModelRecordProperties.Create (33900)
6c7f96 mORMot.TSQLModel.SetTableProps (34106)
6c901e mORMot.TSQLModel.Create (34310)

I have two RTREE tables in the same model:

  TSQLMapBox = class(TSQLRecordRTree)
  private
    FLat1: Double;
    FLon1: Double;
    FLat2: Double;
    FLon2: Double;
  published
    property Lat1: Double read FLat1 write FLat1;
    property Lon1: Double read FLon1 write FLon1;
    property Lat2: Double read FLat2 write FLat2;
    property Lon2: Double read FLon2 write FLon2;
  end;

  TSQLAnimalMap = class(TSQLRecordRTree)
  private
    FLat1: Double;
    FLon1: Double;
    FLat2: Double;
    FLon2: Double;
  published
    property Lat1: Double read FLat1 write FLat1;
    property Lon1: Double read FLon1 write FLon1;
    property Lat2: Double read FLat2 write FLat2;
    property Lon2: Double read FLon2 write FLon2;
  end;

Only happens when the model is created in concurrence (several calls to my webservice), using SOA.
It happens with both tables.
The quantity of fields change in the error message "...has 7 fields: RTREE expects...", someties it shows 5 instead of 7.

Any ideas?.

Thanks in advance.

#4 mORMot 1 » Problem concatenating strings in where clause » 2019-11-03 18:00:40

israel_jbl
Replies: 2

I got this error:

FMessage 'Error SQLITE_ERROR (1) [select ID,FirstName,LastName,ZoneName,BirthDate  from userx where FirstName||" "||LastName like "%AAA%"] using 3.30.1 - no such column: ID, extended_errcode=1'

Without concatenate in where clause it works fine.

All my tables are external with PostgreSQL 11.

Using TSQLRestServerDB, the query is executed in server side with ExecuteList method.

TSQLUserX = class(TSQLAuthUser)
  private
    FFirstName: RawUTF8;
    FLastName: RawUTF8;
    FBirthDate: TDateTime;
    FSex: TSQLSex;
    FEMail: RawUTF8;
    ...

Thanks in advance. You do an amazing job.

#5 mORMot 1 » How handle self referencing table » 2019-10-02 00:53:14

israel_jbl
Replies: 1

I have this PODO:

  TDomZone = class(TSynPersistent)
  private
    FParentZone: TDomZone;
    FZoneType: RawUTF8;
    FCode: RawUTF8;
    FISOCode: RawUTF8;
    FName: RawUTF8;
    FLatitude: Double;
    FLongitude: Double;
    FAltitude: Double;
  published
    property ParentZone: TDomZone read FParentZone write FParentZone;
    property &ZoneType: RawUTF8 read FZoneType write FZoneType;
    property Code: RawUTF8 read FCode write FCode;
    property ISOCode: RawUTF8 read FISOCode write FISOCode;
    property Name: RawUTF8 read FName write FName;
    property Latitude: Double read FLatitude write FLatitude;
    property Longitude: Double read FLongitude write FLongitude;
    property Altitude: Double read FAltitude write FAltitude;
  end;

  TSQLZone = class(TSQLRecord)
  ...
  published
     ParentZone: TSQLZone;
  ...
  end;

...Interfaces...
...Repository...
...Factory...

TInterfaceFactory.RegisterInterfaces([TypeInfo(IDomZoneQuery), TypeInfo(IDomZoneCommand)]);

Self referencing attribute ParentZone. I'm using SOA. The framework throw Access Violation in some where else, I don't know why, but if I comment the RegisterInterfaces line, it works.

Stacktrace:

mORMot.JSONObjectFromClass(TClass($445F4244),$315BCC8)
mORMot.TJSONSerializer.WriteObject($323A044,[woDontStoreDefault])
mORMot.WriteProp($849C84)
mORMot.WritePropsFromRTTI(TClass($849B64))
mORMot.TJSONSerializer.WriteObject($32555E8,[woDontStoreDefault])
mORMot.TServiceMethodArgument.AddJSON($326B450,$3228C38,[woDontStoreDefault])
mORMot.TServiceMethodExecute.ExecuteJson((...),'[]',$326B450,$5BDF6A0 {''},False)
mORMot.TServiceFactoryServer.ExecuteMethod($320C460)
mORMot.ComputeResult
mORMot.TSQLRestServerURIContext.InternalExecuteSOAByInterface
mORMot.TSQLRestRoutingREST.ExecuteSOAByInterface
mORMot.TSQLRestServerURIContext.ExecuteCommand
mORMot.TSQLRestServer.URI($5BDFBE4)
mORMotHttpServer.TSQLHttpServer.Request($3246AC8)
SynCrtSock.THttpServerGeneric.Request($3246AC8)
SynCrtSock.THttpApiServer.Execute
System.Classes.ThreadProc($3786050)
System.ThreadWrapper($3228CF0)
:75370419 KERNEL32.BaseThreadInitThunk + 0x19
:7763662d ntdll.RtlGetAppContainerNamedObjectPath + 0xed
:776365fd ntdll.RtlGetAppContainerNamedObjectPath + 0xbd


How I should handle self referencing tables?

I love  your framework, crazy it is free, thanks!

Board footer

Powered by FluxBB