Thread: ResultSetMetaData.getTableName() == null
Hi, GetTableName() always returns null. Can someone tell me if this is considered to be a problem and if so when it will be fixed. Best regards, Alex Stienstra.
Hi Alex, Alex Stienstra wrote: > GetTableName() always returns null. Can someone tell me if this is > considered to be a problem and if so when it will be fixed. Are you sure it's really null? According to the javadoc for ResultSetMetaData, getTableName() may return "" - see http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSetMetaData.html#getTableName(int). I did a quick test app, and the driver version I'm using (postgresql-8.1-404.jdbc3.jar) returns "". If you get different, tell me which driver version you're using and I'll try that. I don't know why it returns "" - perhaps someone else can answer that. Mind you, Oracle 10g does the same. Sybase ASE 12.5 is the only DB I have access to where I get a non-empty table name. Regards, Philip. -- Philip Yarra Senior Software Engineer, Utiba Pty Ltd philip@utiba.com
Philip Yarra wrote: > I don't know why it returns "" - perhaps someone else can answer that. > Mind you, Oracle 10g does the same. Sybase ASE 12.5 is the only DB I > have access to where I get a non-empty table name. There was a long discussion about this some time ago, see http://archives.postgresql.org/pgsql-jdbc/2004-08/msg00023.php or thereabouts which is when we started agreeing on things :) Essentially I think it boiled down to "getTableName() should return the table alias name", and since we don't have that available we return "". There's a postgresql-specific interface to get at the underlying table name (in the cases where that info is available). -O
Oliver Jowett wrote:
> Essentially I think it boiled down to "getTableName() should return the
> table alias name", and since we don't have that available we return "".
> There's a postgresql-specific interface to get at the underlying table
> name (in the cases where that info is available).
Hmmm... maybe I'm missing something, but the attached test case doesn't
use any aliases, and it returns "" (at least it does for me). Is this
expected behaviour? If there's a postgresql-specific way to get it,
couldn't this method implement the same way of getting it?
BTW: not a big deal for me, I can't see that I'd ever want to use that
method. I'm just curious...
Regards, Philip.
--
Philip Yarra
Senior Software Engineer, Utiba Pty Ltd
philip@utiba.com
driver = org.postgresql.Driver
url = jdbc:postgresql://your_host:5432/your_db
user = you
pass = your_password
import java.sql.*;
import java.util.*;
import java.io.*;
import java.text.SimpleDateFormat;
class rsmd
{
static Connection conn = null;
static Properties prop = null;
public static void main(String [] args)
{
try
{
if(args.length > 0) prop = loadProp(args[0]);
else usage();
String url = prop.getProperty("url");
String user = prop.getProperty("user");
String pass = prop.getProperty("pass");
String driver = prop.getProperty("driver");
Class.forName(driver);
conn = DriverManager.getConnection(url,user,pass);
conn.setAutoCommit(true);
createTable();
insertData();
executeSelect();
conn.commit();
dropTable();
conn.commit();
conn.close();
log("all done");
}
catch(Exception ex)
{
log(ex);
System.exit(1);
}
}
static void executeSelect()
{
PreparedStatement stmt = null;
try{
stmt = conn.prepareStatement("SELECT * FROM tempextest");
ResultSet rs = stmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
String table = rsmd.getTableName(1);
if(table == null) log("null table name");
if("".equals(table)) log("empty table name");
log("table name [" + table + "]");
} catch (SQLException sqlex) {
log(sqlex);
closeStatement(stmt);
}
}
static void createTable()
{
log("Creating table tempextest");
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement("CREATE TABLE tempextest(id INT PRIMARY KEY)");
stmt.executeUpdate();
} catch (SQLException sqlex) {
log(sqlex);
log("error creating table tempextest, can't proceed");
closeStatement(stmt);
System.exit(1);
}
log("Table tempextest created");
}
static void insertData()
{
log("Inserting data into table tempextest");
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement("INSERT INTO tempextest(id) VALUES (42)");
stmt.executeUpdate();
} catch (SQLException sqlex) {
log(sqlex);
log("error inserting data into table tempextest, can't proceed");
closeStatement(stmt);
System.exit(1);
}
log("Table tempextest created");
}
static void dropTable()
{
log("Dropping table tempextest");
PreparedStatement stmt = null;
try {
stmt = conn.prepareStatement("DROP TABLE tempextest");
stmt.executeUpdate();
} catch (SQLException sqlex) {
log(sqlex);
closeStatement(stmt);
}
log("Table tempextest dropped");
}
static Properties loadProp(String fileName)
{
try{
Properties prop = new Properties();
FileInputStream fis = new FileInputStream(fileName);
prop.load(fis);
return prop;
} catch (Exception ex) {
log("exception loading properties: " + ex);
usage();
}
return null;
}
static void usage()
{
System.err.println("Usage: java rsmd propfile");
System.exit(1);
}
static void log(String msg)
{
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.S");
System.out.println(sdf.format(new java.util.Date()) + ":" + msg);
}
static void log(Exception ex)
{
log(ex.toString());
ex.printStackTrace();
}
static void log(Object o)
{
log(o.toString());
}
static void closeStatement(Statement st)
{
try{
st.close();
}catch(SQLException sqlex) {
log(sqlex);
}
}
}
Philip Yarra wrote: > Oliver Jowett wrote: > >> Essentially I think it boiled down to "getTableName() should return >> the table alias name", and since we don't have that available we >> return "". There's a postgresql-specific interface to get at the >> underlying table name (in the cases where that info is available). > > > Hmmm... maybe I'm missing something, but the attached test case doesn't > use any aliases, and it returns "" (at least it does for me). Is this > expected behaviour? If there's a postgresql-specific way to get it, > couldn't this method implement the same way of getting it? We don't know whether aliases have been used or not so we can't do this. -O
Oliver Jowett wrote:
> We don't know whether aliases have been used or not so we can't do this.
Just had a look at the source for getTableName:
public String getTableName(int column) throws SQLException
{
return "";
}
I can cast rsmd to PGResultSetMetaData and call getBaseTableName, and it
does what I expect - in my example, gets the table name... so is there
some reason why getTableName couldn't be re-written as:
public String getTableName(int column) throws SQLException
{
return getBaseTableName(column);
}
Sorry if this is re-hashing old ground - I read the thread you mentioned
before, but that seemed to be more about getting column names where
they're aliased in the query using an AS clause. I'm assuming there's a
lot of background that led to the implementation we have currently...
any hints gratefully accepted.
Regards, Philip.
--
Philip Yarra
Senior Software Engineer, Utiba Pty Ltd
philip@utiba.com
Philip Yarra wrote:
> so is there
> some reason why getTableName couldn't be re-written as:
>
> public String getTableName(int column) throws SQLException
> {
> return getBaseTableName(column);
> }
>
We came to the conclusion in the original discussion that getTableName()
should return the aliased name of the table in the query, or the actual
table name if not aliased.
getBaseTableName() always returns the real underlying table name, even
if it was actually aliased to something else in the query. This reflects
the information that the server gives us (see the RowDescription message
in
http://www.postgresql.org/docs/8.1/static/protocol-message-formats.html
-- getBaseTableName() returns a name based on the table object ID in
that message)
So your suggested implementation would return incorrect information
whenever there were aliases involved.
We have no way of telling if aliasing has been done or not based on the
data returned by the server, so we can't even do it only when no aliases
are used.
-O
Oliver Jowett wrote: > Philip Yarra wrote: > So your suggested implementation would return incorrect information > whenever there were aliases involved. Thanks, I understand now. So the decision was to return nothing, rather than something that's potentially wrong? > We have no way of telling if aliasing has been done or not based on the > data returned by the server, so we can't even do it only when no aliases > are used. Looks like Sybase came to the same question and decided that returning the base table name was ok: $ java -cp .:/home/philip/bin/jconn3.jar rsmd sybase3.prop 2006-09-22T15:50:39.206:QUERY: SELECT * FROM tempextest 2006-09-22T15:50:39.210:table name [tempextest] 2006-09-22T15:50:39.212:QUERY: SELECT * FROM tempextest tablealias 2006-09-22T15:50:39.215:table name [tempextest] Oracle seems to have a very similar implementation to PostgreSQL: $ java -cp .:/home/philip/bin/ojdbc14.jar rsmd oracle.prop 2006-09-22T15:53:59.882:QUERY: SELECT * FROM tempextest 2006-09-22T15:53:59.986:empty table name 2006-09-22T15:53:59.987:table name [] 2006-09-22T15:53:59.989:QUERY: SELECT * FROM tempextest tablealias 2006-09-22T15:54:00.3:empty table name 2006-09-22T15:54:00.4:table name [] How about allowing the user to set a connection property so that the (possibly-wrong-but-more-useful) call through to getbaseTablename is done, so that *some* info comes back, with default of existing behaviour? Or has that also been discussed and dismissed previously? :-) Regards, Philip. -- Philip Yarra Senior Software Engineer, Utiba Pty Ltd philip@utiba.com
MySQL returns the tablename if possible otherwise the alias. I find this an acceptable solution. Having at least a name one could query the meta system in other to determine whether it is a table name or not. I am working on automatically generated web interface on the basis of a single SQL query. This is why I need to know the columns and tables involved in the query: http://www.melstra.nl/test/Form1.php Best regards, Alex Stienstra.
Hi, Alex, Alex Stienstra wrote: > MySQL returns the tablename if possible otherwise the alias. I find this > an acceptable solution. Having at least a name one could query the meta > system in other to determine whether it is a table name or not. The problem is that PostgreSQL does not pass any information about aliases down to the client. So we have two possibilities to implement this feature: Hack up the server to provide that additional info, or implement a fully-fledged SQL parser in the client and get the alias names ourselves. Both seems not worth it currently. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
On 22-Sep-06, at 9:23 AM, Markus Schaber wrote: > Hi, Alex, > > Alex Stienstra wrote: >> MySQL returns the tablename if possible otherwise the alias. I >> find this >> an acceptable solution. Having at least a name one could query the >> meta >> system in other to determine whether it is a table name or not. > > The problem is that PostgreSQL does not pass any information about > aliases down to the client. > > So we have two possibilities to implement this feature: Hack up the > server to provide that additional info, or implement a fully- > fledged SQL > parser in the client and get the alias names ourselves. Certainly parsing the SQL is expensive, however I would like to see the server extend the protocol to give us this information. Dave > > Both seems not worth it currently. > > HTH, > Markus > -- > Markus Schaber | Logical Tracking&Tracing International AG > Dipl. Inf. | Software Development GIS > > Fight against software patents in Europe! www.ffii.org > www.nosoftwarepatents.org > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: 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 >
Hi Markus,
I'am surprised that PostgreSQL doesn't pass the table informatie to the
client. Susprised, since for example the library PHP (pg_field_table) is
using, does pass on this information. I'am quite sure that this library
function is based on the same API as the JDBC implementation is.
I agree with you that PostgreSQL server should supply this information
and implementing a full-flegded SQL parser is no option. Specially,
because PostgreSQL already parses the statement and therefore knows the
tables and columns that are involved in the query.
It is a pitty that this (for me) vital information is not made
available.
Best regards,
Alex.
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Markus Schaber
Sent: vrijdag 22 september 2006 15:24
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] ResultSetMetaData.getTableName() == null
Hi, Alex,
Alex Stienstra wrote:
> MySQL returns the tablename if possible otherwise the alias. I find
> this an acceptable solution. Having at least a name one could query
> the meta system in other to determine whether it is a table name or
> not.
The problem is that PostgreSQL does not pass any information about
aliases down to the client.
So we have two possibilities to implement this feature: Hack up the
server to provide that additional info, or implement a fully-fledged SQL
parser in the client and get the alias names ourselves.
Both seems not worth it currently.
HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org
---------------------------(end of broadcast)---------------------------
TIP 1: 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
Hi, Alex, Alex Stienstra wrote: > I'am surprised that PostgreSQL doesn't pass the table informatie to the > client. Susprised, since for example the library PHP (pg_field_table) is > using, does pass on this information. I'am quite sure that this library > function is based on the same API as the JDBC implementation is. AFAICS, it does pass _table_ information, but not alias information. Are you shure the PHP library does show you alias information? Is it using V2 or V3 protocol under the hoods? Thanks, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org