mORMot Open Source

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Execute complex SQL with join

Hello gurus,

I have to execute not very complex custom SQL but it looks like not very obvious for mORMot.
Please show me the way

function TfrmTransform.Initialize: TfrmDocked;
var
  tab: TSQLTable;
  sql: String;
begin
  Result := inherited;
    sql := 'SELECT  transfer.id_transfer, id_user_sender, id_user_reciever, SendDateTime, RecieveDate, MessageState, MessageFormat, PatientId, PatientFirstName, PatientSecondName, PatientBirthday FROM ' +
      'transfer ' +
      'LEFT JOIN ' +
      '( ' +
      'SELECT id_transfer, PatientId, PatientFirstName, PatientSecondName, PatientBirthday FROM ommessageopendetailsform ' +
      'UNION ' +
      'SELECT id_transfer, PatientId, PatientFirstName, PatientSecondName, PatientBirthday FROM ommessageopendetailsinstant ' +
      'UNION ' +
      'SELECT id_transfer, PatientId, PatientFirstName, PatientSecondName, PatientBirthday FROM ommessageopendetailsjpg ' +
      'UNION ' +
      'SELECT id_transfer, PatientId, PatientFirstName, PatientSecondName, PatientBirthday FROM ommessageopendetailspdf ' +
      'UNION ' +
      'SELECT id_transfer, PatientId, PatientFirstName, PatientSecondName, PatientBirthday FROM ommessageopendetailsmail ' +
      ') AS opendetails ' +
      'ON id_transfer=opendetails.id_transfer';

  tab := FConnectionManager.Client.ExecuteList([], sql);
  cds := TSQLTableToClientDataSet(self, tab);
  BindSourceDB.Dataset := cds;
  tab.Free;
end;

on server I has an error:

20141114 12031343 EXC           ESQLite3Exception ("Error SQLITE_ERROR (1) - \"no such column: transfer.id_transfer\"") at 00563882  stack trace 0053B79C 0053D9BC 0053CE75 005403B4 0040491E 00407554 7702F8B1 66DF1911 0040491E 00407554 004075BB 005FD485 005F5CEF 005FB0D0 00446F4D 004073CA 75623677 77049F42 77049F15

on server I have MySQL and I thought the SQL will be executed directly by MySQL...
What should I do?

Thanks a lot

mpv

Member

From: Ukraine

Registered: 2012-03-24

Posts: 1,571

Website

Re: Execute complex SQL with join

To get good call stack instead of  " stack trace 0053B79C 0053D9BC " please, enable map file creation (Options-> Compiling->Symbol reference info). After this we can see what type of connection actually used.

General SQL suggestion - are you sure you need UNION in your case. Looks like UNION ALL must be  used (this is not a source of exception, but huge slow down of query). And in all case it is better to split such query to 2 part.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: Execute complex SQL with join

thanks for UNION ALL tip.

will try to provide MAP info little later.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Execute complex SQL with join

Did you try to run the query in SynDBExplorer?

All this is raw SQL.
If the SQL is correct, the SynDB class would not have any problem about it.
I suggest you fix your SQL.

Or change your data model: such multiple tables with UNION on similar tables is IMHO not a good design smell.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: Execute complex SQL with join

Sure I checked all SQLs... not in SynDBExplorer but in other Mysql manager (SQLyog).
SQL is quite correct and runs good.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: Execute complex SQL with join

I use on server UniDAC connection properties to MySQL.
Now I made new form with Unidac Connection and Unidac Query... My SQL query works fine on the form.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Execute complex SQL with join

You are using the REST server as a proxy for your SQL queries.
This is not how it is supposed to work!

You have to understand that the mORMot REST server is not a proxy, but a REST server. You should use it to access resources as REST.
The Client.EngineList() method works by chance for simple queries, because you enable it, but it is NOT to be used in production.
The SQL you specify to EngineList() is NOT transmitted directly to the MySQL server. It is first translated by the SQLite3 kernel, which do not understand your statement, as the error states.
So I suspect it could not work directly, since it is NOT supposed to work like this.

IMHO I would change the data layout, to fit more the ORM style, using classes, high level types and un-normalization.
Please change your mind from the RDBMS point of view!

But if you want to directly access the MySQL server, and run such queries, what you should do is create a service - either a method-based service, or an interface-based service - which will run the query on the mORMot server side, then return the content as JSON.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: Execute complex SQL with join

Yes, I see.
What I need now is fast solution to fill a grid with some data.
As I understand main problem of me now is SQLite does not know about ID field an can not allow to join other tables via the ID.

It is too slow way to select joined data in a delphi loop for example... So words about flexible custom SQL queries in mORMot Framework is little bit exaggerated.

Have we simple way to get a workaround with ID field in SQL?

Your offer with web-service is clever and good but too complex for my simple task.

Thanks a lot again for your help.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Execute complex SQL with join

The framework is mainly an ORM, so it generates most of the SQL for you, and you can still use your own SQL if needed.

The service is not difficult to implement.
See the following sample:
https://github.com/synopse/mORMot/tree/ … 20services
This is what you need: execute SQL on the server, then put it in a Grid.
You can cut it down to handle only your MySQL database.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: Execute complex SQL with join

ok, will try.

next question...

tab: TSQLTable;
tab := FConnectionManager.Client.ExecuteList([], sql);

result table contains information about ID so we can convert it to objectlist and so on.

can I get ID column in result clientdataset after

cds := TSQLTableToClientDataSet(self, tab);

?

Thanks.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: Execute complex SQL with join

Just like with any TDataSet.
http://docwiki.embarcadero.com/Librarie … B.TDataSet
Search for the 'ID' field name.

But you should better rely on the tab: TSQLTable, which should be faster and more accurate.
For a TSQLTable, you can use FieldIndex() or directly the Get*() methods.