[jira] [Comment Edited] (OLINGO-1460) $filter not working in JPA/Olingo 2.0.11 with MySQL

["Mahdi Ansari (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OLINGO-1460?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17124837#comment-17124837 ] 

Mahdi Ansari edited comment on OLINGO-1460 at 6/3/20, 10:34 AM:
----------------------------------------------------------------

Here is a workaround for how to remove the incorrect 'escape' statement with only one slash, however does not solve the original issue and is not the most efficient way:
  
public class SqlStatementInspector implements StatementInspector { private static final long serialVersionUID = 1L; private static final Logger LOG = Logger.getLogger(SqlStatementInspector.class);@Override public String inspect(String sql) {if (!sql.contains("escape \'
'"))

{return sql;}

// OData JPA query correction -> current version (2.0.11) contains// the invalid 'escape "\"' statement that delivers no results
 LOG.info("Replacing invalid statement: escape \"\\\"");return sql.replace("escape \'
'", "");}}

This overwrites the inspect method and you can modify the generated sql query when using hibernate.

 

 


was (Author: mjz):
Here is a workaround for how to remove the incorrect 'escape' statement with only one slash, however does not solve the original issue and is not the most efficient way:
 
{{public class SqlStatementInspector implements StatementInspector {    private static final long serialVersionUID = 1L;    private static final Logger LOG = Logger.getLogger(SqlStatementInspector.class);@Override    public String inspect(String sql) {if (!sql.contains("escape \'\\'")) {return sql;}// OData JPA query correction -> current version (2.0.11) contains// the invalid 'escape "\"' statement that delivers no results
        LOG.info("Replacing invalid statement: escape \"\\\"");return sql.replace("escape \'\\'", "");}}}}

This overwrites the inspect method and you can modify the generated sql query when using hibernate.

 

 

> $filter not working in JPA/Olingo 2.0.11 with MySQL
> ---------------------------------------------------
>
>                 Key: OLINGO-1460
>                 URL: https://issues.apache.org/jira/browse/OLINGO-1460
>             Project: Olingo
>          Issue Type: Bug
>          Components: odata2-jpa
>    Affects Versions: V2 2.0.11
>         Environment: spring-boot 2.3.0, windows, MariaDB 
>            Reporter: Mahdi Ansari
>            Priority: Major
>
> I made an odata service with {{olingo2}}, {{jpa}} and {{spring-boot}} based on this [GitHub repository|https://github.com/jpenninkhof/odata-boilerplate]. 
> I've set up the project to use MariaDB database and _it works quiet good_.
> However, the project is a little bit old and I tried to upgrade it!
> In the first step I tried to update the libraries versions like this:
>  
> {{    <parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.3.0.RELEASE</version></parent><properties><java.version>1.8</java.version><cxf.version>3.3.6</cxf.version><olingo.version>2.0.11</olingo.version></properties>}}
> when I try the following request that I use a filtering on a column of type string:
>  
> {{http://localhost:9090/odata.svc/Members?$format=json&$filter=FirstName eq 'Jack'}}
> I receive an error message in response to my request in postman or browser, like this:
>  
> {{{"error": {"code": null,"message": {"lang": "en","value": "org.hibernate.exception.SQLGrammarException: could not extract ResultSet"}}}}}
>  
> It actually generates a wrong query on database while it has the correct dialect!
> What is the problem here? It uses {{escape '\'}} instead of {{escape '\\'}} in the query.
> What is your suggestion for solving this issue?
> The interesting part is if I have $filter and $expand at the same time in my query then it will not inject escape '\' in the query anymore. 
>  
>  
>  



--
This message was sent by Atlassian Jira
(v8.3.4#803005)