#1364 Column names are case sensitive when returning Generated Keys

[Brian]

Hi,

I'm new to using HSQLDB. I've found what may or may not be a bug, depending on your point of view, but I do know that this behavior is inconsistent relative to SQLite, Oracle, and Postgres.

When you execute an insert that will return generated keys, if the String array of column names is not capitalized, HSQLDB will fail to find the columns for that table and will fail to execute the statement (and return the new primary keys).

I've tried to

 CREATE SEQUENCE seq START WITH 1 INCREMENT BY 1;

 create table FolderTable (
     FolderId integer GENERATED BY DEFAULT AS SEQUENCE seq,
     Name varchar(50),
     Parent integer
 );

 insert into FolderTable (FolderId, Name, Parent)
       values(0, 'ROOT', NULL);

Java:

 String sql = "INSERT INTO FolderTable (Name,Parent) VALUES (?,?)";
 String[] retColumns = new String[]{"FolderId"};
 try(PreparedStatement stmt = _conn.prepareStatement( sql, retColumns )) {
     stmt.setString( "etc" );
     stmt.setLong( 0L );
     stmt.executeUpdate();
     try(ResultSet rs = stmt.getGeneratedKeys()){
         rs.next();
         return rs.getLong(1);
     }
 }

By making "FolderId" upper-case, it works fine, aka:

 String[] retColumns = new String[]{"FOLDERID"};
 // etc...

[Fred Toussi]

As you can have both FOLDERID and "FolderId" columns in the same table, the behaviour is correct and ensures the column is referenced correctly.

[Piotr Gwiazda]

Fred,
The OP is right. Please double check above again. When you use "FolderId" using the case as in the table it does not work.
It starts working when you pass uppercase column name "FOLDERID" to prepared statement while leaving "FolderId" in db schema.

If you had both FOLDERID and "FolderId" columns in the same table, wrong column would be referenced.

Please reopen.

[Fred Toussi]

create table FolderTable (FolderId integer ... is the same as create table FOLDERTABLE (FOLDERID integer ... as unquoted names are turned into uppercase in SQL.

But if you use double quotes around names in SQL, they become case sensitive:

create table "FolderTable" (
"FolderId" integer ...

In JDBC you use the exact case that is created by SQL: uppercase if originally unquoted, exact mixed-case when originally quoted.

[Brian]

Hi,

I think the confusion, in my opinion, arises in that the DDL accepts/interprets the column names, and subsequent DML statements would accept/interpret them just fine as well, whereas the return column is not correctly interpreted or accepted.

Again, it is in other DBMS - I use now use HSQLDB for unit tests and test deployments, while I have integration tests in other databases. The code above works just fine in the other DBMSs.

Java:

String sql = "INSERT INTO FolderTable (Name,Parent) VALUES (?,?)";

// Doesn't work
String[] retColumns = new String[]{"FolderId"};
try(PreparedStatement stmt = _conn.prepareStatement( sql, retColumns )) {
    stmt.setString(1, "etc" );
    stmt.setLong(2, 0L );
    stmt.executeUpdate();
    try(ResultSet rs = stmt.getGeneratedKeys()){
        rs.next();
        return rs.getLong(1);
    }
} catch (SQLException ex){ /* do nothing */ }

// Works
String[] retColumns = new String[]{"FOLDERID"};
try(PreparedStatement stmt = _conn.prepareStatement( sql, retColumns )) {
    stmt.setString(1, "etc" );
    stmt.setLong(2, 0L );
    stmt.executeUpdate();
    try(ResultSet rs = stmt.getGeneratedKeys()){
        rs.next();
        return rs.getLong(1);
    }
}

// Works
String selSql = "SELECT FolderId FROM FolderTable WHERE Name = ?";
try(PreparedStatement stmt = _conn.prepareStatement(selSql)) {
    stmt.setString(1, "etc");
    ResultSet rs = stmt.execute();
    System.out.println(rs.getLong("FolderId")); /* This does work */
}

[Fred Toussi]

SQL statements are translated to their normal form according to well-defined rules which enforce case-sensitivity when names are double quoted. In JDBC case-sensitivity is generally used to distinguish between two names that are different only in case. In this case the behaviour is correct but we may introduce further relaxations when there is no ambiguity in the column reference.