Wrong data from: getMetadata().getTableName(i) method

Frank Brouwer wrote:

> Hi,
>
> When using the getMetadata().getTableName(i) method on a (big) resultset,
> the method returns empty or a wrong value for the table name belonging to
> the column number i.
>
> Statement like:
> select *
> from table1, table2, table3, table4
> where tabl1.pmkey = table2.key
> and table2.pmkey = table3.key
> and table3.pmkey = table4.key
>
> Sample code:
> int colCount = rs.getMetaData().getColumnCount();
> while (rs.next())
> {
> for (int i = 1; i <= colCount; i++)
> {
> tableName = rs.getMetaData().getTableName(i);
> }
> }
>
> Found in:
> Java: 1.5.x and 1.6.x (64- and 32 bit)
> SQLServer: 2005
> Windows: Vista (64 bit) and Windows XP SP2
>
> Thanks,
> Frank.

What name(s) do you get? I always though that the getTableName() call
was poorly designed/specified. A data value can come from zero, one,
or many tables.

eg: "select 1", "select 'some constant (is it really from mytable?' from mytable"
or "select a.col1 + b.col2 from mytable a, myothertable b"

When I run this code:

s.execute("create table #qwe(foo varchar(100))");
s.execute("create table #asd(foo varchar(100))");
s.execute("create table #zxc(foo varchar(100))");

s.execute("insert #qwe values('qwe')");
s.execute("insert #asd values('asd')");
s.execute("insert #zxc values('zxc')");

ResultSet r = s.executeQuery("select 'bleh', * from #qwe, #asd, #zxc");
r.next();
int colCount = r.getMetaData().getColumnCount();

for (int i = 1; i <= colCount; i++)
{
System.out.println(" column name " + i + " is '" + r.getMetaData().getTableName(i) + "'");
}

I get no name at all: (Driver version is 1.2.2828.100, DBMS version is 8.00.818)
column name 1 is ''
column name 2 is ''
column name 3 is ''
column name 4 is ''

Hi Frank,

The Microsoft SQL Server 2005 JDBC Driver v1.2 only supports the
ResultSetMetaData.getTableName() call for IMAGE, TEXT and NTEXT colums.
The table name returned for other types is the empty string (""). We are
planning to add support for getTableName(), getSchemaName(), and
getCatalogName() for other types in the future. But as Joe points out,
certain expressions will still yield columns that are not associated with
any one particular table. In those cases, getTableName() would still
return the empty string.

Regards,
--David Olix [MSFT]