0

I'm getting the exception shown below, when I run the following query with jdbc. This is a simplified example, the other parts of the preparedStatementSetter with setString and setInt, etc. ran successfully.

public List<EmployeeMonitoringResultTo> searchEmployeesForEmployerInRange(final GetEmployeesForEmployerInRangeCriteria criteria) {
    final StringBuilder sql = 
    new StringBuilder(  "select * from CODES c ")
                .append("    where c.TYPE in ( ? )  "); // TYPE has DATA_TYPE 12, TYPE_NAME VARCHAR

    return jdbcTemplate.query(
        sql.toString(),
        new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setArray(1, searchTypes());
            }
        },
        new SpecificRowMapper<SpecType>()
        );      
}

private Array searchTypes() {
    final Collection<String> collection = fetchStrings();

    Array resultArray = null;;
    try {
        resultArray = jdbcTemplate.getDataSource().getConnection().createArrayOf("VARCHAR", collection.toArray());
    } catch (SQLException e) {
        log.error("Problem with created java.sql.Array", e);
        throw e;
    }

    return resultArray;
}

Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10349][11717][4.13.80] Unsupported cross conversion from source type Object;. ERRORCODE=-4461, SQLSTATE=42815 at com.ibm.db2.jcc.am.id.a(id.java:677) at com.ibm.db2.jcc.am.id.a(id.java:60) at com.ibm.db2.jcc.am.id.a(id.java:103) at com.ibm.db2.jcc.am.ic.a(ic.java:289) at com.ibm.db2.jcc.am.ic.a(ic.java:191) at com.ibm.db2.jcc.am.kc.a(kc.java:1943) at com.ibm.db2.jcc.am.go.a(go.java:2289) at com.ibm.db2.jcc.am.go.setArray(go.java:2254) at com.jolbox.bonecp.PreparedStatementHandle.setArray(PreparedStatementHandle.java:261) at net.sf.log4jdbc.PreparedStatementSpy.setArray(PreparedStatementSpy.java:349) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) ... 93 more

K.C.
  • 2,084
  • 2
  • 25
  • 38
  • 2
    Yeah, you can't do this. You need to work out how many `?` signs you need in your `in` clause, and set the parameters individually. You can't set an entire array into an `in` clause like this. – Dawood ibn Kareem Apr 08 '14 at 07:35
  • Indeed @DavidWallace , you are right. Do you post an answer or do I? – K.C. Apr 08 '14 at 08:12
  • 2
    My honest feeling is that [this question](http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives) has enough information in its answers, and there's no point in duplicating it all here. – Dawood ibn Kareem Apr 08 '14 at 08:36
  • True, the answer of BalusC is clear enough. I used Strings.repeat("?", ", ",types.size()); to build the query. I'll close this one. Answer: http://stackoverflow.com/a/1916391/1173560 – K.C. Apr 08 '14 at 08:53

1 Answers1

0

The toArray method returns an array of objects. You could try using an array of strings:

collection.toArray(new String[0]);

You probably can't use an SQL array with an IN operator anyway, so you'll have to generate SQL instead of using a prepared statement. (Or at least generate the correct number of question marks and then set the parameters in a loop, like suggested in the comments.)

You are also missing the where keyword.

Joni
  • 108,737
  • 14
  • 143
  • 193
  • Indeed I forgot 'where' in my simplified example. I tried with your solution, but unfortunately I got also this error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][1091][10417][4.13.80] Invalid data conversion: Parameter instance com.ibm.db2.jcc.am.o@5aaeaeb2 is invalid for the requested conversion. ERRORCODE=-4461, SQLSTATE=42815 – K.C. Apr 08 '14 at 08:11