Integer with Null value converted to Varchar , not working with Sybase batchupdate

[pansingh]

Hi,

I am having this strange behavior with Sybase database & Spring JDBC.

My application is using BatchUpdate which sometime can have NULL value for int/float column.

This is the sample code :


//Test :table test(col1 int null,col2 int null)

String sql = "insert into test(" + "col1,"
+ "col2)"
+ "values(" + ":col1," + ":col2)";



// Now prepare dummy data
MapSqlParameterSource[] sourceDataTable = new MapSqlParameterSource[1];

MapSqlParameterSource sourceDataRow1 = new MapSqlParameterSource();

sourceDataRow1.addValue("col1", 1);
sourceDataRow1.addValue("col2", null);

sourceDataTable[0] = sourceDataRow1;

SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(
dataSource);

int[] results = simpleJdbcTemplate
.batchUpdate(sql, sourceDataTable);


Same code with same data works fine in QA environment but this fails in PROD.

Only change we did was PROD Sybase database cache size & it was rebooted after that.
We are not sure is this due to chnage in cache size or rebooting sybase server did reset some property if it did then which one. (DBA is saying DB is still same).

After this reboor my code started failing. with error like "Implicit conversion from datatype 'CHAR' to 'INT' is not allowed. Use the CONVERT function to run this query."


Any input on this would be really helpfull ?

[pansingh]

So far solution I have implemented is using convert int he SQL

String old_sql = "insert into test(" + "col1,"
+ "col2)"
+ "values(" + ":col1," + ":col2)";


String new_sql = "insert into test(" + "col1,"
+ "col2)"
+ "values(" + "convert(int,:col1)," + "convert(int,:col2))";

After these new sql changes it is working fine but I don't don't know why it started failing .
Any help on that would be appreciated.