java.sql.SQLException: Invalid column type for update query with In clause in jdbcTemplate

Question

I am trying to update the Oracle table row using jdbcTemplate as follows

String sql = "update EVENTS set status = null where TEMP_ID IN (select TEMP_ID from EVENTS where STATUS = 'COMPLETE' and EXCHANGE_ID IN (?)   )";    
Map<String, Object> paramMap = new HashMap<String, Object>();
List<Long> longValues = new ArrayList<Long>();
longValues.add(1);
longValues.add(2);
paramMap.put("EXCHANGE_ID", longValues);
int rowsAffected = this.jdbcTemplate.update(sql,paramMap,Long.class);

where EXCHANGE_ID is a column in EVENTS table with data type NUMBER(6,0).

When I try to run the above program it is throwing an exception as follows

PreparedStatementCallback; uncategorized SQLException for SQL [update EVENTS set status = null where TEMP_ID IN (select TEMP_ID from EVENTS where STATUS = 'COMPLETE' and EXCHANGE_ID= ? )]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type

Edit : Number of Parameters in In clause are not fixed. so number of Parameters can be 2 ,3 depending upon the user.

for simplicity I have added following lines in code

longValues.add(1);
longValues.add(2);

In reality I am receiving Parameters from the form. I have just added part of code in my question.

Due to some constraints I can only use ? at my Parameter place in my sql query not :EXCHANGE_ID

My guess is that you can't bind a list the way you are doing it. — Tim Biegeleisen, Feb 11 '19 at 11:59
I am accepting list elements from form but for simplicity I had added in my code like this longValues.add(1); longValues.add(2); — Pawan Patil, Feb 11 '19 at 12:06
I ask this because you may just use a fixed number of placeholders, e.g. `WHERE IN (?, ?)` — Tim Biegeleisen, Feb 11 '19 at 12:10
Possible duplicate of [How to execute IN() SQL queries with Spring's JDBCTemplate effectivly?](https://stackoverflow.com/questions/1327074/how-to-execute-in-sql-queries-with-springs-jdbctemplate-effectivly) — Marmite Bomber, Feb 11 '19 at 22:06

Estanislao Pérez Nartallo · Answer 1 · 2019-02-11T12:52:03.630

1

Updated: You need to use :EXCHANGE_ID in the SQL sentence:

final String sql = "update EVENTS set status = null where TEMP_ID IN (select TEMP_ID from EVENTS where STATUS = 'COMPLETE' and EXCHANGE_ID = :EXCHANGE_ID)";

final MapSqlParameterSource params = new MapSqlParameterSource();
params.put("EXCHANGE_ID", Long.valueOf(1));

int rowsAffected = this.jdbcTemplate.update(sql, params);

edited Feb 11 '19 at 12:52

answered Feb 11 '19 at 12:31

Estanislao Pérez Nartallo

212
2
5

EXCHANGE_ID is just an ID or a list? Beacuse in your updated query you are using a equals operation and passing a list. – Estanislao Pérez Nartallo Feb 11 '19 at 12:46
EXCHANGE_ID is just an ID – Pawan Patil Feb 11 '19 at 12:47
See my updated answer. Please, let me know if it works ;) – Estanislao Pérez Nartallo Feb 11 '19 at 12:52
see my updated query again . problem is number of parameters in In clause are not fixed. – Pawan Patil Feb 11 '19 at 12:54
@PawanPatil if you are restricted to use `?` you must dynamically generate the required number of the `?` according to the number of the values in the list. Only using [`NamedParameterJdbcTemplate`](https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/jdbc/core/namedparam/NamedParameterJdbcTemplate.html) allows you to expand the parameter to the required number of placeholders. – Marmite Bomber Feb 11 '19 at 22:04

java.sql.SQLException: Invalid column type for update query with In clause in jdbcTemplate

1 Answers1