How to retrieve column names from an in memory database?

Question

Here's the code that gives me headaches:

  public List<String> listColumnsForTable(String tableName) throws SQLException {
    List<String> columns = new ArrayList<String>();
    DatabaseMetaData metadata = _connection.getMetaData();
    ResultSet resultSet = metadata.getColumns(null, null, tableName, null);
    while (resultSet.next())
      columns.add(resultSet.getString("COLUMN_NAME"));
    return columns;
  }

This code works fine with SQL Server (I haven't checked with MySQL, Oracle or others), but I need to run some integration tests on an in memory database. All the databases I tried (h2, hsqldb and derby) fail.

Here is the link on github.

If you want the full project (with tests for h2, hsqldb, derby and sql server) do the following:

git clone git://github.com/sensui/InMemoryColumns.git
cd InMemoryColumns
gradlew

All the dependencies will be automatically downloaded. If you want to check the library versions look in the build.gradle script.

Now import the project in your favorite IDE (eclipse or idea).

The tests are available in the DatabaseMetadataCheckerTests class (canListColumnsForTable and canCheckIfColumnsExistInTable).

Normally you shouldn't modify those. I have created 4 test classes that provide connection details for each in memory database and you need to run those (the DatabaseMetadataCheckerTests is abstract so you don't run that).

NOTE: When/if you find a solution than the tests for that specific database will pass. You can easily try other databases like Oracle or MySQL just by extending the DatabaseMetadataCheckerTests class and providing the connection details (check the other tests).

Issue solved

The table names and column names should be in UPPERCASE. Check this commit for details.

Answer 1

H2, HSQLDB (as well as Oracle and DB2) comply with the SQL standard and thus unquoted object names are folded to uppercase (SQL Server does not do that, it keeps whatever case you used plus it might be configured to be not case sensitive for string comparisons).

create table foo (id integer) will be stored as FOO with the column name ID in the system catalog.

So you will need to pass the table name in uppercase to the JDBC API calls.

A note on porting this to other DBMS:

Postgres does not comply with the standard here and folds everything to lowercase

For MySQL there is no definite answer on how it does this. It depends on various configuration settings (and the storage engine, and the filesystem) so you can never be sure how an unquoted table name will actually be stored in the system.