Java DatabaseMetaData.getSchemas() returns empty ResultSet, expected non-empty ResultSet

Question

Trying to understand what is going on here. DatabaseMetaData is returning an empty result set, where as an SQL query that is effectively the same does not. Not a major issue as I am using the second code example as a work around.

DatabaseMetaData dmd = this.connection.getMetaData();
ResultSet rs = dmd.getSchemas();
while (rs.next()){
  // empty result set
}

Expected a non-empty result set.

ResultSet rs = this.connection.prepareStatement("SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;").executeQuery();
while (rs.next()){
  // non-empty result set with expected results
}

Expected a non-empty result set and got it.

Answer 1

As far as I can tell the MySQL JDBC driver considers that a catalog, not a schema. So you should use getCatalogs instead (and everywhere you use it, you need to use the catalog parameter, not the schema parameter).

The getSchemas method in Connector/J always returns an empty result set:

public java.sql.ResultSet getSchemas() throws SQLException {
    Field[] fields = new Field[2];
    fields[0] = new Field("", "TABLE_SCHEM", java.sql.Types.CHAR, 0);
    fields[1] = new Field("", "TABLE_CATALOG", java.sql.Types.CHAR, 0);

    ArrayList<ResultSetRow> tuples = new ArrayList<ResultSetRow>();
    java.sql.ResultSet results = buildResultSet(fields, tuples);

    return results;
}

The getCatalogs returns the result of SHOW DATABASES. And in DatabaseMetaDataUsingInfoSchema you see the TABLE_SCHEMA column of the information schema aliased as TABLE_CAT (for catalog) and the catalog parameter being passed as a value for the TABLE_SCHEMA column in the query:

String sql = "SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME,"
        + "COLUMN_NAME, NULL AS GRANTOR, GRANTEE, PRIVILEGE_TYPE AS PRIVILEGE, IS_GRANTABLE FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE "
        + "TABLE_SCHEMA LIKE ? AND TABLE_NAME =? AND COLUMN_NAME LIKE ? ORDER BY COLUMN_NAME, PRIVILEGE_TYPE";

java.sql.PreparedStatement pStmt = null;

try {
    pStmt = prepareMetaDataSafeStatement(sql);

    if (catalog != null) {
        pStmt.setString(1, catalog);
    } else {
        pStmt.setString(1, "%");
    }

    pStmt.setString(2, table);
    pStmt.setString(3, columnNamePattern);