sm_msdn


When updating a timestamp column value to null from within a java program, I get the exception below. The env is MS SQL server 2005 SP2, and MS jdbc driver version: 1.1.1501.101. Please advise of any workarounds or solutions.

Update <table_name> set <colname_oftype_timestamp> to null

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(Unknown Source)
at com.microsoft.sqlserver.jdbc.DTV.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Parameter.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Parameter.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setNull(Unknown Source)
Thanks,
sm.



Re: JDBC driver NullPointerException when updating timestamp column value to null: version 1.1.1501.101

John Gordon


Could you provide a code fragment that you are using where you construct the string and make the call

Thank you,

John (MSFT)







Re: JDBC driver NullPointerException when updating timestamp column value to null: version 1.1.1501.101

sm_msdn

Below is the information you asked for. My guess is the root cause is call to the method preparedStatement.setNull(argIndex, java.sql.Types.NULL), with the second argument null.

String sql = "UPDATE tablename SET col_timestamp = ";
List sqlSelectParams = new ArrayList();
sqlSelectParams.add(theTimeStamp); //this value can be either null or non-null.

//call update method of class org.springframework.jdbc.core.JdbcTemplateUpdate
update(sqlUpdate, sqlSelectParams.toArray(new Object[sqlSelectParams.size()]));

The code works with configuration jTDS driver, but runs into this issue with msjdbc driver.
Complete 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(Unknown Source)
at com.microsoft.sqlserver.jdbc.DTV.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Parameter.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.Parameter.setValue(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.setNull(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.setNull(DelegatingPreparedStatement.java:104)
at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:96)
at org.springframework.jdbc.core.JdbcTemplate$ArgPreparedStatementSetter.setValues(JdbcTemplate.java:1181)
at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:695)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:476)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:691)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:753)
at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:761)






Re: JDBC driver NullPointerException when updating timestamp column value to null: version 1.1.1501.101

David Olix - MSFT

Hi,

Your guess that the problem lies with the second argument, java.sql.Types.NULL, is correct. When calling PreparedStatement.setNull, you need to specify the JDBC type corresponding to the SQL type of the parameter. In this case, you probably want to use java.sql.Types.TIMESTAMP.

Regards,

--David Olix [MSFT]





Re: JDBC driver NullPointerException when updating timestamp column value to null: version 1.1.1501.101

sm_msdn

Can there be a driver patch with support for Types.NULL.
Thanks,
sm.




Re: JDBC driver NullPointerException when updating timestamp column value to null: version 1.1.1501.101

David Olix - MSFT

Hi,

According to https://java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/preparedstatement.html :

"The setNull method allows a programmer to send a JDBC NULL (a generic SQL NULL) value to the database as an IN parameter. Note, however, that one must still specify the JDBC type of the parameter."

SQL Server doesn't really support the SQL NULL type (corresponding to the JDBC NULLtype), just NULL values of other types. So this probably isn't something that we would support in the future. It would also present some difficulties with statement preparation and cause expensive repreparation if used with batch execution.

Regards,

--David Olix [MSFT]