Thread: Get a table name
Hi all,
I'm trying to get the table name of a column in this way:
ResultSet rs;
try
{
rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
System.out.println(rsmd.getTableName(..));
but the getTableName( ..) method gives me an empty string. Isn't there a
way to know the table name of a specific field I'm obtaining? Thanks
In general, no. That's because resultset columns can be "entangled" in
more complex way than "column a of table b".
2009/12/27, Gianvito Pio <pio.gianvito@gmail.com>:
> Hi all,
> I'm trying to get the table name of a column in this way:
>
> ResultSet rs;
> try
> {
> rs = ps.executeQuery();
> ResultSetMetaData rsmd = rs.getMetaData();
> System.out.println(rsmd.getTableName(..));
>
> but the getTableName( ..) method gives me an empty string. Isn't there a
> way to know the table name of a specific field I'm obtaining? Thanks
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
--
Wysłane z mojego urządzenia przenośnego
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/
>In general, no. That's because resultset columns can be "entangled" in more complex way than "column a of table b". Actually, this *is* defined at the protocol level: From the RowDescription message at http://developer.postgresql.org/pgdocs/postgres/protocol-message-formats.html : ... Then, for each field, there is the following: String The field name. Int32 If the field can be identified as a column of a specific table, the object ID of the table; otherwise zero. ... No idea whether JDBC exposes this or whether the server actually sends the correct oids, or if this is just part of the protocol as a future-proofing attempt. --- Maciek Sakrejda | Software Engineer | Truviso 1065 E. Hillsdale Blvd., Suite 230 Foster City, CA 94404 (650) 242-3500 Main (650) 242-3501 F www.truviso.com
Gianvito Pio wrote:
> Hi all,
> I'm trying to get the table name of a column in this way:
>
> ResultSet rs;
> try
> {
> rs = ps.executeQuery();
> ResultSetMetaData rsmd = rs.getMetaData();
> System.out.println(rsmd.getTableName(..));
>
> but the getTableName( ..) method gives me an empty string. Isn't there a
> way to know the table name of a specific field I'm obtaining? Thanks
You can use PGResultSetMetaData.getBaseTableName() if you want to know
the name of the underlying table that provided the data for a column
(where known).
We concluded a while back that getTableName() is meant to return the
aliased table name in the query, not the underlying table name. It's not
easy for the driver to find the aliased name, so it always returns an
empty string, as you found.
-O
Maciek Sakrejda wrote: >> In general, no. That's because resultset columns can be "entangled" in more complex way than "column a of table b". > > Actually, this *is* defined at the protocol level: > No idea whether JDBC exposes this or whether the server actually sends > the correct oids, or if this is just part of the protocol as a > future-proofing attempt. The driver exposes this via PGResultSetMetadata.getBaseTableName(), and I believe the server does send useful data here. -O
Oliver Jowett <oliver@opencloud.com> writes:
> Maciek Sakrejda wrote:
>> No idea whether JDBC exposes this or whether the server actually sends
>> the correct oids, or if this is just part of the protocol as a
>> future-proofing attempt.
> The driver exposes this via PGResultSetMetadata.getBaseTableName(), and
> I believe the server does send useful data here.
For the record, my recollection is that that part of the protocol was
put in specifically at the request of the JDBC hackers.
regards, tom lane
> Gianvito Pio wrote:
> Hi all,
> I'm trying to get the table name of a column in this way:
>
> ResultSet rs;
> try
> {
> rs = ps.executeQuery();
> ResultSetMetaData rsmd = rs.getMetaData();
> System.out.println(rsmd.getTableName(..));
>
> but the getTableName( ..) method gives me an empty string. Isn't there
> a way to know the table name of a specific field I'm obtaining? Thanks
Well that is interesting.
danap.
// Method Instances
String sqlStatementString;
Statement sqlStatement;
ResultSet rs, db_resultSet;
DatabaseMetaData dbMetaData;
ResultSetMetaData tableMetaData;
// ====================================================
// Setting Up the Column Names, Form Fields, ComboBox
// Text, Hashmaps, Special Fields, & Primary Key(s).
sqlStatementString = "SELECT * FROM " + schemaTableName + "
LIMIT 1";
System.out.println(sqlStatementString);
db_resultSet = sqlStatement.executeQuery(sqlStatementString);
// Primary Key(s)
dbMetaData = dbConnection.getMetaData();
tableMetaData = db_resultSet.getMetaData();
System.out.println("TableName:" + tableMetaData.getTableName(1));
System.out.println("CatalogName:" +
tableMetaData.getCatalogName(1));
System.out.println("SchemaName:" + tableMetaData.getSchemaName(1));
rs = dbMetaData.getPrimaryKeys(tableMetaData.getCatalogName(1),
tableMetaData.getSchemaName(1),
tableMetaData.getTableName(1));
while (rs.next())
{
if (rs.getString("COLUMN_NAME").indexOf("chunk") == -1 &&
rs.getString("TABLE_NAME").equals(tableName))
{
primaryKeys.add(rs.getString("COLUMN_NAME"));
System.out.println(rs.getString("TABLE_NAME") + " " +
rs.getString("Column_NAME"));
}
}
DBTablesPanel actionPerformed() Connection Created
SELECT * FROM "public"."keY_tAble2" LIMIT 1
TableName:
CatalogName:
SchemaName:
keY_tAble2 Host
keY_tAble2 Db
keY_tAble2 Username
DBTablesPanel actionPerformed() Connection Closed
On Mon, 28 Dec 2009, Oliver Jowett wrote:
> We concluded a while back that getTableName() is meant to return the
> aliased table name in the query, not the underlying table name. It's not
> easy for the driver to find the aliased name, so it always returns an
> empty string, as you found.
>
Just recently I was reading the Mysql JDBC driver's documentation [1] and
came across the "useColumnNamesInFindColumn" config option that comes with
this description:
Prior to JDBC-4.0, the JDBC specification had a bug related to what
could be given as a "column name" to ResultSet methods like
findColumn(), or getters that took a String property. JDBC-4.0
clarified "column name" to mean the label, as given in an "AS"
clause and returned by ResultSetMetaData.getColumnLabel(), and if
no AS clause, the column name. Setting this property to "true" will
give behavior that is congruent to JDBC-3.0 and earlier versions of
the JDBC specification, but which because of the specification bug
could give unexpected results. This property is preferred over
"useOldAliasMetadataBehavior" unless you need the specific
behavior that it provides with respect to ResultSetMetadata.
This claims that we've got things backwards now despite our extensive
previous discussion because the spec was misleading. I gave the JDBC 4
spec another read and didn't find anything particularly revealing, but
taking a look at the Javadoc for getColumnLabel [2] does clear things up:
Gets the designated column's suggested title for use in printouts
and displays. The suggested title is usually specified by the SQL
AS clause. If a SQL AS is not specified, the value returned from
getColumnLabel will be the same as the value returned by the
getColumnName method.
For reference the previous discussion started at [3] and continued to [4].
So it looks like we should fix this up and ensure there's a way of
getting to old mode as well.
Kris Jurka
[1] http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-configuration-properties.html
[2] http://java.sun.com/javase/6/docs/api/java/sql/ResultSetMetaData.html#getColumnLabel(int)
[3] http://archives.postgresql.org/pgsql-jdbc/2004-07/threads.php#00314
[4] http://archives.postgresql.org/pgsql-jdbc/2004-08/threads.php#00008