Name: krT82822			Date: 02/14/2000


java version "1.3.0rc1"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.3.0rc1-T)
Java HotSpot(TM) Client VM (build 1.3.0rc1-S, mixed mode)

This problem was submitted previously, and evaluated as a
duplicate of bug #4029237, which is supposed to be fixed in kestrel.
After testing again with the jdk 1.3 rc1, I found out that the problem is NOT
fixed.  [Perhaps it is fixed in the final kestrel, but that cannot be determined from RC1.]

Again, the problem is limited to bind variables in the WHERE clause that can be
either (sql)null or not (sql)null. Using setNull() to set null variables in
other parts of a sql statement works fine.

By the way: I doubt that it is a bridge issue. I have the same problem using the
Oracle Thin Driver against an oracle db. seems to be a generic parsing problem.

The test case is still in your bug db.

-----------------------------

from previous report (#94830):

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: 101208) 
======================================================================