SQLException

2 posts, 0 answers
  1. Roberto
    Roberto avatar
    1 posts
    Member since:
    Feb 2012

    Posted 02 Feb 2012 Link to this post

    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
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 07 Feb 2012 Link to this post

    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 >>
  3. DevCraft banner
Back to Top