Thread: PGResultSetMetaData

Hello,

as I need getTableName() and getSchemaName() I found
the interface PGResultSetMetaData.
Is ist still experimental?
Has anybody used it already? If so, could you give me
a hint how to implement it?

Thank you,
Rainer Leo

leo wrote:
> as I need getTableName() and getSchemaName() I found
> the interface PGResultSetMetaData.
> Is ist still experimental?
> Has anybody used it already? If so, could you give me
> a hint how to implement it?

It's not experimental.  However, instead of using the PostgreSQL
implementation class, you should stick to the JDBC API.

    ResultSet rs = stmt.executeQuery(...);
    ResultSetMetaData rmd = rs.getMetaData();

    String table = rmd.getTableName(1);
    String schema = rmd.getSchemaName(1);

(just for example)

--
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation

Chris Smith:
>    ResultSet rs = stmt.executeQuery(...);
>    ResultSetMetaData rmd = rs.getMetaData();
>
>    String table = rmd.getTableName(1);
>    String schema = rmd.getSchemaName(1);
Does this really work?

Using Postgres 8 i am getting empty strings from both methods.

Leo,

Here is the documentation of the interface

/**
     * Returns the underlying table name of query result, or ""
     * if it is unable to be determined.
     *
     * @since 8.0
     */
    public String getBaseTableName(int column) throws SQLException;

    

    /**
     * Returns the underlying table name of query result, or ""
     * if it is unable to be determined.
     *
     * @since 8.0
     */
    public String getBaseSchemaName(int column) throws SQLException;

So I'm guessing your query makes it difficult to find the table names
and the schema names.

What is your query?

Dave

leo wrote:

>Chris Smith:
>
>
>>   ResultSet rs = stmt.executeQuery(...);
>>   ResultSetMetaData rmd = rs.getMetaData();
>>
>>   String table = rmd.getTableName(1);
>>   String schema = rmd.getSchemaName(1);
>>
>>
>Does this really work?
>
>Using Postgres 8 i am getting empty strings from both methods.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: if posting/reading through Usenet, please send an appropriate
>      subscribe-nomail command to majordomo@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561

leo wrote:
> Chris Smith:
>
>>   ResultSet rs = stmt.executeQuery(...);
>>   ResultSetMetaData rmd = rs.getMetaData();
>>
>>   String table = rmd.getTableName(1);
>>   String schema = rmd.getSchemaName(1);
>
> Does this really work?
>
> Using Postgres 8 i am getting empty strings from both methods.

getTableName() always returns an empty string, as the server does not
tell us about table aliases. The same applies to getSchemaName().
getColumnName() will return the column alias.

If you want the name of the underlying table and column, cast to
PGResultSetMetadata and use getBaseTableName() / getBaseSchemaName() /
getBaseColumnName(). These will return the table/schema/column name of
the underlying relation, where available.

For example, given this query:

   SELECT p.proname AS pname FROM pg_catalog.pg_proc p

getTableName(1) returns ""
getSchemaName(1) returns ""
getColumnName(1) returns "pname"
getBaseTableName(1) returns "pg_proc"
getBaseSchemaName(1) returns "pg_catalog"
getBaseColumnName(1) returns "proname"

See http://archives.postgresql.org/pgsql-jdbc/2004-08/msg00008.php for
the discussion that lead to this behaviour.

(Kris, any reason why PGResultSetMetadata does not extend
ResultSetMetadata?)

-O

> cast to PGResultSetMetadata...
That works perfectly.

Thank you very much for your help.

Chris Smith: 

   ResultSet rs = stmt.executeQuery(...);  ResultSetMetaData rmd = rs.getMetaData();
  String table = rmd.getTableName(1);  String schema = rmd.getSchemaName(1);   

Does this really work?

Using Postgres 8 i am getting empty strings from both methods.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate     subscribe-nomail command to majordomo@postgresql.org so that your     message can get through to the mailing list cleanly

 

On Mon, 24 Jan 2005, Oliver Jowett wrote:

> (Kris, any reason why PGResultSetMetadata does not extend
> ResultSetMetadata?)
>

Never really thought about it.  Note that PGStatement and PGConnection
don't extend the java.sql versions, instead there are BaseXXX versions
that extend the pg and java.sql versions that the Abstract classes
implement.  I'm confused as to how this actually works because the
Abstract classes don't implement the full java.sql interface.  Then the
real JdbcX classes also claim to implement the java.sql inteface and
actually do.

In any case what we have now is a little confusing internally, but
externally it is consistent.

Kris Jurka

>>(Kris, any reason why PGResultSetMetadata does not extend
>>ResultSetMetadata?)

By ResultSetMetadata I assume you mean java.sql.ResultSetMetadata.  The
problem with having PGResultSetMetadata extend that interface is which
one would you end up extending?  The JDBC1, JDBC2, JDBC3 or JDBC4
version?

--Barry


-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Oliver Jowett
Sent: Sunday, January 23, 2005 12:34 PM
To: leo
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] PGResultSetMetaData

leo wrote:
> Chris Smith:
>
>>   ResultSet rs = stmt.executeQuery(...);
>>   ResultSetMetaData rmd = rs.getMetaData();
>>
>>   String table = rmd.getTableName(1);
>>   String schema = rmd.getSchemaName(1);
>
> Does this really work?
>
> Using Postgres 8 i am getting empty strings from both methods.

getTableName() always returns an empty string, as the server does not
tell us about table aliases. The same applies to getSchemaName().
getColumnName() will return the column alias.

If you want the name of the underlying table and column, cast to
PGResultSetMetadata and use getBaseTableName() / getBaseSchemaName() /
getBaseColumnName(). These will return the table/schema/column name of
the underlying relation, where available.

For example, given this query:

   SELECT p.proname AS pname FROM pg_catalog.pg_proc p

getTableName(1) returns ""
getSchemaName(1) returns ""
getColumnName(1) returns "pname"
getBaseTableName(1) returns "pg_proc"
getBaseSchemaName(1) returns "pg_catalog"
getBaseColumnName(1) returns "proname"

See http://archives.postgresql.org/pgsql-jdbc/2004-08/msg00008.php for
the discussion that lead to this behaviour.

(Kris, any reason why PGResultSetMetadata does not extend
ResultSetMetadata?)

-O

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Barry Lind wrote:
>
>>>(Kris, any reason why PGResultSetMetadata does not extend
>>>ResultSetMetadata?)
>
>
> By ResultSetMetadata I assume you mean java.sql.ResultSetMetadata.  The
> problem with having PGResultSetMetadata extend that interface is which
> one would you end up extending?  The JDBC1, JDBC2, JDBC3 or JDBC4
> version?

Whichever one you're building against.. they're all called
java.sql.ResultSetMetadata.

The reason I ask is that the current scheme is a little awkward to use
if you want to use both the standard and extra interface methods. Either
you need two variables providing different interface views on the same
object, or you do a lot of casting.

-O