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

metadata.getColumns() using Apache Tomcat / jndi

From
Thomas Strunz
Date:
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:
<pre>
        PGSimpleDataSource dataSource = new PGSimpleDataSource();
        dataSource.setDatabaseName(postgresqlDatabaseName);
        dataSource.setServerName(postgresqlHost);
        dataSource.setUser(postgresqlUser);
        dataSource.setPassword(postgresqlPW);
        return dataSource;
</pre>

If I want to get the datasource from jndi / tomcat as in:

<Resource name="jdbc/Zinc"
            auth="Container"
            type="javax.sql.DataSource"
            username="yyyyyy"
            password="xxxxxx"
            driverClassName="org.postgresql.Driver"
            url="jdbc:postgresql:Zinc"
            maxActive="20"
            maxIdle="4"/>

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.

<Resource name="jdbc/Zinc"
            auth="Container"
            type="javax.sql.DataSource"
            username="sa"
            password=""
            driverClassName="org.hsqldb.jdbcDriver"
            url="jdbc:hsqldb:hsql://localhost/ZincDB"
            maxActive="20"
            maxIdle="4"/>

works perfectly fine and metaData.getColumns returns exactly what I would expect.

My table name only contains letters and is all lower case (postgresql jdbc is IMHO a bit quirky since table and field names seem to be partially case-sensitive).

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.

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

From
Dave Cramer
Date:
Thomas,

Any chance you can see what is in the server logs ?

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca




On Fri, Sep 30, 2011 at 2:13 AM, Thomas Strunz <beginner_@hotmail.de> 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:
> <pre>
>         PGSimpleDataSource dataSource = new PGSimpleDataSource();
>         dataSource.setDatabaseName(postgresqlDatabaseName);
>         dataSource.setServerName(postgresqlHost);
>         dataSource.setUser(postgresqlUser);
>         dataSource.setPassword(postgresqlPW);
>         return dataSource;
> </pre>
>
> If I want to get the datasource from jndi / tomcat as in:
>
> <Resource name="jdbc/Zinc"
>             auth="Container"
>             type="javax.sql.DataSource"
>             username="yyyyyy"
>             password="xxxxxx"
>             driverClassName="org.postgresql.Driver"
>             url="jdbc:postgresql:Zinc"
>             maxActive="20"
>             maxIdle="4"/>
>
> 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.
>
> <Resource name="jdbc/Zinc"
>             auth="Container"
>             type="javax.sql.DataSource"
>             username="sa"
>             password=""
>             driverClassName="org.hsqldb.jdbcDriver"
>             url="jdbc:hsqldb:hsql://localhost/ZincDB"
>             maxActive="20"
>             maxIdle="4"/>
>
> works perfectly fine and metaData.getColumns returns exactly what I would
> expect.
>
> My table name only contains letters and is all lower case (postgresql jdbc
> is IMHO a bit quirky since table and field names seem to be partially
> case-sensitive).
>
> 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.
>

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

From
dmp
Date:
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

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

From
dmp
Date:
dmp wrote:

> 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
>
try:

getColumNames(null, null, tableName, %)

danap.


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

From
Thomas Strunz
Date:
Hi all,

yes the documentation claims this but as written in my initial message it does work like this for PGSimpleDataSource and other RDBMS but not when using postgresql with jndit/tomcat.
So I assume there must be at least 2 different "code path" for getColumns(). Also allowing NULL would make sense anyway if you want all columns to be returned.

Anway I changed the command to "metaData.getColumns(null, null, molTableName, "%");". Behaviour remains exactly the same. this works for MS SQL, MySQL and HSQLDB but not for postgresql. Meaning there must be a bug somewhere.



> 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

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

From
Oliver Jowett
Date:
On 3 October 2011 18:00, Thomas Strunz <beginner_@hotmail.de> wrote:

> Anway I changed the command to "metaData.getColumns(null, null,
> molTableName, "%");". Behaviour remains exactly the same. this works for MS
> SQL, MySQL and HSQLDB but not for postgresql. Meaning there must be a bug
> somewhere.

Have you confirmed that the table name you are passing really is the
same in both cases?

Failing that, I suggest you log the query on the server side and see
what's happening differently.

Oliver

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

From
Thomas Strunz
Date:
Hi Oliver,

oh my god you are right. postgresql is the only one of these RDBMS that is case-sensitive.
configuration files are different for the 2 cases eg. tableName vs. tablename.

I ran into "issues" with this before and actually made myself a note "PostgreSQL is case-sensitive" and now I still forgot about that...

thanks for your help.

> Date: Mon, 3 Oct 2011 18:39:39 +1300
> Subject: Re: [JDBC] metadata.getColumns() using Apache Tomcat / jndi
> From: oliver@opencloud.com
> To: beginner_@hotmail.de
> CC: danap@ttc-cmc.net; pgsql-jdbc@postgresql.org
>
> On 3 October 2011 18:00, Thomas Strunz <beginner_@hotmail.de> wrote:
>
> > Anway I changed the command to "metaData.getColumns(null, null,
> > molTableName, "%");". Behaviour remains exactly the same. this works for MS
> > SQL, MySQL and HSQLDB but not for postgresql. Meaning there must be a bug
> > somewhere.
>
> Have you confirmed that the table name you are passing really is the
> same in both cases?
>
> Failing that, I suggest you log the query on the server side and see
> what's happening differently.
>
> Oliver