This is a migrated thread and some comments may be shown as answers.

SQLException

1 Answer 89 Views
Web Services
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Roberto
Top achievements
Rank 1
Roberto asked on 02 Feb 2012, 12:06 PM
Hello, I've been using your Data Services wizard lately, but I came across some difficulties. It seems that the translation from the Rest query to the SQL has some problems, namely when I add more than one filter.

With each filter used separately it works perfectly, but combining the 2 throws the following exception:

Error executing query: Telerik.OpenAccess.RT.sql.SQLException: Incorrect syntax near the keyword 'IS'.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery()
   at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()
SQL:
SELECT COUNT(1) AS  EXPR1                  FROM [UnidadeAutonoma] a JOIN [PropriedadeHorizontal] AS b ON (a.[PropriedadeHorizontal_intIdPropriedadeHorizontal] = b.[intIdPropriedadeHorizontal]) JOIN [Pedido] AS c ON (a.[Pedido_intIdPedido] = c.[intIdPedido]) WHERE ((CASE WHEN ( (lower(b.[vcNumeroPH]) LIKE '%41%' ESCAPE '\' AND lower(c.[vcNumeroPedido]) LIKE '%03%' ESCAPE '\') IS NULL) THEN 0 ELSE  (CASE WHEN lower(b.[vcNumeroPH]) LIKE '%41%' ESCAPE '\' AND lower(c.[vcNumeroPedido]) LIKE '%03%' ESCAPE '\' THEN 1 ELSE 0 END)  END) = 1)   Telerik.OpenAccess.RT.sql.SQLException: Incorrect syntax near the keyword 'IS'.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.executeQuery()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.executeQuery()
   at OpenAccessRuntime.Relational.fetch.FetchResultImp.Execute()

The RESTful query is:
http://localhost:1635/WCFSIDataBase.svc/UnidadeAutonomas()?$filter=substringof('41',tolower(PropriedadeHorizontal/VcNumeroPH)) and substringof('03',tolower(Pedido/VcNumeroPedido))&$top=10&$expand=Pedido/Processo/ProcessoTipo, PropriedadeHorizontal, CadastroPredial&$inlinecount=allpages

Again, if only 1 of the filters is present it works fine (anyone).

Thanks in advance,

Roberto FĂ©lix

1 Answer, 1 is accepted

Sort by
0
Thomas
Telerik team
answered on 07 Feb 2012, 12:22 PM
Bom dia Roberto,

we identified the issue and the needed changes will be in the 2012 Q1 release that is due within 2 weeks.
Unfortunately, there is currently no workaround.

Regards,
Thomas
the Telerik team
Sharpen your .NET Ninja skills! Attend Q1 webinar week and get a chance to win a license! Book your seat now >>
Tags
Web Services
Asked by
Roberto
Top achievements
Rank 1
Answers by
Thomas
Telerik team
Share this question
or