db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Dyre.Tjeldv...@Sun.COM
Subject LIKE with prepared statements and empty strings
Date Tue, 18 Apr 2006 14:45:04 GMT
I puzzled by the following:

The query SELECT * FROM t WHERE v like ''

appears to give me all rows where v is an empty
string, as expected.

But a prepared statement

SELECT * FROM t WHERE v like ?, with setString(1, "")

always gives zero rows. Is this expected?

Detailed example:
	conn.setAutoCommit(true);

	Statement s = conn.createStatement();
	try { s.execute("drop table t"); } catch (SQLException e) {}
	s.execute("create table t (i int, v varchar(128))");
	
	PreparedStatement ins = 
	    conn.prepareStatement("insert into t values (?, ?)");
	
	ins.setInt(1,0);
	ins.setString(2, "ABC");
	ins.execute();

	ins.setInt(1,1);
	ins.setString(2, "XYZ");
	ins.execute();

	ins.setInt(1,2);
	ins.setString(2, "");
	ins.execute();

	PreparedStatement ok = 
	    conn.prepareStatement("select * from t where v like ''");
	ResultSet ok_rs = ok.executeQuery();
	while (ok_rs.next()) {
	    System.out.println("ok: "+ok_rs.getObject(1) + " <" + 
			       ok_rs.getObject(2) + ">");
	}

	PreparedStatement bad = 
	    conn.prepareStatement("select * from t where v like ?");
	bad.setString(1, "");
	ResultSet bad_rs = bad.executeQuery();
	while (bad_rs.next()) {
	    System.out.println("bad: "+bad_rs.getObject(1) + " <" + 
			       bad_rs.getObject(2) + ">");
	}

This prints:

ok: 2 <>

-- 
dt


Mime
View raw message