CODEWITHSUNDEEP
javajdbc
praveen_mohan
praveen_mohan

Reputation: 465

Not able to retrive all the tables attributes present in the database using databasemetadata

I am trying to access databasemetadata to retrieve all tables present in it. getTables should return a ResultSet of 10 columns.

When I try to iterate over the ResultSet to fetch all the column data, I get

Exception in thread "main" java.sql.SQLException: Invalid column index

I retrieve the first 5 column data from the first row, then the exception occurs when printing the 6th column, TYPE_CAT.

Relevant Code:

DatabaseMetaData dbmd = connection1.getMetaData();
    ResultSet rs = dbmd.getTables(null, null, null, new String[]{"TABLE"});
        while(rs.next()){
            for(int i=1;i<11;i++){
                System.out.print(rs.getString(i)+"  ");
            }
            System.out.println("");
        }

Upvotes: 3

Views: 1194

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 108939

It looks like you are either using a very old JDBC 2 driver, or the driver is not compliant with JDBC 3.0, 4.0 and/or 4.1 by not having all columns required by the specification.

Although JDBC specifies the columns the resultset (at minimum) should return, it is upto the driver implementer to actually correctly define the ResultSet and its values. JDBC is 'just' a specification and a set of interfaces, so it can not actually make sure - at runtime - that all drivers return all specified columns. There are certification tests but these require bureaucratic hurdles (opensource) or a hefty sum of money + bureaucratic hurdles (commercial), so not all JDBC driver implementers bother to test for compliance.

In JDBC 2 (Java 1.3) DatabaseMetaData.getTables (links to Java 1.3 apidoc) returns only five columns:

  1. TABLE_CAT String => table catalog (may be null)
  2. TABLE_SCHEM String => table schema (may be null)
  3. TABLE_NAME String => table name
  4. TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
  5. REMARKS String => explanatory comment on the table

Whereas the JDBC 3.0 (Java 1.4 / Java 5), JDBC 4.0 (Java 6) and JDBC 4.1 (Java 7) defines 5 more columns:

  1. TABLE_CAT String => table catalog (may be null)
  2. TABLE_SCHEM String => table schema (may be null)
  3. TABLE_NAME String => table name
  4. TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
  5. REMARKS String => explanatory comment on the table
  6. TYPE_CAT String => the types catalog (may be null)
  7. TYPE_SCHEM String => the types schema (may be null)
  8. TYPE_NAME String => type name (may be null)
  9. SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed table (may be null)
  10. REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED". (may be null)

You might want to check if there is a newer driver available for your database.

Upvotes: 1

JoshDM
JoshDM

Reputation: 5052

Rather than use your value of 11, try getting the ResultSetMetaData and determining the column count to confirm your columns retrieved:

ResultSetMetaData rsMetaData = rs.getMetaData();
int numberOfColumns = rsMetaData.getColumnCount();

From it, you can get column names and values and confirm what you are retrieving using the getColumnName(int) method.

Upvotes: 0

Related Questions