DO NOT REPLY [Bug 31045] New: - Null handling in Insert, Update, and Delete

[bu...@apache.org]

DO NOT REPLY TO THIS EMAIL, BUT PLEASE POST YOUR BUG 
RELATED COMMENTS THROUGH THE WEB INTERFACE AVAILABLE AT
<http://issues.apache.org/bugzilla/show_bug.cgi?id=31045>.
ANY REPLY MADE TO THIS MESSAGE WILL NOT BE COLLECTED AND 
INSERTED IN THE BUG DATABASE.

http://issues.apache.org/bugzilla/show_bug.cgi?id=31045

Null handling in Insert, Update, and Delete

           Summary: Null handling in Insert, Update, and Delete
           Product: Commons
           Version: unspecified
          Platform: PC
        OS/Version: Windows XP
            Status: NEW
          Severity: Major
          Priority: Other
         Component: DbUtils
        AssignedTo: commons-dev@jakarta.apache.org
        ReportedBy: mukwar@yahoo.com


While using the DbUtils to perform an insert or an update into DB2 v8 I ran into
a null issue.  DbUtils uses the following to handle nulls.  DB2 is requiring the
passing in of the EXACT column type to use setNull(i,Types.*).  Using
Types.OTHER throws an exception (see after code).

DbUtils Code:

    /**
     * Fill the <code>PreparedStatement</code> replacement parameters with 
     * the given objects.
     * @param stmt
     * @param params Query replacement parameters; <code>null</code> is a valid
     * value to pass in.
     * @throws SQLException
     */
    protected void fillStatement(PreparedStatement stmt, Object[] params)
        throws SQLException {

        if (params == null) {
            return;
        }

        for (int i = 0; i < params.length; i++) {
            if (params[i] != null) {
                stmt.setObject(i + 1, params[i]);
            } else {
                stmt.setNull(i + 1, Types.OTHER);
            }
        }
    }

Exception thrown:

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0123E  SQL data type out of
range. SQLSTATE=HY004
	at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
	at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source)
	at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source)
	at COM.ibm.db2.jdbc.app.DB2PreparedStatement.execute2(Unknown Source)
	at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeUpdate(Unknown Source)
	at PrepStmt.main(PrepStmt.java:56)
 
Code snippet that threw the exception:

Connection con = DriverManager.getConnection(url, uid, pswd);
PreparedStatement p = con.prepareStatement("insert into dac.person values (?,?)");
p.setInt(1,0); // integer column, id thing
p.setNull(2,Types.OTHER); // pretend BLOB column.  But it fails on anything.
//this one works for the blob column --> p.setBytes(2,null); 
int x = p.executeUpdate();

---------------------------------------------------------------------
To unsubscribe, e-mail: commons-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-dev-help@jakarta.apache.org