about mORMot.TSQLRestClientURI.ExecuteList

edismo · 2013-10-04 18:29:33

Hi,

I'm having problems with mORMot.TSQLRestClientURI.ExecuteList.
I use the version 1.18.
see...

Simple example code...

Database information

  ConnectionProperties := TSQLDBSQLite3ConnectionProperties.Create('.\data\sample.db3','','','');
  VirtualTableExternalRegisterAll(MyModel, ConnectionProperties);
  inherited Create(MyModel, nil, ':memory:', TSQLRestServerDB);
  Server.CreateMissingTables(0);

Tables

TCar class=(TSQLRecord)
private
  fColor: RawUTF8;
published
  property   Color: RawUTF8 read fColor write fColor;
end;

TPerson class=(TSQLRecord)
private
  fFirstName: RawUTF8;
  fCar: TCar;
published
  FirstName: RawUTF8 read fFirstName write fFirstName;
  Car: TCar read fCar write fCar;
end;

Data for tables
Table Person
ID|FirstName|Car|
1|Chuck|1|

Table Car
ID|Color|
1|Red|

load data Person/Car

SQLTableJSON := DatabaseServer.ExecuteList([TPerson, TCar],'select Person.FirstName, Car.Color from Person, Car');

it´s OK,
in SynSQLite3.TSQLRequest.Execute step return SQLITE_DONE
in mORMotDB.TSQLVirtualTableCursorExternal.Search (fStatement <> nill) is false

But...

// Multiples data for tables
Person table
ID|FirstName|Car|
1|Chuck|1|
2|Zinedine|2|

Car table
ID|Color|
1|Red|
2|Black|

Excpetion

SQLTableJSON := DatabaseServer.ExecuteList([TPerson, TCar],'select Person.FirstName, Car.Color from Person, Car where Person.Car = Car.ID');

Exception occurs

in sqlite3.prepare_v2 return SQLITE_ERROR, then SynSQLite3.sqlite3_check raise
Message is: 'no such column: Car.Id'

Excpetion - remove condition where:

SQLTableJSON := DatabaseServer.ExecuteList([TPerson, TCar],'select Person.FirstName, Car.Color from Person, Car');

then...
in SynSQLite3.TSQLRequest.Execute step return SQLITE_ERROR
in mORMotDB.TSQLVirtualTableCursorExternal.Search (fStatement <> nill) is true
Message is: 'SQL logic error or missing database'

Changing for:

SQLTableJSON := DatabaseServer.ExecuteList([TPerson, TCar],'select Person.FirstName from Person');

Even including TCar and multiple records, but only from Person. Works.

Can you help me?That should work or not is the correct way?

ab · 2013-10-04 18:46:22

Try to use RowID instead of ID when using virtual tables in SQLite3.

Or, try to make direct call the external DB, via the TSynDBProperties instance.
Since you are by passing the whole ORM feature here, you can do this, when you expect to run SQL statements.

edismo · 2013-10-04 19:20:37

Thanks AB,

I used RowID returned the same message: "SQL logic error or missing database '

  SQLTableJSON := DatabaseServer.ExecuteList([TPerson, TCar],'select Person.FirstName, Car.Color from Person, Car where Person.Car = Car.RowID');

TSynDBProperties not found in documentation.
Found TSQLDBSQLite3ConnectionProperties = class (TSQLDBConnectionProperties), is this?

I not found method that returns TSQLTableJSON.
What would be the correct one to use?

ab · 2013-10-05 06:45:17

See sample "16 - Execute SQL via services":

function TServiceRemoteSQL.Execute(const aSQL: RawUTF8; aExpectResults, aExpanded: Boolean): RawJSON;
var res: ISQLDBRows;
begin
  if fProps=nil then
    raise Exception.Create('Connect call required before Execute');
  res := fProps.ExecuteInlined(aSQL,aExpectResults);
  if res=nil then
    result := '' else
    result := res.FetchAllAsJSON(aExpanded);
end;

So FetchAllAsJSON() will return a JSON array as RawUTF8 which can be used to fill a TSQLTableJSON.

Note that you are still think "RDBMS", not ORM/data sharding.
Your data model could be defined in a more DDDish way, as an aggregate root in which the Car is part of the Person definition.

edismo · 2013-10-07 12:15:24

Thanks AB,

It worked!

This would be the best option?

var
   Person: TPerson;
   Car: TCar;
begin
   Person: = TPerson.CreateAndFillPrepare (DatabaseServer,'');
   Person.FillOne;
   EdtFirstName.Text: = Person.FirstName;
   EdtLastName.Text: = Person.LastName;
   Car: TCar.Create = (DatabaseServer, Person.Car);
   EdtCarColor.Text: = Car.Color;

ab · 2013-10-07 12:55:12

Yes, I think so.

You can take a look at auto-generated UI forms.
See for instance how SynDBExplorer edits an TSQLConnection record - see TRecordEditForm() in unit mORMotUIEdit.
It is able to lookup a sub field from its values (e.g. put TCar values in a combobox from a TPerson edition form).
But it will probably not be enough for you.

edismo · 2013-10-07 13:33:18

thanks for help.

mORMot Open Source

#1 2013-10-04 18:29:33

about mORMot.TSQLRestClientURI.ExecuteList

#2 2013-10-04 18:46:22

Re: about mORMot.TSQLRestClientURI.ExecuteList

#3 2013-10-04 19:20:37

Re: about mORMot.TSQLRestClientURI.ExecuteList

#4 2013-10-05 06:45:17

Re: about mORMot.TSQLRestClientURI.ExecuteList

#5 2013-10-07 12:15:24

Re: about mORMot.TSQLRestClientURI.ExecuteList

#6 2013-10-07 12:55:12

Re: about mORMot.TSQLRestClientURI.ExecuteList

#7 2013-10-07 13:33:18

Re: about mORMot.TSQLRestClientURI.ExecuteList

Board footer