Using non-typed Null for PreparedStatement.setObject cause an error with Sybase sql server.

[kalinichenko]

java.sql.SQLException: JZ0SE: Invalid object type (or null object) specified for setObject().
    at com.sybase.jdbc4.jdbc.ErrorMessage.raiseError(ErrorMessage.java:765)
    at com.sybase.jdbc4.jdbc.SybPreparedStatement.setObject(SybPreparedStatement.java:1247)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:169)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:169)
    at scalikejdbc.StatementExecutor$$anonfun$bindParams$3.apply(StatementExecutor.scala:77)
    at scalikejdbc.StatementExecutor$$anonfun$bindParams$3.apply(StatementExecutor.scala:75)
    at scalikejdbc.StatementExecutor.bindParams(StatementExecutor.scala:75)
    at scalikejdbc.StatementExecutor.initialize(StatementExecutor.scala:59)
    at scalikejdbc.StatementExecutor.<init>(StatementExecutor.scala:53)
    at scalikejdbc.DBSession$class.createStatementExecutor(DBSession.scala:65)
    at scalikejdbc.DBSession$class.updateWithFilters(DBSession.scala:308)
    at scalikejdbc.ActiveSession.updateWithFilters(DBSession.scala:420)
    at scalikejdbc.DBSession$class.updateWithFilters(DBSession.scala:290)
    at scalikejdbc.ActiveSession.updateWithFilters(DBSession.scala:420)
    at scalikejdbc.SQLUpdate.apply(SQL.scala:460)
    at models.Employee$.create(Employee.scala:134)
    at models.EmployeeSpec$$anonfun$1$$anonfun$apply$1$$anon$1$delayedInit$body.apply(EmployeeSpec.scala:43)
    at models.EmployeeSpec$$anonfun$1$$anonfun$apply$1$$anon$1.delayedInit(EmployeeSpec.scala:42)
    at models.EmployeeSpec$$anonfun$1$$anonfun$apply$1$$anon$1.<init>(EmployeeSpec.scala:42)
    at models.EmployeeSpec$$anonfun$1$$anonfun$apply$1.apply(EmployeeSpec.scala:42)
    at models.EmployeeSpec$$anonfun$1$$anonfun$apply$1.apply(EmployeeSpec.scala:42)

[seratch]

Oh we must specify java.sql.Types when using #setNull?

http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setNull(int, int)

It's difficult to support this for ScalikeJDBC without reducing usability.

[kalinichenko]

I'm not sure yet. I will try the same via jtds driver instead of jconn. Second option is specify generic sql type Null. I let you know how it works on Monday when I have an access to sybase sql.

[kalinichenko]

So, I checked the case when sql.Types is not specified for null values for sybase (using origin jdbc driver and jtds), mssql and oracle.

I succeed only at doing it in mssql.

Oracle cursed something like this:

[error]    SQLException: : Invalid column type  (DBError.java:180)
[error] oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
[error] oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
[error] oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:285)
[error] oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.java:5465)
[error] oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:1028)
[error] oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2311)
[error] oracle.jdbc.driver.OraclePreparedStatement.setObject(OraclePreparedStatement.java:2566)
[error] org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:169)
[error] org.apache.commons.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:169)

that means the same issue.

Using sybase I tried to use Types.NULL and jtds driver for it but I had no luck either.

[seratch]

That's strange. ScalikeJDBC basically works with Oracle DB though we don't support it officially. I just tried with Oracle JDBC driver 10.2.0.2 and it's surely possible to pass null value to statements.

[kalinichenko]

My fault. I was using an old jdbc driver for Oracle 9i although my server version was 10.2.0.3.0. It definetelly works for oracle.

[seratch]

OK. I have no plan to support Sybase. I hope Sybase JDBC drivers will start supporting null values for #setObject in the future (as same as Oracle JDBC driver).

[kalinichenko]

btw, I've checked sybase 15.5 + jtds and it's possible to call setObject without specifying its sql type.

[seratch]

Cool. I'm glad to hear that.

[dgrene]

hi @kalinichenko , could you please tell me how you resolved this issue with sybase.I am still struggling with that

[pariskapoor]

Hi All,

I had the same issue. In sybase procedure we set as
@contrlnum int = null

While in JAVA callable statement. how to set null by default. We tried setNull, setObject and setString but not working. Any idea please help.