Difficulty getting PK in HSQLDB

I'm using hsqldb for a unit test (NOT production). These tests need to access the table's primary key, but I cant seem to get it to work. BTW, I'm using the latest version 2.0.0

I've created a small snippet to recreate the problem. Any feedback would be greatly appreciated

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Test {
    public static void main(String[] args) throws Exception {
        Connection conn = getHSQLConnection();
        System.out.println("Got Connection.");
        Statement st = conn.createStatement();
        st.executeUpdate("create table survey (id int,name varchar (20), PRIMARY KEY (id) );");
        ResultSet rs = null;
        DatabaseMetaData meta = conn.getMetaData();
        rs = meta.getPrimaryKeys(null, null, "survey");
        String pk = null;
        if (rs.next()) {
            pk = rs.getString("COLUMN_NAME");
            System.out.println("getPrimaryKeys(): columnName=" + pk);
        }
        else {
            System.out.println("Couldn't get the PK");
        }
        st.close();
        conn.close();
    }

    private static Connection getHSQLConnection() throws Exception {
        Class.forName("org.hsqldb.jdbcDriver");
        System.out.println("Driver Loaded.");
        String url = "jdbc:hsqldb:data/test1";
        return DriverManager.getConnection(url, "sa", "");
    }
}开发者_如何学运维

Use this:

rs = meta.getPrimaryKeys(null, null, "SURVEY");

In standard SQL, the name of a column, table or other object is turned into all uppercase unless you use double quotes around the name when you use it in SQL statements.

The latest version is currently 2.2.3 and has bug fixes and improvments over version 2.0.0