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.

Mark Rotteveel · Accepted Answer · 2015-06-01T10:44:05.363

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);

Interesting, should this be considered a bug? As http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getSchemas() would seem to imply that it should return a ResultSet with the sechema names. — Michael Hobbs, Jun 01 '15 at 22:51
@MichaelHobbs Probably not, the definition of schema and catalog is rather varied between database systems. What MySQL calls schemas is more like catalogs defined in the SQL standard. It is confusing that the terminology doesn't map with what is used within MySQL. — Mark Rotteveel, Jun 02 '15 at 09:51
I filled a bug report and they came back and basically said that this was a long standing issues that goes way back to the early days. It is like you said, comes down to how schema/catalogs were defined. Still seems odd they would have a function that basically does nothing. — Michael Hobbs, Jun 02 '15 at 23:58
@MichaelHobbs I guess the decision to use `getCatalog` predated the MySQL implementation of the information schema and call databases schemas instead of catalogs. It is pretty hard to reverse such a decision as it will break applications that already depend on it. The JDBC specification explicitly says that metadata that doesn't exist or is not supported should return an empty result set. — Mark Rotteveel, Jun 03 '15 at 06:11

score 1 · Answer 2 · answered Feb 01 '21 at 04:54

I realize this is an old post, but it comes up in a Google search on this topic, and the answer here did not work for me.

After much more digging, I found the correct way to do this.

First, you can get a list of all the metadata table types by doing this

    DatabaseMetaData metaData = connection.getMetaData();
    ResultSet rs = metaData.getTableTypes();
    while (rs.next()) {
        System.out.println(rs.getString(1));
    }

The table type that we are interested in is simply called TABLE.

And this code will pull up all the names of the schema's in the SQL server, assuming your credentials have the rights to view them.

    DatabaseMetaData metaData = connection.getMetaData();
    ResultSet rs = metaData.getTables(null, null, null, new String[]{"TABLE"});
    while(rs.next())
    {
        System.out.println(rs.getString(1));
    }

It worked for me anyways with Java 8 and mysql-connector-java-8.0.22

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

2 Answers2