Re: getTableName

On Mon, Dec 16, 2002 at 04:02:32PM -0500, Fernando Nasser wrote:
> Dror Matalon wrote:
> >On Mon, Dec 16, 2002 at 02:39:47PM -0500, Dave Cramer wrote:
> >
> >>Unfortunately we have no idea which table belongs with which column.
> >>
> >>We would need the server to return fully qualified names such as
> >>table.col
> >
>
> The problem is that a table name is not defined when joins are
> involved, if there are subqueries without an alias clause, for synthetic
> columns (results of expressions) etc.

Table name should be defined in a join:
for instance:
table foo
a int,
b int

table bar
c int,
d int

select a, b, c from foo, bar where foo.b = bar.d;

I don't see any reason why the server shouldn't know that a and b came
from foo and c came from bar. I agree though that there will be cases
where the table is unknown.

>
> BTW, the definition of getTableName() says "" is returned in these cases.
> I wonder how useful would be a feature that works "some times".
>
> The Sun JDBC book (White et al.) says "Because this feature is not widely
> supported, the return value from many DBMSs will be an empty string.
>
> I am not sure if I understand why you need this.
> If you are generating this query graphically with some tool, you would know
> for sure where each column come from as the user will be selecting things
> from listboxes with table qualified names (or for several listboxes for a
> specific table) and your tool will be adding them to the select-list in
> some fixed order.
>

We provid JSP tags where the user can create a HTML report on the fly.
Heres the doc that describes it:

The SelectXsl tag

Create a report using an SQL query. The look and feel of the report can
be changed using an XSL stylesheet. If you don't define one, the report
will use a default stylesheet. You can also control the start row and
the number of rows per page in your report. The default is to start on
row 0 and limit to 500 rows per page.

0.1 query attribute

SQL Query that defines the report. Example: "Select * from customer" .

0.2 file attribute

The name of the XSL stylesheet used to transform this report from XML to
HTML. If you don't define a XSL stylesheet a default one is going to be
applied.

0.3 rowsPerPage attribute

Limits the number of rows per page and will display "next" and
"previous" buttons as needed. For instance, if you specified 100, and
your report only has 25 rows, it will not display the arrows. If you
specify 25 and your report has 40 rows, it will display "next" in the
first page and previous in the second page.

0.4 startRow attribute

Start displaying the report at this row. Note that the row order is
different depending on the query. So the first row of "select * from
customer order by customer_id" is different than the first row in
"select * from customer order by first_name" .

If the user has a query in a report:

select name, address, homepage from company and I want to display it in a web
page so that it looks something like:

<a href="<%=homepage%>"<%=company%></a>

So it's a link.
I have no way to tell that homepage is a field of type "url". We keep
meta information that we can only track if we know which table the field
belongs to.

Regards,

Dror

>
> --
> Fernando Nasser
> Red Hat - Toronto E-Mail: fnasser(at)redhat(dot)com
> 2323 Yonge Street, Suite #300
> Toronto, Ontario M4P 2C9
>

--
Dror Matalon
Zapatec Inc
1700 MLK Way
Berkeley, CA 94709
http://www.zapatec.com