You are not logged in.
Pages: 1
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?
Offline
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.
Offline
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?
Offline
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.
Offline
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;
Offline
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.
Offline
thanks for help.
Offline
Pages: 1