SQL statements with NOT LIKE are ignored

[Former Member]

Hi experts,

I have a web application which uses JPA framework and OpenSQL. When I use JPQL query with NOT LIKE keywords in it the 'NOT LIKE' criteria gets somehow ignored and the returned result do not reflect it. I can see in the log that the actually generated SQL is correct and if I test it directly in the SQL Server Management Studio it returns the correct results, but in my app the results I get are not correct.

For example:

select e from MyEntity e where e.status not like 'success';

gets transformed to something like

select e.id, e.status from MY_ENTITY_TABLE e where e.status not like 'success'

and I expect to get all entities with status other than 'success'. But what I get is all the entities as if the JPQL statement was

select e from MyEntity e;

What could be the reason for this behavior?

Best regards,

Martin

[Former Member]

where e.status not like 'success';

This doesn't make sense imho.

You probably want either of these:

where e.status != 'success';

where e.status not like '%success%';

hope this helps

[Former Member]

Thank you for quick reply, Joe!

Yes, I meant the second option with '%' symbols but still I get incorrect results. Seems that OpenSQL layer ignores the 'NOT LIKE' criterias. Any other ideas?

I'm pasting you the log I have with the JPQL and the prepared SQL queries

<query jpql="select distinct e, e.time.srvrStrInitTime from SlAutoGeneral e where e.status not like :status order by e.time.srvrStrInitTime desc ">
    <executeQuery>
        <pre><code>

Best regards,

Martin

[Former Member]

Hi guys,

just fyi I found my problem and it wasn't with JPA or OpenSQL. I just passed wrong values to the parameters in the query.

Best regards,

Martin