Query using String Array in IN cluase

gautam · **Joined:** Mon Nov 28, 2005 1:04 am **Posts:** 9

Hi friends,

I am trying to execute a query by passing string array to IN cluase. But I am getting Data type mismatch error message. Here is my query.

Code:

String Sql =
         "select ocorder.Id, ocorder.Custid"
        + " from Ocorder ocorder where ocorder.Id in (:ordnumarray)";
         

Here I am passing ordnumarray as "302000097, 302000098"

Is there anything wrong in this query?

Please guide me.

thanks,
gautam

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Have you omitted the quotes accidentally? An array of strings should appear in SQL as ('302000097', '302000098'). Without the quotes, it's an array of ints.. hence type mismatch.

gautam · **Joined:** Mon Nov 28, 2005 1:04 am **Posts:** 9

Thanks for your reply.

I changed it to ('302000097', '302000098') still it is giving the same message as follows,

Code:

net.sf.hibernate.JDBCException: Could not execute query
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1539)
Caused by: java.sql.SQLException: Data type mismatch.
   at com.ibm.as400.access.JDError.throwSQLException(JDError.java:381)

if I give two seperate strings with query.setString("order1", "302000097") and query.setString("order2", "302000098") the query is working. But If I give these two order numbers in single a string(by separating with quotes) it is not working.

Can you help me.

thanks,
gautam

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Ah I'm caught up. I thought you were building the string yourself.

Just change the java code you're using to populate :ordnumarray to setParameterList instead of setParameter/setString.

ramnath · **Posted:** Tue Dec 20, 2005 12:44 am

Hi

How about using Criteria here?
Some thing like this

Criteria critetria= session.createCriteria(YOURCLASS.class);
critetria.add(Expression.in("Id", IDLIST));
List list = critetria.list();

Any Suggestions?????????

gautam · **Joined:** Mon Nov 28, 2005 1:04 am **Posts:** 9

Hi tenwit.

Your answer is awesome. This solved my problem. I have given credit to you. Thank you very much. Keep it up.

gautam.