Search code examples
javahsqldb

HSQLDB ignore case

Having this code in java:

Connection c = jdbcDriver.getConnection("jdbc:hsqldb:mem:test;sql.ignore_case=true", null);
Statement stmt = c.createStatement();
stmt.executeUpdate("CREATE TABLE \"TEST\"(ID INT)");
stmt.executeQuery("SELECT id FROM \"test\";");

Throws a org.hsqldb.HsqlException

Exception in thread "main" java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: test
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.JDBCStatement.executeQuery(Unknown Source)
at Test.main(Test.java:15)
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: test
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.ParserDQL.readTableName(Unknown Source)
at org.hsqldb.ParserDQL.readTableOrSubquery(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableReference(Unknown Source)
at org.hsqldb.ParserDQL.XreadFromClause(Unknown Source)
at org.hsqldb.ParserDQL.XreadTableExpression(Unknown Source)
at org.hsqldb.ParserDQL.XreadQuerySpecification(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleTable(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryPrimary(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryTerm(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpressionBody(Unknown Source)
at org.hsqldb.ParserDQL.XreadQueryExpression(Unknown Source)
at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatements(Unknown Source)
at org.hsqldb.Session.executeDirectStatement(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 3 more

But why? I said ignore_case=true!

See this link: http://hsqldb.org/doc/guide/dbproperties-chapt.html#N15A0A

Solution

  • Remove the " (quotes)

    By default, without the quotes, the names are case-insensitive. But you put the table names between quotes, so they are registered exactly as is, case-sensitive. The two table names are then different and HSQLDB won't match them.

    This will work:

    import java.sql.*;

public class Main {
  public static void main(String[] args) throws Exception {
    Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:test", "SA", "");
    Statement stmt = c.createStatement();
    stmt.executeUpdate("CREATE TABLE TEST(ID INT)");
    stmt.executeQuery("SELECT id FROM test;");
  }
}

    What does sql.ignore_case do, then?

    It makes sure that the varchar comparison is case-insensitive. For instance abc will be matched with ABC:

    import java.sql.*;

public class Main {
  public static void main(String[] args) throws Exception {
    Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:test;sql.ignore_case=true", "SA", "");
    Statement stmt = c.createStatement();
    stmt.executeUpdate("CREATE TABLE TeSt(NAME VARCHAR(99))");
    stmt.executeUpdate("INSERT INTO tEsT (NAME) VALUES ('ABC')");
    ResultSet rs = stmt.executeQuery("SELECT * FROM TesT WHERE NAME = 'abc'");
    rs.next();
    System.out.println(rs.getString(1)); // prints: ABC
  }
}