mORMot Open Source

mhmda

Member

Registered: 2017-03-01

Posts: 19

Execute SQL query

When I use this code to retrieve joined tables I got an error that 'contacts.id' unknown column.

So I can't declare the id of [TContacts] because it already managed internally by mORMot framework.

How could I execute this:

procedure TDB.getContactsA(Ctxt: TSQLRestServerURIContext);
var
    sqlResult:Rawjson;
    sqlText:RawUTF8;
begin
  sqlText:='SELECT cname,bookname FROM contacts LEFT JOIN books ON books.contactsid=contacts.id';
  sqlResult:=ExecuteJson([],sqlText);
  Ctxt.Results([sqlResult]);
end;

Thank you in advance

Last edited by mhmda (2017-08-26 16:00:10)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: Execute SQL query

Writing manual SQL is not the most easiest way to do it.
See https://synopse.info/files/html/Synopse … #TITLE_123

What do you mean by " I can't declare the id of [TContacts] " ?
There is an ID field there.

mhmda

Member

Registered: 2017-03-01

Posts: 19

Re: Execute SQL query

True,  it's there how to add it to the query?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: Execute SQL query

I don't understand what you meant, sorry.

What is the problem with your SQL query?

mhmda

Member

Registered: 2017-03-01

Posts: 19

Re: Execute SQL query

Ok I'll explain I have two TSQLRecords that represent my two tables like this:

TContacts = class(TSQLRecord)
    private
      fCname:  RawUTF8;
      fCemail: RawUTF8;
      fCcel:   RawUTF8;
      fCtel:   RawUTF8;
    published
      property Cname: RawUTF8   read fCname write fCname;
      property Cemail: RawUTF8  read fCemail write fCemail;
      property Ccel: RawUTF8    read fCcel write fCcel;
      property Ctel: RawUTF8    read fCtel write fCtel;
 end;
 Tbooks    = class(TSQLRecord)
   private
    fcontactsid: TID;
    fbookname: RawUTF8;
    fbookyear: TDate;
   published
    property Bookname:RawUTF8 read fBookname write fBookname;
    property contactsid:TID read fcontactsid write fcontactsid;
    property bookyear:TDate read fbookyear write fbookyear;
  end;

I want to call a method from client side: let us call it 'getContactsA' :

procedure TDB.getContactsA(Ctxt: TSQLRestServerURIContext);
var
    sqlResult:Rawjson;
    sqlText:RawUTF8;
begin
  sqlText:='SELECT cname,bookname FROM contacts LEFT JOIN books ON books.contactsid=contacts.id';
  sqlResult:=ExecuteJson([],sqlText);
  Ctxt.Results([sqlResult]);
end;

So from browser I call like this: http://localhost:8080/service/getContactsA

Then it executed in server side and I get this error:

Last edited by mhmda (2017-08-26 18:02:32)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: Execute SQL query

I guess the DB initialization in the ORM was not correct.

You are using MySQL external storage, am I correct?
Anyway, you are trying to run the query on SQLite3, and it is not a good idea, since there are no true SQLite3 tables, only virtual tables mapping to the external DB.
So your "complex" query has issues executing it.

If you want to use the SQL, use directly the SynDBZeos layer, and your SQL returning the JSON.
If you want to use the ORM, don't write manual SQL, but use the documented ORM methods.

mhmda

Member

Registered: 2017-03-01

Posts: 19

Re: Execute SQL query

Yes I use mySQL.

Thanx I'll try that.

turrican

Member

From: Barcelona

Registered: 2015-06-05

Posts: 94

Website

Re: Execute SQL query

Sorry but this is not the way to use the ORM, avoid complex queries like JOIN and more.