mORMot Open Source

mariomoretti

Member

From: italy

Registered: 2012-01-29

Posts: 88

Unexpected array size error in tSqlRestServerDB.ExecuteList

Hello everybody,

I switched tables from sqlite to an external db via ODBC.

A statement worked fine in the sqlite engine , but now raise an error calling tSQLRestServer.InternalAdaptSQL.

The statement is (T is  a tSQLTableJSON):

T := aRest.ExecuteList([tSQLCD_Company], 'select ID from ' + tSQLCD_Company.SQLTableName +
                                            ' where ownerco=0 or id in (select id from ' + tSQLCD_Company.SQLTableName +
                                            ' where ownerco=' + inttostr(maincompany.ID)+')');

maincompany.id is 0, so the resulting statement is
   
select ID from CD_Company where ownerco=0 or id in (select id from CD_Company where ownerco=0) (It seems not having a sense, but is only because the table is empty, so maincompany.id is 0 instead to be >0).

ownerco is a  tID field.

The error is raised in TSynTableStatement.Create when processing the second where condition :  id in (select id from CD_Company where Ownerco=0).

I've debugged and found that it seems dued to an unknown type for  (select id from CD_Company where ownerco=0) , but i was not able to arrange it either patching the source or finding a workaround.

May anyone help me ?

Thanks in advance

Mario

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Unexpected array size error in tSqlRestServerDB.ExecuteList

Use direct execution to the SynDB classes, and bypass the ORM, and its virtual tables mechanism, which won't help for such queries.
Use a service for such complex queries.

mariomoretti

Member

From: italy

Registered: 2012-01-29

Posts: 88

Re: Unexpected array size error in tSqlRestServerDB.ExecuteList

Ok AB,

User may decide if use sqlite or odbc, so i've added a control to use the odbc connection or not and all now works fine.

Complex queries are already only on server side and almost all executed at startup.

tx very very much for you proverbial responsiveness.

Mario

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: Unexpected array size error in tSqlRestServerDB.ExecuteList

Also try to use parameters for your queries, to avoid SQL injection security issues.