Re: metadata.getColumns() using Apache Tomcat / jndi - Mailing list pgsql-jdbc

Thomas Strunz wrote:
> Hi all,
>
> issue affects 9.1-901.jdbc4 and 9.0-801.jdbc4 driver.
>
> i have following java code:
>
> <pre>
> Connection conn = dataSource.getConnection();
> DatabaseMetaData metaData = conn.getMetaData();
> this.databaseProductName = metaData.getDatabaseProductName();
> ResultSet rsColumns = metaData.getColumns(
> null, null, tableName, null);
> while (rsColumns.next()) {
> //..do work
> }
> rsColumns.close();
> conn.close();
> </pre>
>
> This works fine when initializing the PostgreSQL DataSoource manually as in:
> ~
 > ~
 > ~
> the result set returned by metaData.getColumns(null, null, tableName,
> null) is always empty.
>
> However if I use MySQL, SQL Server or HSQLDB the code also works in
> tomcat eg.

> Any ideas? Bug? the problem does seem to be a postgresql issue (and not
> tomcat) because code works fine for other JDBC Drivers.
>
> Thanks for your help.

According to the Java 6 API for getColumns():

Parameters:
     catalog - a catalog name; must match the catalog name as it is stored in
the database; "" retrieves those without a catalog; null means that the catalog
name should not be used to narrow the search
     schemaPattern - a schema name pattern; must match the schema name as it is
stored in the database; "" retrieves those without a schema; null means that the
schema name should not be used to narrow the search
     tableNamePattern - a table name pattern; must match the table name as it is
stored in the database
     columnNamePattern - a column name pattern; must match the column name as it
is stored in the database

You will notice that the columnNamePattern: must match the column name.......

If you send NULL then you have not specified a column name so that the JDBC
appears to not return one, NULL. However right or wrong this is this is exactly
what is happening with the PostgreSQL JDBC.

Class Abstractjdbc2DatabaseMetaData:

java.sql.ResultSet getColumns(int jdbcVersion, String catalog, String
schemaPattern, String tableNamePattern, String columnNamePattern)
~
~
~
if (columnNamePattern != null && !"".equals(columnNamePattern))
         {
             sql += " AND attname LIKE " + escapeQuotes(columnNamePattern);
         }

This is the only place that the columnNamePattern name is specified. I have
not checked beyound this, but the general sql query is not then picking up
all the column names as you might expect to be returned.

danap