Unable to execute LIKE query in HSQLDB

[dhaval soni]

I need to execute LIKE query. i have created as below:

AData ad : ao.find(AData.class, Query.select().where("user=? AND ISSUES LIKE %?%",user,issueId)))  //issues field contain values as //"FIN-1,FIN-2,FIN7". issueId parameter has value as FIN-7

it gives me below error:

java.sql.SQLException: Unexpected token: % in statement [SELECT * FROM PUBLIC.AO_0371A8_ADATA WHERE user=? AND ISSUES LIKE %?%]

Updated the query below as :

AData ad : ao.find(AData.class, Query.select().where("user=? AND ISSUES LIKE '%?%'",user,issueId)))  //issues field contain values as //"FIN-1,FIN-2,FIN7". issueId parameter has value as FIN-7

gives me below error: ( parameter index out of range: 2) (Also tried as ".......LIKE \'%?%\'"... but give me same error as below:

com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
	- name:HSQL Database Engine
	- version:1.8.0
	- minor version:8
	- major version:1
Driver:
	- name:HSQL Database Engine Driver
	- version:1.8.0

java.sql.SQLException: Invalid argument in JDBC call: parameter index out of range: 2
	at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:153)
	at com.atlassian.activeobjects.osgi.DelegatingActiveObjects.find(DelegatingActiveObjects.java:81)  <+3> (NativeMethodAccessorImpl.java:39) (DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:58)
	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:62)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
......
..
.

How it could be working.. what is wrong in this LIKE query?

Thank You

[Timothy]

Have you tried this as described in the documentation?

entityManager.find(Post.class, Query.select().where("SOME_FIELD LIKE ?", "%" + arg));

https://developer.atlassian.com/display/DOCS/Finding+Entities

[dhaval soni]

Tried below both queries as riasing still error as follow:

AData ad : ao.find(AData.class, Query.select().where("user=? AND ISSUES LIKE ? ",user, "%" + issueId + "%")))

Also tried other way as below:

AData ad : ao.find(AData.class, Query.select().where("user=? AND ISSUES LIKE '%?%'  ",user,issueId)))

In above both cases, got below error: (Mainly error says, " java.sql.SQLException: Invalid argument in JDBC call: parameter index out of range: 2").

Uncaught exception thrown by REST service
    com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown     by the Active Objects library:
    Database:
	- name:HSQL Database Engine
	- version:1.8.0
	- minor version:8
	- major version:1
    Driver:
	- name:HSQL Database Engine Driver
	- version:1.8.0

    java.sql.SQLException: Invalid argument in JDBC call: parameter index out of range: 2
	at     com.atlassian.activeobjects.internal.EntityManagedActiveObjects.find(EntityManagedActiveObjects.java:    153)
	at     com.atlassian.activeobjects.osgi.DelegatingActiveObjects.find(DelegatingActiveObjects.java:81)  <+3>     (NativeMethodAccessorImpl.java:39) (DelegatingMethodAccessorImpl.java:25)
 	at java.lang.reflect.Method.invoke(Method.java:597)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.osgi.service.importer.support.internal.aop.ServiceInvoker.do
    ....

I have passed both argumnet parameter and also gave two "?" though it raise and index out of range 2 error...

[Harry Chan]

Are you sure it's ISSUES in caps? Case is important. Confirm that AData has these fields. What does this do?

AData ad : ao.find(AData.class, Query.select().where("user = ? AND ISSUES LIKE ?", user, "%" + issueId)))