Name: mc57594 Date: 01/30/97


I am running JDK 1.02, JDBC 1.21 and JDBC-ODBC Bridge 1.21 on NT server 4.0 (The driver output still says 1.1001 for the bridge).

A. Problem With NULL Timestamp in PreparedStatement
With both MS Access 95 and MS SQL Server 6.5 I have problems trying to populate a Timestamp field with a null value.

        A. If I try
                Timestamp ts = null;
                statement.setTimestamp(counter++, ts);

        I get a null pointer exception.

        B. If I try
                statement.setNull(counter++, java.sql.Types.TIMESTAMP);

        I get a SQLException [Microsoft][ODBC Driver Manager] SQL data type out of range
        I have checked the ResultSetMetaData and it says the column is type 93,TIMESTAMP and nullable.

B. Problem Accessing ResultSet Out Of Order If It Contains NULL columns
I have found another problem that I have worked around but could cause a lot of problems. When I get a ResultSet back from a query, problems occur if a column contains null and the columns are not access in assending order. REALLY!! I get six columns back from by query (at least one column contains null). I I access them from 1 to 6 all is okay. I reverse the access order I get a SQLException on the getXXX method following the null read.
[Microsoft][ODBC Driver Manager] SQInvalid column number


I tried for 2 days to send this problem to ###@###.###, ###@###.### so I am trying multiple locations this time.

Mike
company - Born Information Services , email - ###@###.###
======================================================================

-------------------------------------------
dale.mcduffie@Eng 1997-07-20: Additional information from another customer:

The Jdbc-Odbc bridge driver throws a
NullPointerException when a null string is passed
to PreparedStatment.setString(n, String).
Using "setNull( n, Types.VARCHAR)" works as expected.

The error occurs at:
java.lang.NullPointerException:
  at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setChar(JdbcOdbcPreparedStatement.java:1067)
  at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setString(JdbcOdbcPreparedStatement.java:507)

company - Left Coast Software , email - ###@###.###
===============================================================================

daniel.indrigo@Canada 1997-08-21 Still another customer with this problem

Re: BugID 4029237

I am still experiencing this same problem as reported
in the above bug id. Any attempt to use setNull to
write a null value to a Timestamp field produces
an exception. I am trying to copy data from one
database to another, so I am directly mapping values
of type Timestamp to type Timestamp. Therefore,
null values must be set using the setNull()
operation. However, whenever I attempt this, the
Microsoft ODBC Driver Manager returns an exception that
says "SQL data type out of range".
Is this a confirmed (or still suspected ODBC driver
problem)? Or is there a known fix? Any suggestions?
(This is a "show-stopper" for the work I'm doing
with JDBC right now!)

Code segment:
case Types.TIMESTAMP:
{
        Timestamp t = source.getTimestamp(columnIndex);
        if(t != null)
{
destination.setTimestamp(columnIndex,t);
}
else
        {
destination.setNull(columnIndex,sqlType); // sqlType is of type java.sql.Types.TIMESTAMP
}
break;
}
Components involved: Windows NT 4.0, MS-SQL Server 6.5 (w/it's ODBC driver),
Sun JDK 1.1.3, Sun JDBC-ODBC Bridge. Microsoft 32-bit ODBC Driver
Manager (Administrator).

company - AFIT , email - ###@###.###
=============================================================================

daniel.indrigo@Canada 1997-08-21 And here's another

When trying to set a parameter value of type {93} to NULL, JdbcOdbc throws a SQL data type out of range exception.
This does not happen with other drivers. The following shows a trace of what my program is doing and then the
relevant exception stack. The line of values has the format:
   [index]=value{targetSqlType}

SQL: Create PreparedStatement: INSERT INTO Member (firstName,lastName,loginName,middleName,dateOfBirth,dateOfBirth2,SSN,defaultGroupID,salary2,defaultLocationID) VALUES (?,?,?,?,?,?,?,?,?,?)
SELECT @@IDENTITY
[1]=Dave{12} [2]=FormNewMember#7{12} [3]=dleib{12} [4]=Bert{12} [5]=1955-04-06 00:00:00.0{93} [6]=<NULL>{93}

Test Test_I1_Insert Failed with the following exception...
java.sql.SQLException: [Microsoft][ODBC Driver Manager] SQL data type out of range
at sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:3669)
at sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:3822)
at sun.jdbc.odbc.JdbcOdbc.SQLBindInParameterNull(JdbcOdbc.java:440)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.setNull(JdbcOdbcPreparedStatement.java:302)

company - ChiMu Corporation , email - ###@###.###

Name: krT82822 Date: 09/04/99


This is a problem I filed this week with an internal review ID of 94698. I found the same behaviour with 1.3, so I resubmit it as a beta issue.

The problem can be summarized in the following example:
   UPDATE TAB1 SET B=? WHERE A=?
   A and B can be null or not null

Using setNull() to set the value of B works fine. Using setNull() to set A
doesn't produce the expected result. I have the same problem with
   SELECT * FROM TAB1 where A=?
if A = null, the statement returns 0 rows (should return 1 row in my test case).
INSERT statements work fine. The problem seems to be restricted to the WHERE clause.
I am using JDK1.2.2 (same pb with 1.2.0 and 1.2.1). I tested the following piece
of code with an Oracle db (Oracle thin driver and JdbcDdbc drv) and MS Access
(JdbcOdbc drv). Seems to be a generic issue.

Note: a workaround like SELECT ... WHERE A IS NULL is not applicable. I need generic routines to process null and not null values.

Test case:
   drop table TAB1;
   create table TAB1 (A number, B number);
   insert into TAB1(A,B) values(1, 10);
   insert into TAB1(A,B) values(null, 20);
   commit;

Output:
   select: count=1 A=1 ...OK
   select: count=0 A=null ...wrong. should return count=1
   update: count=1 A=1 B=111 ...OK
   update: count=1 A=1 B=null ...OK
   update: count=0 A=null B=222 ...wrong. should return count=1

Test code:
import java.sql.*;
public class JdbcTest
{
   Connection conn = null;
   PreparedStatement stmt = null;

   // genericSelect()
   void genericSelect(Object a)
   {
      String sql = "select count(*) from TAB1 where A=?";
      try
      {
         stmt = conn.prepareStatement(sql);
         if (a == null) stmt.setNull(1, Types.INTEGER);
         else stmt.setObject(1, a);
         ResultSet rs = stmt.executeQuery();
         rs.next();
         System.out.println("select: count=" + rs.getObject(1) + " A=" + a);
      }
      catch (SQLException e)
      {
         System.out.println("ooops: " + e.getMessage());
      }
   }

   // genericUpdate()
   void genericUpdate(Object a, Object b)
   {
      final String sql = "update TAB1 set B=? where A=?";
      try
      {
         stmt = conn.prepareStatement(sql);
         if (b == null) stmt.setNull(1, Types.INTEGER);
         else stmt.setObject(1, b);
         if (a == null) stmt.setNull(2, Types.INTEGER);
         else stmt.setObject(2, a);

         int count = stmt.executeUpdate();
         System.out.println("update: count=" + count + " A=" + a + " B=" + b);
      }
      catch (SQLException e)
      {
         System.out.println("ooops: " + e.getMessage());
      }
   }

   // main()
   public static void main(String[] args)
   {
      JdbcTest appl = new JdbcTest();
      try
      {
         // register the driver
         Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         appl.conn = DriverManager.getConnection ("jdbc:odbc:Db1");
         appl.conn.setAutoCommit(false);
      }
      catch (Exception e)
      {
         System.out.println("Exception: " + e.getMessage());
      }

      appl.genericSelect(new Integer(1)); // works...
      appl.genericSelect(null); // doesn't work...

      appl.genericUpdate(new Integer(1), new Integer(111)); // works...
      appl.genericUpdate(new Integer(1), null); // works...
      appl.genericUpdate(null, new Integer(222)); // doesn't work...
   }
}

Thank you...
(Review ID: 94830)
======================================================================

Name: krT82822 Date: 11/07/99


java version "1.2.1"
Classic VM (build JDK-1.2.1-A, native threads)
This bug is the same I have already report.
The previous number is Review ID: 96541

I send you the code that generate de problem. It is very simple.
The problem is that the ResultSet.getBinaryStream() function don't
return null where the field in de DataBase is null. It returns an
InputStream with 0 bytes of length.

In the example send. It prints "Not Null", when it must print "Null".
This is only to show the problem.

The source code of the example is saw below.

/*
 * This example shows how to read stream data from the database
 */

import java.sql.*;
import java.io.*;

class StreamExample
{
  public static void main (String args [])
       throws SQLException, IOException
  {
    // Load the Oracle JDBC driver
    try {
      Class.forName ("oracle.jdbc.driver.OracleDriver");
    }
    catch (Exception e) {
      e.printStackTrace();
    }

    // Connect to the database
    Connection conn =
      DriverManager.getConnection("jdbc:oracle:thin:@1.100.0.62:1521:edu",
                                  "gratisfono","manager");

    // Not autocommit
    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    // drop the table is it exist
    try {
    stmt.execute("drop table streamexample2");
    }
    catch (Exception e) {
    }
    
    // create the table
    stmt.execute("create table streamexample2 (data long raw)");
    
    // insert null into table
    stmt.execute("insert into streamexample2 values (null)");

    // commit it
    conn.commit();

    // Do a query to get the row with NAME 'StreamExample'
    ResultSet rset=stmt.executeQuery("select data from streamexample2");
    
    // Get the first row
    if (rset.next ())
    {
      InputStream gif_data=rset.getBinaryStream(1);

      if (gif_data==null)
        System.out.println("Null");
      else
        System.out.println("Not null");
    }
  }
}
(Review ID: 97304)
======================================================================