BUG: JDBC Stored Proc with Output Date Param Causes Exception

• Microsoft Visual J++, version 6.0

SYMPTOMS

Calling a stored procedure with output Date parameters from JDBC can cause a java.lang.NumberFormatException. The exception text is of the form:

*** Number Format Exception ***
java.lang.NumberFormatException: <garbage # here>

        at java/lang/Integer.parseInt (Integer.java)
        at java/lang/Integer.parseInt (Integer.java)
        at java/sql/Timestamp.valueOf (Timestamp.java)
        at com/ms/jdbc/odbc/JdbcOdbcCallableStatement.getTimestamp

(JdbcOdbcCallableStatement.java)

        at com/ms/jdbc/odbc/JdbcOdbcCallableStatement.getObject

(JdbcOdbcCallableStatement.java)

        at sdate.main (sdate.java:30) 

CAUSE

This happens in the implementation of the JDBC/ODBC bridge. Note that calling GetTimestamp instead of getObject produces the same problem.

RESOLUTION

Avoid using Date as an output parameter in a stored procedure.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

-- start of SQL
   create proc SimpleDate2

     @i1  int output,
      @i2  int output,
       @i3  int output,
       @Date4  datetime output
     as

   select @i1 = 666,

          @i2 = 777,
          @i3 = 888,
          @Date4 = getdate()
    return 999

--- End of SQL 

Timestamp tsDate = new Timestamp(System.currentTimeMillis());

int i=-1, iobj=-1,k;
try   {
Class.forName("com.ms.jdbc.odbc.JdbcOdbcDriver");
Connection conn = DriverManager.getConnection"JDBC:ODBC:DRIVER={SQL 
   Server};   SERVER=(local);DATABASE=pubs;UID=sa;PWD=;";
for(i = 1; i < 100; i++ ) {
       CallableStatement stmt = conn.prepareCall(" { ? = call
SimpleDate2(?, ?, ?, ?) }");
stmt.registerOutParameter(1, Types.SMALLINT);
for(k=2;k<5;k++){
stmt.setInt(k,k+999);
   stmt.registerOutParameter(k, Types.INTEGER);
}
stmt.setObject(5, tsDate, Types.TIMESTAMP);
   stmt.registerOutParameter(5, Types.TIMESTAMP);

     int count = stmt.executeUpdate();

Object obj=stmt.getObject(5);
System.out.println(obj);

stmt.close();

 System.gc();
      }
 conn.close();


 }
  catch(NumberFormatException nfe)    {
       System.out.println("*** Number Format Exception ***");
 nfe.printStackTrace ();
       }

} 

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Rick Anderson, Microsoft Corporation

Additional query words:

Keywords : kbDatabase kbVJ600
Version : WINDOWS:6.0
Platform : WINDOWS
Issue type : kbbug