Tablealias as column qualifier in resultset

[Per Newgro <pe...@gmx.ch>]

Hi,

i've a little question. 
If i execute the example-statement
  
  SELECT MYTABLE.* FROM myTable MYTABLE WHERE MYTABLE:A = 5;

i expected to access the column in resultset by usage of

  Object o = getObject("MYTABLE.A);

But this is throwing a ColumnNotFoundException. The other access is working.

  Object o = getObject("A);

I'm a little bit confused because how is derby solving same column names in a 
join of two tables, if the columns are not referenced by the qualifier?

Cheers
Per

Re: Tablealias as column qualifier in resultset

[Knut Anders Hatlen <Kn...@Sun.COM>]

Per Newgro <pe...@gmx.ch> writes:

> Hi Rajesh,
>
>> Hello Per,
>>
>> I have never seen any examples of using "tableName.columnName" with the
>> ResultSet.getObject() method.
>> My understanding   is  the ResultSet.getObject () is expected to use
>> only the column names, hence using
>> the table name qualifier will  not work.  Using only the column names
>> should be right approach as
>> you noticed (getObject("A"))
>>
> But this is a valid sql syntax or? Is the meaning of that, that it's not 
> supported by the jdbc-spec or only by derby driver?

This is defined by the JDBC spec, and it is implemented correctly by
Derby as far as I can see. Here's the relevant part of the spec:

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/ResultSet.html

  Column names used as input to getter methods are case
  insensitive. When a getter method is called with a column name and
  several columns have the same name, the value of the first matching
  column will be returned. The column name option is designed to be
  used when column names are used in the SQL query that generated the
  result set. For columns that are NOT explicitly named in the query,
  it is best to use column numbers. If column names are used, there is
  no way for the programmer to guarantee that they actually refer to
  the intended columns.

>> For cases where the same column names appear in joins of two tables, I
>> would qualify those
>> columns with the respective tables in the query and  use the
>> *getObject*(int columnIndex)  instead:
> But this means i have to map the column index and the object field. Thats 
> impossible, because the resultset order of fields depends on create table 
> statement, which is externally managed from my application (in a sql script 
> file). I.e. i can not guarantee the correct mapping.

Since you know the column names, you could list the columns explicitly
in your SQL query instead of using *.

  select mytable.a, mytable.b, ...

Then you know that A is the first column, B is the second, and so on.

Another option is to use ResultSet.getMetaData() to retrieve a
ResultSetMetaData object from which you can find the name and the
table of each column. See ResultSetMetaData.getColumnName(int column)
and ResultSetMetaData.getTableName(int column).

-- 
Knut Anders

Re: Tablealias as column qualifier in resultset

[Per Newgro <pe...@gmx.ch>]

Hi Rajesh,

> Hello Per,
>
> I have never seen any examples of using "tableName.columnName" with the
> ResultSet.getObject() method.
> My understanding   is  the ResultSet.getObject () is expected to use
> only the column names, hence using
> the table name qualifier will  not work.  Using only the column names
> should be right approach as
> you noticed (getObject("A"))
>
But this is a valid sql syntax or? Is the meaning of that, that it's not 
supported by the jdbc-spec or only by derby driver?

> For cases where the same column names appear in joins of two tables, I
> would qualify those
> columns with the respective tables in the query and  use the
> *getObject*(int columnIndex)  instead:
But this means i have to map the column index and the object field. Thats 
impossible, because the resultset order of fields depends on create table 
statement, which is externally managed from my application (in a sql script 
file). I.e. i can not guarantee the correct mapping.

Cheers
Per

AW: Tablealias as column qualifier in resultset

[Steffen Winkler <st...@danprodukte.de>]

For cases where the same column names appear in joins of two tables, you can
use column-Alias, e.g.:

 SELECT  t.TABLENAME AS TABLENAME
       , t2.TABLENAME AS REFTABLENAME
   FROM  SYSTABLES t
      ,  SYSTABLES t2
 WHERE ...

resultSet.getString( TABLENAME );
resultSet.getString( REFTABLENAME );

-----Ursprüngliche Nachricht-----
Von: Rajesh Kartha [mailto:kartha02@gmail.com]
Gesendet: Mittwoch, 6. Dezember 2006 14:10
An: Derby Discussion
Betreff: Re: Tablealias as column qualifier in resultset


Per Newgro wrote:

>Hi,
>
>i've a little question.
>If i execute the example-statement
>
>  SELECT MYTABLE.* FROM myTable MYTABLE WHERE MYTABLE:A = 5;
>
>i expected to access the column in resultset by usage of
>
>  Object o = getObject("MYTABLE.A);
>
>But this is throwing a ColumnNotFoundException. The other access is
working.
>
>  Object o = getObject("A);
>
>I'm a little bit confused because how is derby solving same column names in
a
>join of two tables, if the columns are not referenced by the qualifier?
>
>Cheers
>Per
>
>
>
Hello Per,

I have never seen any examples of using "tableName.columnName" with the
ResultSet.getObject() method.
My understanding   is  the ResultSet.getObject () is expected to use
only the column names, hence using
the table name qualifier will  not work.  Using only the column names
should be right approach as
you noticed (getObject("A"))

For cases where the same column names appear in joins of two tables, I
would qualify those
columns with the respective tables in the query and  use the
*getObject*(int columnIndex)  instead:

Example; select a.b,b.b from a, b where a.a=b.a


HTH,
Rajesh

Re: Tablealias as column qualifier in resultset

[Rajesh Kartha <ka...@gmail.com>]

Per Newgro wrote:

>Hi,
>
>i've a little question. 
>If i execute the example-statement
>  
>  SELECT MYTABLE.* FROM myTable MYTABLE WHERE MYTABLE:A = 5;
>
>i expected to access the column in resultset by usage of
>
>  Object o = getObject("MYTABLE.A);
>
>But this is throwing a ColumnNotFoundException. The other access is working.
>
>  Object o = getObject("A);
>
>I'm a little bit confused because how is derby solving same column names in a 
>join of two tables, if the columns are not referenced by the qualifier?
>
>Cheers
>Per
>
>  
>
Hello Per,

I have never seen any examples of using "tableName.columnName" with the 
ResultSet.getObject() method.
My understanding   is  the ResultSet.getObject () is expected to use 
only the column names, hence using
the table name qualifier will  not work.  Using only the column names 
should be right approach as
you noticed (getObject("A"))

For cases where the same column names appear in joins of two tables, I  
would qualify those
columns with the respective tables in the query and  use the 
*getObject*(int columnIndex)  instead:

Example; select a.b,b.b from a, b where a.a=b.a


HTH,
Rajesh