Name: krC82822 Date: 12/10/2000


orig synopsis: "ResultSet.getObject() returns sometimes 0, sometimes null for numeric null"

java version "1.3.0beta"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.0beta-b07)
Java HotSpot(TM) Client VM (build 1.3.0beta-b04, mixed mode)
java version "1.2.2"
Classic VM (build Linux_JDK_1.2.2_RC4, native threads, sunwjit)

(Also tested on Windows NT SDK 1.2.2.)

It seems to me that the java.sql.* API documentation and the JDBC specification
leave the behavior of java.sql.ResultSet.getObject(int) slightly ambiguous. Some
JDBC drivers (e.g. PostgreSQL 7.0) return 0 for SQL-null values in numeric
fields, seemingly following the static SQLtype-Javatype-mapping of get<Type>
methods (ch. 8 of the JDBC specification). Some JDBC drivers (e.g. Solid 3.0,
RDB ODBC via JDBC-ODBC bridge) make getObject(int) return a Java-null for
SQL-null values in numeric fields, seemingly following the dynamic type mapping
(ch. 14 of the JDBC specification).

Here is a simple test program for demonstrating the issue. Of course, one needs
the relevant JDBC data sources and drivers.

=================begin=====================================================

import java.sql.*;

public class GetObjectBug {


// fields

  private String jdbcDriverName="sun.jdbc.odbc.JdbcOdbcDriver";
  private String jdbcURL;

  private String sql = "SELECT numero FROM kokeiluja WHERE numero IS NULL";

  private Connection conn;

// constructors

  public GetObjectBug() {
    java.util.Properties props = System.getProperties();
    String propJdbcDriver=props.getProperty("jdbcdriver");
    String propJdbcURL=props.getProperty("jdbcURL");

    if(propJdbcURL==null) {
      System.out.println("\n*** You must specify the JDBC URL "+
        "in the jdbcURL system property. ***\n\n");
      System.exit(1);
    }
    else this.jdbcURL=propJdbcURL;

    if(propJdbcDriver!=null)
      this.jdbcDriverName=propJdbcDriver;
  }

  public GetObjectBug(String sql) {
    this();
    this.sql=sql;
  }

// other methods

  public void connect() throws Exception {
    System.out.println("\nConnecting to URL "+this.jdbcURL+
      " using driver "+this.jdbcDriverName+":");
    Class.forName(this.jdbcDriverName).newInstance();
    this.conn=DriverManager.getConnection(this.jdbcURL, System.getProperties());
  }

  public void close() throws Exception {
    this.conn.close();
  }

  public String runSql() throws Exception {
    Statement st = conn.createStatement();
    ResultSet rs = st.executeQuery(this.sql);
    int nOfCol = rs.getMetaData().getColumnCount();

    StringBuffer result = new StringBuffer();
    while(rs.next()) {
      for(int column=1; column<=nOfCol; column++)
        result.append(rs.getObject(column)+"\t");
      result.append("\n");
    }

    rs.close();
    st.close();
    return result.toString();
  }

// main

  public static void main(String[] args) throws Exception {
    GetObjectBug gob;
    if(args.length>0)
      gob = new GetObjectBug(args[0]);
    else gob = new GetObjectBug();
      
    gob.connect();
    System.out.println("\n\n"+gob.runSql());
    gob.close();
  }

} // class GetObjectBug ends

==================end=================================================

For a numeric field with null values in PostgreSQL, this program output 0's
(zeros). For a similar field in Solid, it outputs "null"s.

In my opinion, the API documentation for java.sql.getObject() should be
made more explicit here.
(Review ID: 106793)
======================================================================