Hi,
> The same works in HSQL, Oracle, MySQL, and MSSQL.
Yes, but those databases don't return the same rows (see also my test
case below).
H2 uses \ as the default escape character (like PostgreSQL and MySQL).
However H2 throws an exception if the escape character is not followed
by \, % or _ (like Apache Derby, if it would use \ as the default
escape character). PostgreSQL and MySQL don't throw an exception in
this case.
The LIKE ESCAPE processing is quite tricky. And unfortunately, all
databases work a bit different. I will change the behavior of H2 to
match PostgreSQL, and, I believe, Oracle and MS SQL Server.
Results from my test case:
jdbc:mysql://localhost:3306/test
result1: 0
result2: 1
result3: 1
jdbc:postgresql:test
result1: 0
result2: 0
result3: 0
jdbc:derby:test;create=true
result1: 1
result2: java.sql.SQLDataException: Escape character must be followed
by escape character, '_', or '%'. It cannot be followed by any other
character or be at the end of the pattern.
result3: 0
jdbc:hsqldb:test;hsqldb.default_table_type=cached
result1: 1
result2: java.sql.SQLException: data exception: invalid escape sequence
result3: java.sql.SQLException: data type cast needed for parameter or
null literal
jdbc:h2:data/test;page_store=true (next release)
result1: 0
result2: 0
result3: 0
My test case:
package db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.h2.tools.DeleteDbFiles;
public class TestMultiDb {
public static void main(String[] a) throws Exception {
DeleteDbFiles.execute("data/test", "test", true);
test("com.mysql.jdbc.Driver",
"jdbc:mysql://localhost:3306/test",
"sa", "sa");
test("org.postgresql.Driver",
"jdbc:postgresql:test",
"sa", "sa");
test("org.apache.derby.jdbc.EmbeddedDriver",
"jdbc:derby:test;create=true", "sa", "sa");
test("org.hsqldb.jdbcDriver",
"jdbc:hsqldb:test;hsqldb.default_table_type=cached",
"sa", "");
test("org.h2.Driver",
"jdbc:h2:data/test;page_store=true", "sa", "sa");
}
static void test(String driver,
String url, String user,
String password) throws Exception {
Class.forName(driver);
Connection conn = DriverManager.getConnection(
url, user, password);
ResultSet rs;
Statement stat = conn.createStatement();
try {
stat.execute("drop table test");
} catch (SQLException e) {
// ignore
}
System.out.println(url);
stat.execute(
"create table test(name varchar(255))");
PreparedStatement prep;
prep = conn.prepareStatement(
"insert into test values(?)");
prep.setString(1, "abc+mike\\+");
prep.execute();
try {
prep = conn.prepareStatement(
"select count(*) from test where name like ?");
prep.setString(1, "abc+mike\\+");
rs = prep.executeQuery();
rs.next();
System.out.println("result1: " + rs.getString(1));
} catch (SQLException e) {
System.out.println("result1: " + e);
}
try {
prep = conn.prepareStatement(
"select count(*) from test where name like ? escape '+'");
prep.setString(1, "abc++mike\\+");
rs = prep.executeQuery();
rs.next();
System.out.println("result2: " + rs.getString(1));
} catch (SQLException e) {
System.out.println("result2: " + e);
}
try {
prep = conn.prepareStatement(
"select count(*) from test where name like '%%' escape ?");
prep.setString(1, "%");
rs = prep.executeQuery();
rs.next();
System.out.println("result3: " + rs.getString(1));
} catch (SQLException e) {
System.out.println("result3: " + e);
}
conn.close();
}
}
Regards,
Thomas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
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.
