Getting "Invalid column type" exception, while using NamedParameterJDBCTemplate for insertion

Question

I am using below code while inserting a row into database(oracle 10g xe,jar: ojdbc14.jar)

String sql = "INSERT INTO SPONSOR_TB(ID,NAME,INDUSTRY_TYPE,IS_REPORTING_SPONSOR,IS_NOT_SOLICITE) VALUES(SEQ_SPONSOR_ID.NEXTVAL,:NAME1,:INDUSTRY_TYPE,:IS_REPORTING_SPONSOR,:IS_NOT_SOLICITE)";

MapSqlParameterSource paramSource = new MapSqlParameterSource();
paramSource.addValue("NAME1",sponsor.getName());
paramSource.addValue("INDUSTRY_TYPE", sponsor.getIndustryType());
paramSource.addValue("IS_NOT_SOLICITE", sponsor.getNotSoliciteFlag()?'Y':'N');
paramSource.addValue("IS_REPORTING_SPONSOR", sponsor.getReportingFlag()?'Y':'N');
KeyHolder generatedKeyHolder = new GeneratedKeyHolder();
namedParameterJdbcTemplate.update(sql, paramSource, generatedKeyHolder,new String[]{"ID"});
int id = generatedKeyHolder.getKey().intValue();

Structure of the table is:

create table SPONSOR_TB
(
 id                   INTEGER not null,
 name                 VARCHAR2(20),
 industry_type        INTEGER not null,
 is_reporting_sponsor CHAR(1) not null,
 is_not_solicite      CHAR(1) not null 
)

and SEQ_SPONSOR_ID is sequence

And Sponsor class is:

public class Sponsor{
      private int id;
      private String name;
      private boolean reportingFlag;
      private boolean notSoliciteFlag;
      private int industryType;
      //getter setter
}

And db configuration in spring-servlet.xml is:

 <beans:bean
    id="dataSource"
    class="org.springframework.jdbc.datasource.DriverManagerDataSource" >

    <beans:property
        name="driverClassName"
        value="oracle.jdbc.driver.OracleDriver" />

    <beans:property
        name="username"
        value="SPONSOR_DB" />

    <beans:property
        name="password"
        value="ajeet" />

    <beans:property
        name="url"
        value="jdbc:oracle:thin:@localhost:1521:XE" />
</beans:bean>

I am getting following exception:

 SEVERE: Servlet.service() for servlet [spring] in context with path [/GroupSolution] threw exception [Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [INSERT INTO SPONSOR_TB (ID,NAME,INDUSTRY_TYPE,IS_REPORTING_SPONSOR,IS_NOT_SOLICITE) VALUES(SEQ_SPONSOR_ID.NEXTVAL,?,?,?,?)]; SQL state [null]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type] with root cause
java.sql.SQLException: Invalid column type
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectCritical(OraclePreparedStatement.java:9168)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:8749)
    at oracle.jdbc.driver.OraclePreparedStatement.setObjectInternal(OraclePreparedStatement.java:9471)
    at oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:9454)
    at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:351)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:216)
    at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:127)
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:298)
    at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:251)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:581)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:843)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.update(NamedParameterJdbcTemplate.java:288)
    at com.groupsolution.dao.SponsorDaoImpl.createSponsor(SponsorDaoImpl.java:55)
    at com.groupsolution.service.SponsorServiceImpl.createSponsor(SponsorServiceImpl.java:31)
    at com.groupsolution.controller.SponsorController.addSponsor(SponsorController.java:38)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:176)
    at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:436)
    at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:424)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:900)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:827)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
    at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:641)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:225)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:168)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:98)
    at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:927)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407)
    at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1001)
    at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:585)
    at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)

Caffrey

By combinig answers from David and Halfbit, got the soultion: changed my database field from CHAR(1) to CHAR(2) and then tried paramSource.addValue("NAME1",sponsor.getName(),Types.VARCHAR); paramSource.addValue("INDUSTRY_TYPE", sponsor.getIndustryType(),Types.INTEGER); paramSource.addValue("IS_NOT_SOLICITE", sponsor.getNotSoliciteFlag()?'Y':'N',Types.VARCHAR); paramSource.addValue("IS_REPORTING_SPONSOR", sponsor.getReportingFlag()?'Y':'N',Types.VARCHAR); Thanks to David and Halfbit – Caffrey

Whenever you see this exp make sure the data you are passing as input are of same type as of the column to which you are passing. In my case the IN clause I was using was expecting Array of integers i.e [1,2,3,..] but I passed [[1,2,3,...]] – Inconsiderable

Answer 1

You might want to try using strings instead of characters for your CHAR(1) Y/N fields.

Homeopathic

Thanks a lot @halfbit.. changed my database field from CHAR(1) to CHAR(2) and then tried paramSource.addValue("NAME1",sponsor.getName(),Types.VARCHAR); paramSource.addValue("INDUSTRY_TYPE", sponsor.getIndustryType(),Types.INTEGER); paramSource.addValue("IS_NOT_SOLICITE", sponsor.getNotSoliciteFlag()?'Y':'N',Types.VARCHAR); paramSource.addValue("IS_REPORTING_SPONSOR", sponsor.getReportingFlag()?'Y':'N',Types.VARCHAR); It worked for me... :-) – Caffrey

Whenever you see this exp make sure the data you are passing as input are of same type as of the column to which you are passing. In my case the IN clause I was using was expecting Array of integers i.e [1,2,3,..] but I passed [[1,2,3,...]] – Inconsiderable

Answer 2

You are getting this exception because you are using JdbcTemplate instead of NamedParameterJdbcTemplate. MapSqlParameterSource works with NamedParameterJdbcTemplate.

Madancy

That was exactly it! Thank you for posting it! – Crossbill

It's because of variadic arguments in JdbcTemplate. – Antepast

FMI Instead of LocalDate you should putDate.valueOf(localdate). – Antepast

Whenever you see this exp make sure the data you are passing as input are of same type as of the column to which you are passing. In my case the IN clause I was using was expecting Array of integers i.e [1,2,3,..] but I passed [[1,2,3,...]] – Inconsiderable

Answer 3

Try changing your code to:

paramSource.addValue("NAME1",sponsor.getName(), Types.VARCHAR);
paramSource.addValue("INDUSTRY_TYPE", sponsor.getIndustryType(), Types.INTEGER);
paramSource.addValue("IS_NOT_SOLICITE", sponsor.getNotSoliciteFlag()?'Y':'N', Types.CHAR);
paramSource.addValue("IS_REPORTING_SPONSOR", sponsor.getReportingFlag()?'Y':'N', Types.CHAR);

Morbihan

@David- I tried using Types.xxxx which you suggested. But now I am getting error: SQL state [null]; error code [17132]; Invalid conversion requested; Even if I changed code to: paramSource.addValue("NAME1","somename", Types.VARCHAR); paramSource.addValue("INDUSTRY_TYPE",2, Types.INTEGER); paramSource.addValue("IS_NOT_SOLICITE",'N', Types.CHAR); paramSource.addValue("IS_REPORTING_SPONSOR",'N', Types.CHAR); – Caffrey

Answer 4

In my case I was providing an Enumeration datatype instead of a datatype compatible with VARCHAR2 in database (like String ).

My query was like :

private static final String QUERY ="SELECT res.id, FROM result res " +
"WHERE res.country = :resCountry ";

And at the time of passing the parameter resCountry, I did like this:

List<Map<String, Object>> listResult = jdbcTemplate.queryForList(QUERY,Country.US);

Now the complaint/exception came from the fact that Country.US was of type Enumeration Country

I did the following change to make it work.

List<Map<String, Object>> listResult = jdbcTemplate.queryForList(QUERY,Country.US.name());

After this change it worked fine.

Harrison

Whenever you see this exp make sure the data you are passing as input are of same type as of the column to which you are passing. In my case the IN clause I was using was expecting Array of integers i.e [1,2,3,..] but I passed [[1,2,3,...]] – Inconsiderable

Answer 5

Though it is too late, today I found the same issue. I tried all of the above options which were provided by the other members, but no luck. What I found was, to add a char column in where clause I was providing the java.lang.char in that space.

Suppose my query looks like this - SELECT * FROM app_config WHERE enable = :enable;, where the 'enable' column is char(1) column in DB. So while passing the parameter in namedparameterJdbcTemplate, I was passing a java char as 'Y', which was causing the error.

In order to solve that I passed normal java string as the parameter value in the namedparameterJdbcTemplate like "Y" and BANG!!! It works fine. Also I need not to provide the Types in MapSqlParameterSource. So my ultimately code looks like below -

protected String getConfigValue(String configKey, String enable) throws BusinessException {
        String value = "";
        try {
            if (StringUtils.isNotBlank(configKey)) {
                MapSqlParameterSource parameters = new MapSqlParameterSource();
                parameters.addValue(ApplicationDaoConstants.CONFIG_KEY, configKey); // This is the VARCHAR column in DB
                parameters.addValue(ApplicationDaoConstants.ENABLE, enable); // This is the character column in DB

                value = nmJdbcTmplt.query(AppConfigSQL.GET_CONFIG_VALUE, parameters, rs -> {
                    if (rs.next()) {
                        return rs.getString(ApplicationDaoConstants.CONFIG_VALUE);
                    }
                    return "";
                });
            }
        } catch (Exception e) {
            log.error("Exception occured while accessing app_config ", e);
            throw new BusinessException(ApplicationConstants.ERROR_ACCESSING_DATABASE);
        }

        return value;
    }

I hope this could help others. Thanks!

Handtohand