mORMot Open Source

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

zeos-mysql + mormot as sql proxy

Hello,

I know that mormot was not designed as sql proxy.

But maybe there is a way...

I have mysql database and mormot connected via zeos to the database.
Everything works fine but now I need to execute complex SQL and I get as error.

I tryed to execute the SQL via SynDBExplorer and it works fine.
So I have a hope.

connection to mysql:

  aProps := TSQLDBZEOSConnectionProperties.Create(
      TSQLDBZEOSConnectionProperties.URI(dMySQL,
        settings.ReadString(CurrentLogonName, 'mysql_host', 'localhost') +
        ':' + settings.ReadString(CurrentLogonName, 'mysql_port', MYSQL_PORT)
        , 'libmysql.dll'),
      settings.ReadString(CurrentLogonName, 'mysql_database', 'mednet_default'),
      CurrentLogonName,
      password);

  VirtualTableExternalRegisterAll(FLocalModel, aProps, [regMapAutoKeywordFields]);

  FLocalRestServer := TSQLRestServerDB.Create(FLocalModel, SQLITE_MEMORY_DATABASE_NAME); // authentication=true
  FLocalRestServer.CreateMissingTables;

  FLocalClient := TSQLRestClientDB.Create(FLocalRestServer);

sql:

SELECT 
GREATEST(
	(SELECT MAX(transfer.SendDateTime) FROM transfer WHERE transfer1.id=transfer.MasterBackLink), 
	(SELECT MAX(transfer.RecieveDate) FROM transfer WHERE transfer1.id=transfer.MasterBackLink)) AS maxdate, 
(SELECT COUNT(*) FROM transfer WHERE transfer1.id=transfer.MasterBackLink) AS messagecount, 
transfer1.id, transfer1.id_user_sender, transfer1.id_user_reciever  FROM transfer AS transfer1 
WHERE transfer1.MessageFormat=1 AND transfer1.id=transfer1.MasterBackLink AND (transfer1.id_user_sender='Test1 Alex' OR transfer1.id_user_reciever='Test1 Alex') 
ORDER BY GREATEST((SELECT MAX(transfer.SendDateTime) FROM transfer WHERE transfer1.id=transfer.MasterBackLink), 
(SELECT MAX(transfer.RecieveDate) FROM transfer WHERE transfer1.id=transfer.MasterBackLink)) DESC

and I would like to get TSQLTableJSON:

var
  tab: TSQLTableJSON;
....
  tab := FConnectionManager.LocalClient.ExecuteList([], sql);

Error:

First chance exception at $762BB727. Exception class ESQLite3Exception with message 'Error SQLITE_ERROR (1) using 3.8.10.2 - 'no such function: greatest' extended_errcode=1'. Process mednet.exe (14492)

Have I any chance?

What way should I use to connect to execute the SQL as SynDBExplorer does it?

Thanks.

alexdmatveev

Member

Registered: 2014-09-12

Posts: 87

Re: zeos-mysql + mormot as sql proxy

I can try to redesign my SQL to avoid horizontal GREATEST function and use UNION+MAX to get vertical max().

Or have we a way with mormot?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: zeos-mysql + mormot as sql proxy

You would have to define a proper service on the server side, and execute directly the SynDBZeos statement, by-passing the ORM and ExecuteList().
You should simply use FetchAllAsJson() method.

See http://synopse.info/files/html/Synopse% … #TITLE_116