db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bernt M. Johnsen" <Bernt.John...@Sun.COM>
Subject Re: LIKE with prepared statements and empty strings
Date Tue, 18 Apr 2006 14:58:05 GMT
>>>>>>>>>>>> Dyre.Tjeldvoll@Sun.COM wrote (2006-04-18 16:45:04):
> 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?

I would say no. The two statements should have given the same
result. I guess the first one is correct, but since LIKE may have some
weird semantics I would not bet on it without consulting the spec.

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

-- 
Bernt Marius Johnsen, Database Technology Group, 
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway

Mime
View raw message