Attached is a test case that demonstrates a difference in how where clause
handles NULL. In the first case, my SELECT includes "WHERE c IS NULL" My
second case has "WHERE c=?" and then I do preparedStatement.setNull(1,
Types.VARCHAR). The queries return different results. There must be some
difference in the semantics of the two queries but I don't know what it
is. Can someone explain it? Thanks
(apologize if this is a dup, don't see the original)
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/h2-database/-/tRdj7J5WeWoJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;
import org.junit.Before;
import org.junit.Test;
public class TestCase
{
private Connection connection;
@Test
public void myTest() throws Exception
{
{
PreparedStatement prepareStatement = this.connection.prepareStatement("SELECT DUMMY_TABLE_QUERY_ID, DUMMY_TABLE_QUERY_NAME, DUMMY_TABLE_QUERY_DESCRIPTION FROM DUMMY_TABLE_QUERY WHERE DUMMY_TABLE_QUERY_NAME IS NULL");
ResultSet rs = prepareStatement.executeQuery();
System.out.println("Query IS NULL:");
while (rs.next())
{
System.out.println(rs.getString("DUMMY_TABLE_QUERY_ID")
+ "\t"
+ rs.getString("DUMMY_TABLE_QUERY_NAME"));
}
rs.close();
prepareStatement.close();
}
{
PreparedStatement prepareStatement = this.connection.prepareStatement("SELECT DUMMY_TABLE_QUERY_ID, DUMMY_TABLE_QUERY_NAME, DUMMY_TABLE_QUERY_DESCRIPTION FROM DUMMY_TABLE_QUERY WHERE DUMMY_TABLE_QUERY_NAME=?");
prepareStatement.setNull(1, Types.VARCHAR);
ResultSet rs = prepareStatement.executeQuery();
System.out.println("Query = ? (null):");
while (rs.next())
{
System.out.println(rs.getString("DUMMY_TABLE_QUERY_ID")
+ "\t"
+ rs.getString("DUMMY_TABLE_QUERY_NAME"));
}
rs.close();
prepareStatement.close();
}
}
@Before
public void setup() throws Exception
{
Class.forName("org.h2.Driver");
this.connection = DriverManager.getConnection("jdbc:h2:mem:dataset");
Statement statement = this.connection.createStatement();
statement.execute("create table DUMMY_TABLE_QUERY\r\n"
+ "(\r\n"
+ " DUMMY_TABLE_QUERY_ID VARCHAR(128) PRIMARY KEY,\r\n"
+ " DUMMY_TABLE_QUERY_NAME VARCHAR(256),\r\n"
+ " DUMMY_TABLE_QUERY_DESCRIPTION VARCHAR(1024),\r\n"
+ " DUMMY_TABLE_QUERY_DISPLAY_NAME VARCHAR(256),\r\n"
+ ");");
statement.execute("INSERT INTO DUMMY_TABLE_QUERY(DUMMY_TABLE_QUERY_ID,DUMMY_TABLE_QUERY_DESCRIPTION) VALUES ('1','DESC1')");
statement.execute("INSERT INTO DUMMY_TABLE_QUERY(DUMMY_TABLE_QUERY_ID,DUMMY_TABLE_QUERY_DESCRIPTION) VALUES ('2','DESC2')");
statement.close();
}
}
