SimpleJDBCInsert does not work with MS SQL Server 2000

[pgondi]

I am using Micrososft SQL Server 2000 and Sql Server JDBC driver 2005 for data access. When am inserting a null value using SimpleJDBCInsert I get NullPointerException from JDBC Driver. I think there is some problem either in the driver or Spring Code. Somewhere the "setNull" function call is not working properly.

It works fine if I use JDBCTemplate function update(sql, qry, sqlTypes) function call.

It also does not work with SimpleJDBCTemplate. Here is the Stack Trace:

java.lang.NullPointerException
at com.microsoft.sqlserver.jdbc.AppDTVImpl$SetValueOp .executeDefault(Unknown Source)
at com.microsoft.sqlserver.jdbc.DTV.executeOp(Unknown Source)
at com.microsoft.sqlserver.jdbc.AppDTVImpl.setValue(U nknown Source)
at com.microsoft.sqlserver.jdbc.DTV.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Parameter.setValue(Un known Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.setObject(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStat ement.setNull(Unknown Source)
at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValueInternal(StatementCreatorUtils. java:152)
at org.springframework.jdbc.core.StatementCreatorUtil s.setParameterValue(StatementCreatorUtils.java:88)
at org.springframework.jdbc.core.ArgPreparedStatement Setter.setValues(ArgPreparedStatementSetter.java:5 1)
at org.springframework.jdbc.core.JdbcTemplate$2.doInP reparedStatement(JdbcTemplate.java:771)
at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:566)
at org.springframework.jdbc.core.JdbcTemplate.update( JdbcTemplate.java:767)
at org.springframework.jdbc.core.JdbcTemplate.update( JdbcTemplate.java:825)
at org.springframework.jdbc.core.JdbcTemplate.update( JdbcTemplate.java:833)
at org.springframework.jdbc.core.simple.AbstractJdbcI nsert.executeInsertInternal(AbstractJdbcInsert.jav a:341)
at org.springframework.jdbc.core.simple.AbstractJdbcI nsert.doExecute(AbstractJdbcInsert.java:331)
at org.springframework.jdbc.core.simple.SimpleJdbcIns ert.execute(SimpleJdbcInsert.java:102)
CCDaoImpl.create(CCDaoImpl.java:77)


Here is the code am using:

in the Init method:
public void init(DataSource dataSource) {
     this.insertCC = new SimpleJdbcInsert(dataSource)
          .withTableName("TABLE1")
          .usingColumns("COL1", "COL2", "COL3", "COL4");
}

In the create method:
public void create(CC cc){
    SqlParameterSource parameters = new BeanPropertySqlParameterSource(cc);
   this.insertCC.execute(parameters);
}

Here If any of COL value is null, it does not work.

Any guidance is appreciated.

[trisberg]

What does your table look like? Have you tried the jTDS driver?

[pgondi]

I appreciate your response.

Table definition is pretty straight forward:

Table: table1
COL1 VARCHAR NOT NULL (PK)
COL2 VARCHAR NULL
COL3 VARCHAR NULL
COL4 VARCHAR NULL

I never heard of this driver before. Let me google it and see.

[trisberg]

I have been able to reproduce this. I have seen JDBC drivers have similar issues with date columns, but never with a varchar column. I'll have to investigate more to see if there is anything we can do in the StatementCreatorUtils.

As a workaround, you can specify the types explicitly like this:

parameters.registerSqlType("COL3", Types.VARCHAR);

You could also use the jTDS driver which worked fine with the same code.

[trisberg]

I have created a JIRA issue to fix this - http://jira.springframework.org/browse/SPR-4689

What version of the Microsoft driver where you using?

[pgondi]

What version of the Microsoft driver where you using?

I am using 2005 JDBC driver.

I downloaded JTDS driver few minutes ago and it is working fine with this driver. Going forward I will use this driver instead of MS one.

Thanks a lot for your time and help! I really appreciate it.

[aperezymadrid]

This reply was an error. It works fine