#1 2017-08-26 15:55:46

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)

Offline

#2 2017-08-26 16:04:45

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
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.

Offline

#3 2017-08-26 16:21:08

mhmda
Member
Registered: 2017-03-01
Posts: 19

Re: Execute SQL query

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

Offline

#4 2017-08-26 17:40:43

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
Website

Re: Execute SQL query

I don't understand what you meant, sorry.

What is the problem with your SQL query?

Offline

#5 2017-08-26 17:59:17

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:

mo.png

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:

m.png

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

Offline

#6 2017-08-26 18:11:19

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,659
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.

Offline

#7 2017-08-26 18:13:52

mhmda
Member
Registered: 2017-03-01
Posts: 19

Re: Execute SQL query

Yes I use mySQL.

Thanx I'll try that.

Offline

#8 2017-08-28 09:28:33

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.

Offline

Board footer

Powered by FluxBB