How to get column names from a database in memory?

Question

Here is 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 have not tested it with MySQL, Oracle or others), but I need to run some integration tests in the memory database >. All databases that I tried (h2, hsqldb and derby) fail .

Here is a link to github .

If you need a complete project (with tests for h2, hsqldb, derby and sql server), follow these steps:

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

All dependencies will be automatically downloaded. If you want to check library versions, see build.gradle script.

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

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

Usually you should not change them. I created 4 test classes that provide connection information for each of the memory databases, and you need to run them ( DatabaseMetadataCheckerTests is abstract so you don't run it).

Note: When / if you find a solution than tests for this particular database, will pass . You can easily try other databases, such as Oracle or MySQL, by simply extending the DatabaseMetadataCheckerTests class and providing connection details (check other tests).

Problem resolved

table names and column names must be in UPPERCASE . See this commit for more details.

Answer 1

H2, HSQLDB (as well as Oracle and DB2) comply with the SQL standard, and therefore names without quotes are uppercase (SQL Server does not do this, it holds everything you used, plus it can be configured as case-insensitive for string comparisons )

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

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

Note on porting this to another DBMS:

Postgres is not up to standard here and collapses everything to lowercase

For MySQL, there is no definite answer to how it does it. It depends on various configuration parameters (both the storage engine and the file system), so you can never be sure how the name will be stored without quotes in the system.