mORMot Open Source

deChateau

Member

From: México

Registered: 2016-03-18

Posts: 2

MSSQL RESTful server/client TSQLTableJSON LEFT JOIN problem

Hello, everyone.

I have a problem with LEFT JOIN query type using TSQLTableJSON by calling TSQLRestClientURI.ExecuteList in client app to a TSQLRestServerDB Server App using a TSQLDBServerHttpApi server publishing a TOleDBMSSQL2012ConnectionProperties pointing to a MS SQL Server 2014 Database. (I would prefer to use ZEOS -or UniDAC- but... I just can't make it work using ZDBC 7.2 RC 1).

My Query is like:

SELECT
  Customers.ID,
  Customers.CustomerType,
  Customers.Name,
  CustomersClasses.Name <— Her an ‘… AS OtherFieldName’ would be better
FROM
  Customers
LEFT JOIN
  CustomersClasses
ON
  CustomersClasses.Id = Customers.IdCustomerClass

When I try to call TSQLRestClientURI.ExecuteList method, internally is generated a query like:

SELECT
  Customers.ID,
  Customers.CustomerType,
  Customers.Name,
  CustomersClasses.Name
FROM
  Customers,CustomersClasses

And the result is Nil.

Code segment:
const
  strSQL  = 'Customers.ID,Customers.CustomerType,Customers.Name,CustomersClasses.Name';
begin
  ...
  MyTable   := MyRestClient.ExecuteList([TSQLCustomers,TSQLCustomersClasses], strSQL);  <-- Result is Nil
  ...

Any guidelines to solve my problem? Maybe I am not doing it well.

Thanks in advance.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: MSSQL RESTful server/client TSQLTableJSON LEFT JOIN problem

Please take a look in the doc about how the ORM works.
See http://synopse.info/files/html/Synopse% … ml#TITL_27

Such a complex query would be handled by the SQLite3 virtual engine.
LEFT JOIN is not handled.
So it is far from efficient.

The problem should be solved either by using data sharding (i.e. putting the data in the main table, without a JOIN, or making two separate requests), or by running directly the SQL on the TOleDBMSSQL2012ConnectionProperties, by-passing the ORM, and defining an SOA service on the server side.

deChateau

Member

From: México

Registered: 2016-03-18

Posts: 2

Re: MSSQL RESTful server/client TSQLTableJSON LEFT JOIN problem

Thanks for the orientation! I've been reading the documentation but have some doubts about somethings.
With your answer I can see better where to go from now to solve my problem.
Have a nice day!