>>>>>>>>>>>> [EMAIL PROTECTED] 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
pgpvDPSrA88TT.pgp
Description: PGP signature
