I've discovered a bug in the JDBC driver. When I use PreparedStatement to do an insert, and I insert an empty string (""), then the string is stored as a string with spaces. If I test for it with a where clause, where column = '' or column is null, it doesn't find it. But if I insert using the Statement.execute, it works as expected. With sqlmode=oracle, and using Statement.execute, an empty string will be inserted as a null. Which is just what oracle does. But with the PreparedStatement, it does not insert null.
Dave Polito
Here is some sample code;
//SQL Statement
create table testtable (a varchar(20), b varchar(20))
// TestJDBC.java
//
//
//
import java.sql.*;
public class TestJDBC
{
public static void main(String [] args)
throws ClassNotFoundException, SQLException
{
String user = "username";
String password = "password";
String host = "hostname";
String dbname = "SID";
String url = "jdbc:sapdb://" + host + "/" + dbname + "?sqlmode=oracle";
Class.forName("com.sap.dbtech.jdbc.DriverSapDB");
Connection connection = DriverManager.getConnection(url, user, password);
Statement stmt = connection.createStatement();
stmt.execute("delete from testtable");
PreparedStatement statement = connection.prepareStatement("insert into testtable values ( ?, ?)");
statement.setString(1, "NULL");
statement.setString(2, null);
statement.execute();
statement.setString(1, "EMPTY WITH PREPSTMT");
statement.setString(2, "");
statement.execute();
stmt.execute("insert into testtable values ('EMPTY WITH INSERT', '')");
statement.setString(1, "SPACE");
statement.setString(2, " ");
statement.execute();
statement.setString(1, "SPACES");
statement.setString(2, " ");
statement.execute();
statement.close();
ResultSet rset = stmt.executeQuery("select * from testtable");
while( rset.next())
{
System.out.print("A=-" + rset.getString(1) + "-");
System.out.println(" B=-" + rset.getString(2) + "-");
}
rset.close();
rset = stmt.executeQuery("select * from testtable where b is null");
while( rset.next())
{
System.out.print("A=" + rset.getString(1) + "-");
System.out.println(" B=-" + rset.getString(2) + "-");
}
rset.close();
stmt.close();
connection.close();
}
}
******* OUTPUT ********
A=-NULL- B=-null-
A=-EMPTY WITH PREPSTMT- B=--
A=-EMPTY WITH INSERT- B=-null-
A=-SPACE- B=--
A=-SPACES- B=--
TEST--------------------------------------------------------
A=-NULL- B=-null-
A=-EMPTY WITH INSERT- B=-null-
