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