mORMot Open Source

damiand

Member

From: Greece

Registered: 2018-09-21

Posts: 94

CreateAndFillPrepareJoined fails when ORM table gets 2nd row

I got two ORM classes joined, TDoctor and TLocArea:

TDoctor = class(TOrm)
  private
    fFirstName, fSurName: RawUTF8;
    fLocationArea: TLocArea;
    fMISId, fSAPId: TNullableUtf8Text;
  published
    property FirstName: RawUTF8 index 50 read fFirstName write fFirstName;
    property SurName: RawUTF8 index 50 read fSurName write fSurName;
    property LocationArea: TLocArea read fLocationArea write fLocationArea;
    property MISId: TNullableUtf8Text index 20 read fMISId write fMISId;
    property SAPId: TNullableUtf8Text index 20 read fSAPId write fSAPId;
end;

TLocArea = class(TOrm)
  private
    fArea, fNs: RawUTF8;
  published
    property Area: RawUTF8 index 50 read fArea write fArea;
    property Ns: RawUTF8 index 10 read fNs write fNs;
end;

The actual tables are implemented in MS SQL Server, as virtual tables through SQLite. The connection is via ODBC. In the client application the data is retrieved using the CreateAndFillPrepareJoined method.

 doctor := TDoctor.CreateAndFillPrepareJoined(restClient.Orm, '', [], []);

I am able to create some TLocArea objects and a TDoctor object, assign its properties and store it in the DB. I retrieve back the instance with CreateAndFillPrepareJoined with no problem. When I add a second TDoctor object it is persisted in the DB, however the CreateAndFillPrepareJoined returns an empty set, i.e. calling:

doctor.FillOne

returns false and no objects can be retrieved, although they are already stored in the DB tables. Activating logging in the mormot server traces the following related lines:

20230705 04572622 DB    mormot.db.sql.odbc.TSqlDBOdbcStatement(02b02e50) Prepare t=10.59ms q=select FirstName,SurName,LocationArea,MISId,SAPId,ID from dbo.Doctor
20230705 04572622 SQL   mormot.db.sql.odbc.TSqlDBOdbcStatement(02b02e50) Execute t=10.87ms q=
20230705 04572622 SQL   mormot.orm.sql.TOrmVirtualTableCursorExternal(02ab0680) Search select FirstName,SurName,LocationArea,MISId,SAPId,ID from dbo.Doctor
20230705 04572622 DB    mormot.db.sql.odbc.TSqlDBOdbcStatement(02b03030) Prepare t=59us q=select Area,Ns,ID from dbo.LocArea where ID=?
20230705 04572622 warn  vt_Filter Search()
20230705 04572622 ERROR mormot.rest.sqlite3.TRestServerDB(023d4d40) {"ESqlite3Exception(02507d38)":{Message:"Error SQLITE_ERROR (1) [Step] using 3.41.0 - SQL logic error",ErrorCode:1,SQLite3ErrorCode:"secERROR"}} for SELECT Doctor.RowID as `Doctor.RowID`,Doctor.FirstName as `Doctor.FirstName`,Doctor.SurName as `Doctor.SurName`,Doctor.MISId as `Doctor.MISId`,Doctor.SAPId as `Doctor.SAPId`,LocationArea.RowID as `LocationArea.RowID`,LocationArea.Area as `LocationArea.Area`,LocationArea.Ns as `LocationArea.Ns` FROM Doctor LEFT JOIN LocArea AS LocationArea ON Doctor.LocationArea=LocationArea.RowID //  

The profiler at the MS SQL server side traces only the first Prepare statement from the lines above.
Last, when I try to retrieve the TDoctor objects with no joins, e.g. by using the CreateAndFillPrepare method, I can get the two stored rows with no problem. Any help is very much appreciated.

Last edited by damiand (2023-07-05 05:26:47)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: CreateAndFillPrepareJoined fails when ORM table gets 2nd row

You need to use TOrmMany kind of tables to use the ORM join features.

damiand

Member

From: Greece

Registered: 2018-09-21

Posts: 94

Re: CreateAndFillPrepareJoined fails when ORM table gets 2nd row

But, it is a many (TDoctor) to one (TLocArea) relation, not a m:n. So far I haven't seen in the documentation (either in 1.18 or in 2.0) that a TOrmMany base class is needed for such relationship.

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: CreateAndFillPrepareJoined fails when ORM table gets 2nd row

But, it is a many (TDoctor) to one (TLocArea) relation, not a m:n.

In this case, I would write it as follows:

type
  TLocationArea = class(TOrm)
  ...

  TLocationAreaID = type TID;

  TDoctor = class(TOrm)
  ...
  published
    property LocationAreaID: TLocationAreaID
      read fLocationAreaID write fLocationAreaID;

var
  dataArr: TArray<TDoctor>;
begin
  ...RetrieveListObjArray(dataArr, TDoctor, 'LocationAreaID=?', [1234]);

Or if no automatism is desired, then like this: "property LocationAreaID: TID".

With best regards
Thomas

damiand

Member

From: Greece

Registered: 2018-09-21

Posts: 94

Re: CreateAndFillPrepareJoined fails when ORM table gets 2nd row

Thanks Thomas. I need to feed a TOrmTableToGrid object via the FillTable method, i.e. my goal is to fill a TOrmTable object with the TDoctor instances, with the joined objects expanded. If CreateAndFillPrepareJoined method worked as I expected, then it would be very easy, just by calling the FillTable method of the TOrm object. Now I have to find an alternative approach.

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: CreateAndFillPrepareJoined fails when ORM table gets 2nd row

Thanks Thomas. I need to feed a TOrmTableToGrid object via the FillTable method, i.e. my goal is to fill a TOrmTable object with the TDoctor instances, with the joined objects expanded. If CreateAndFillPrepareJoined method worked as I expected, then it would be very easy, just by calling the FillTable method of the TOrm object. Now I have to find an alternative approach.

I haven't used this class myself yet, but if I guess correctly the TOrmTableToGrid event OnValueText allows to manipulate the output. You could load the TDoctor class as TOrmTable and secondly a lookup list with the TLocationArea class. Then for each row in the grid, compose the output for the location. I do it in a similar way, only with my own grid precenter. This is fast, implemented in a few lines and mORMot has all the functions on board for this.

With best regards
Thomas

damiand

Member

From: Greece

Registered: 2018-09-21

Posts: 94

Re: CreateAndFillPrepareJoined fails when ORM table gets 2nd row

Thanks Thomas for one more time. My approach at the end was to create a service which returns the result of the joined SQL query directly from the MS SQL server, bypassing SQLite:

function TInsPump.ListDoctors: RawUTF8;
var
  st: TSqlDBOdbcStatement;
begin
  Result := '';
  st := TSqlDBOdbcStatement.Create(dbConn);
  try
    st.Prepare(StringToUtf8(c_SQL_List_Doctors), true);
    st.ExecutePreparedAndFetchAllAsJson(false, Result);    
  finally
    st.Free
  end;
end;

At the client side, a TOrmTableJson is used to get the json and feed the TOrmTableToGrid with that:

  restClient.Services['InsPump'].Get(insPump);
  table := TOrmTableJson.Create('',insPump.ListDoctors);
  table.OwnerMustFree := false;
  OrmTableToGrid := TOrmTableToGrid.Create(DrawGrid1, table, restClient);
  OrmTableToGrid.OnValueText := OnValueText;

A (final) question, because the documentation is not informant on this point: The first argument of the TOrmTableJson constructor, aSQL holds a comma separated list of column types? If yes, in what syntax, Delphi?

tbo

Member

Registered: 2015-04-20

Posts: 353

Re: CreateAndFillPrepareJoined fails when ORM table gets 2nd row

A (final) question, because the documentation is not informant on this point: The first argument of the TOrmTableJson constructor, aSQL holds a comma separated list of column types? If yes, in what syntax, Delphi?

The documentation for QuerySql says: "contains the associated SQL statement on Query". If you search the source code with grep, it is only used in this sense. Usually an OrmTable is initialized like this: "table := TOrmTableJson.Create('', ...". For more information, another forum user or Arnaud will have to answer. Interestingly, when you do a grep, you always find new things that you haven't noticed before.

With best regards
Thomas