springframework.jdbc.UncategorizedSQLException :Invalid column type-

Question

Hi am trying to do a QueryForInt using spring jbdc.The query returns a simple count.On execution i get springframework.jdbc.UncategorizedSQLException:java.sql.SQLException: Invalid column type. I did find similar posts and tried the suggestions but i am still stuck:(... Any help is appreciated. ``` MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue(DAOConstants.PROD_ID, custVo.getProdId(),OracleTypes.NUMBER); params.addValue(DAOConstants.REQ_IND, DAOConstants.FLAG_Y,OracleTypes.VARCHAR); ``` The query: ``` select count(1) from prod where prod_id = :PROD_ID and req_ind =:REQ_IND ``` Table definition ``` Name Null Type ----------------- -------- ------------ PROD_ID NOT NULL NUMBER(5) REQ_IND VARCHAR2(10) ``` The Logs.. ``` Caused by: org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select count(1) from prod where prod_id = :PROD_ID and req_ind = :REQ_IND ]; SQL state [99999]; error code [17004];…

Accepted Answer

Based on the stacktrace it looks like you are using the JdbcTemplate class. Since you are using the named parameter style place holders and providing a MapSqlParameterSource for the parameters you need to use the NamedParameterJdbcTemplate.

So, why are you getting this strange error message? Well, your SQL query actually works using the Oracle JDBC driver. It does allow you to use either "?" or a named parameter style using a ":" as the prefix fro the placeholders. You still need to provide the parameter values in the correct order which the MapSqlParameterSource in your case does not. You can test this by using an Object array instead like:

```
new Object[] {custVo.getProdId(), DAOConstants.FLAG_Y}
```

The error is based on the values coming in the wrong order from the params.getValues() method, with the String value being passed in for the :PROD_ID.

springframework.jdbc.UncategorizedSQLException :Invalid column type-

Solution

Problem