Re: metadata.getColumns() using Apache Tomcat / jndi

> Date: Fri, 30 Sep 2011 09:07:18 -0600
> From: danap@ttc-cmc.net
> To: beginner_@hotmail.de; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] metadata.getColumns() using Apache Tomcat / jndi
>
> 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