JDBC DatabaseMetaData.getColumns() returns duplicate columns

Question

I'm busy on a piece of code to get alle the column names of a table from an Oracle database. The code I came up with looks like this:

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection(
  "jdbc:oracle:thin:@<server>:1521:<sid>", <username>, <password>);

DatabaseMetaData meta = conn.getMetaData();
ResultSet columns = meta.getColumns(null, null, "EMPLOYEES", null);
int i = 1;
while (columns.next())
{
  System.out.printf("%d: %s (%d)\n", i++, columns.getString("COLUMN_NAME"), 
    columns.getInt("ORDINAL_POSITION"));
}

When I ran this code to my surprise too many columns were returned. A closer look revealed that the ResultSet contained a duplicate set of all the columns, i.e. every column was returned twice. Here's the output I got:

1: ID (1)
2: NAME (2)
3: CITY (3)
4: ID (1)
5: NAME (2)
6: CITY (3)

When I look at the table using Oracle SQL Developer it shows that the table only has three columns (ID, NAME, CITY). I've tried this code against several different tables in my database and some work just fine, while others exhibit this weird behaviour.

Could there be a bug in the Oracle JDBC driver? Or am I doing something wrong here?

---

Update: Thanks to Kenster I now have an alternative way to retrieve the column names. You can get them from a ResultSet, like this:

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@<server>:1521:<sid>", <username>, <password>);

Statement st = conn.createStatement();
ResultSet rset = st.executeQuery("SELECT * FROM \"EMPLOYEES\"");
ResultSetMetaData md = rset.getMetaData();
for (int i=1; i<=md.getColumnCount(); i++)
{
    System.out.println(md.getColumnLabel(i));
}

This seems to work just fine and no duplicates are returned! And for those who wonder: according to this blog you should use getColumnLabel() instead of getColumnName().

Restless

What happens if you look a meta.getTables(null, null, "EMPLOYEES", null)? Are you getting only one table or more than one? – Glidden

Answer 1

In oracle, Connection.getMetaData() returns meta-data for the entire database, not just the schema you happen to be connected to. So when you supply null as the first two arguments to meta.getColumns(), you're not filtering the results for just your schema.

You need to supply the name of the Oracle schema to one of the first two parameters of meta.getColumns(), probably the second one, e.g.

meta.getColumns(null, "myuser", "EMPLOYEES", null);

It's a bit irritating having to do this, but that's the way the Oracle folks chose to implement their JDBC driver.

Besse

Thanks. I'll try your solution tomorrow. However, during one of my debugging sessions I also printed the values of "TABLE_SCHEM" and for every column they were the same... So I hope filtering on that will work. – Restless

Answer 2

This doesn't directly answer your question, but another approach is to execute the query:

select * from tablename where 1 = 0

This will return a ResultSet, even though it doesn't select any rows. The result set metadata will match the table that you selected from. Depending on what you're doing, this can be more convenient. tablename can be anything that you can select on--you don't have to get the case correct or worry about what schema it's in.

Waltner

Wow, thanks! As a matter of fact I was trying to get the column names from a ResultSet in the first place, because that's the way I'm used to do it in ADO.Net (i.e. retrieve Fields from a RecordSet). But I didn't know how to retrieve column data from a ResultSet. Then I found the approach using DatabaseMetaData.getColumns(). However, your answer put me in the right direction. I had totally missed the ResultSet.getMetaData() method, which is perfect for my solution. I'll update the question above. – Restless

Answer 3

In the update to your question I noticed that you missed one key part of Kenster's answer. He specified a 'where' clause of 'where 1 = 0', which you don't have. This is important because if you leave it off, then oracle will try and return the ENTIRE table. And if you don't pull all of the records over, oracle will hold unto them, waiting for you to page through them. Adding that where clause still gives you the metadata, but without any of the overhead.

Also, I personally use 'where rownum < 1', since oracle knows immediately that all rownums are past that, and I'm not sure if it's smart enough to not try and test each record for '1 = 0'.

Seth

Answer 4

In addition to skaffman's answer -

use the following query in Oracle:

select sys_context( 'userenv', 'current_schema' ) from dual;  

to access your current schema name if you are restricted to do so in Java.

Lancet

Answer 5

This is the behavior mandated by the JDBC API - passing nulls as first and second parameter to getColumns means that neither catalog name nor schema name are used to narrow the search. Link to the documentation . It is true that some other JDBC drivers have different behavior by default (e.g MySQL's ConnectorJ by default restricts to the current catalog), but this is not standard, and documented as such

Aspasia