Is it true that the "ResultSet.getMetaData.getTableName (col)" of the postgresql jdbc driver always returns an empty string?

Question

When I use postgresql, I found the following code:

Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select * from t"); String tableName = rs.getMetaData().getTableName(1); System.out.println(tableName); 

It prints an empty line.

So, I checked the source code and found that the org.postgresql.jdbc2.AbstractJdbc2ResultSetMetaData#getTableName always returns an empty string.

Source:

 public abstract class AbstractJdbc2ResultSetMetaData implements PGResultSetMetaData { /* * @param column the first column is 1, the second is 2... * @return column name, or "" if not applicable * @exception SQLException if a database access error occurs */ public String getTableName(int column) throws SQLException { return ""; } } 

You can see that it simply returns a "" .

I found a discussion about this, see: http://archives.postgresql.org/pgsql-jdbc/2009-12/msg00100.php

They think "rs.getMetaData.getTableName (col)" should return an alias in the query not the name of the base table. But it’s hard to implement, so it’s better to leave it blank.

They also gave a method to get the table name, use:

 PGResultSetMetaData.getBaseTableName() 

Example:

 ResultSet rs = stmt.executeQuery("select * from x"); // convert it to PGResultSetMetaData PGResultSetMetaData meta = (PGResultSetMetaData)rs.getMetaData(); String tableName = meta.getBaseTableName(1); 

Now it can print the correct table name.

I do not know that the postgresql implementation is correct, but returning the name of the base table is much more useful than an empty string, and most other databases provide the base name of the table instead of the empty string.

I have a problem using the play2 anorm framework with postgesql: Anonymous Anonymous 2 does not work on postgresql , but it works well in other databases.

What do you think is the correct implementation of the postgresql jdbc driver? Return empty string, base table name, or something else?

Answer 1

I would say that returning an empty row is obviously an incorrect implementation of the interface, since the table name can never be considered an empty string.

The problem that I think they are struggling with is that, although their current implementation is wrong, after they choose the implementation, they will depend on it until it decides that behavior-based violations are acceptable. Therefore, they prefer to add a method whose name is unambiguous, and provide the data that most users expected to get from getTableName , and leave the implementation of the getTableName method clearly violated until a certain consensus is reached on whether it should return or until introduced a patch that implements consensus.

My gut reaction is that the getTableName method should return the alias used for this table. A table can be combined with itself, and using an alias allows you to determine what is referenced. A table could be formed in a query (for example, dropping an array), and therefore it does not even have a table name in the database. If you make the decision "absolutely always, getTableName returns an alias", then at least users know what to expect; otherwise, you will ultimately not be obvious that the method should return.

However, even if I assume that my gut reaction is the “correct implementation”, a compatibility problem arises. It is advisable that you can switch from another DBMS to PostgreSQL with minimal investment, if one of the goals of PostgreSQLs will grow in popularity. So things like "how do other JDBC implement java.sql interfaces?" become relevant. As you say, there is a framework that has hopes for how ResultSetMetaData should be implemented, and most likely it is not the only one that has certain expectations about how the java.sql interfaces will be implemented.

Whatever implementation they choose, it will be a compromise, so I can understand why the “hit down the road” is their choice. When they choose the compromise they want to make, they are blocked.

EDIT: I would suggest that an exception due to not implemented would be better than just silently. I expect that frameworks that rely on a specific getTableName implementation will not make much sense for an empty string anyway, and either the error itself or they themselves fail.

Answer 2

it looks like this will be changed in the next version: https://github.com/pgjdbc/pgjdbc/pull/107