Announcement Announcement Module
Collapse
No announcement yet.
SimpleJDBCInsert does not work with MS SQL Server 2000 Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SimpleJDBCInsert does not work with MS SQL Server 2000

    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:

    Code:
    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.

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

    Comment


    • #3
      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.

      Comment


      • #4
        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:

        Code:
        parameters.registerSqlType("COL3", Types.VARCHAR);
        You could also use the jTDS driver which worked fine with the same code.

        Comment


        • #5
          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?

          Comment


          • #6
            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.

            Comment


            • #7
              Same problem with sqljdbc and latest jtds 1.2.2 for mssql 2000

              This reply was an error. It works fine
              Last edited by aperezymadrid; Apr 15th, 2009, 05:13 AM.

              Comment

              Working...
              X