Re: 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.

Look at the test suite, you can see how the code is used there

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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>
>
>

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(at)postgresql(dot)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.

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