RE: select on multiple tables - Mailing list pgsql-jdbc

The SELECT is in a servlet, something like this:

rs = stmt.executeQuery("SELECT * FROM " + request.getParameter("table1") +
", " + request.getParameter("table2"));
session.setAttribute("result", rs);
request.sendRedirect(request.getParameter("page2"));

that's a simplification of what it does, but it's good enough for
demonstration I think.

The .jsp file that calls the servlet knows what tables it wants to read out
of, and the .jsp file that the servlet redirects to knows what columns it
wants.  The servlet doesn't know anything and hopefully shouldn't care.  I
wanted to abstract it as much as possible.

sorry for the confusion and thanks for the help.

ben


-----Original Message-----

From: Rene Pijlman

To: Ben Carterette

Cc: pgsql-jdbc@postgresql.org

Sent: 8/20/01 12:28 PM

Subject: Re: [JDBC] select on multiple tables



On Thu, 16 Aug 2001 10:02:27 -0500, you wrote:

>This won't work because I don't know in advance of the SELECT which

>tables I'm going to be selecting from.



I'm not sure if I understand this correctly. Whenever you write

the SELECT statement you have to know the names of the tables,

that's required by the syntax of select.



What you're telling us is that you don't know the column names

when you construct the statement, but you do know the column

names 5 lines down when you want to do the rs.getString()?





>On Wednesday, August 15, 2001, at 06:29  PM, Rene Pijlman wrote:

>> On Wed, 15 Aug 2001 16:43:31 -0500, Ben Carterette wrote:

>>> I have a query like "SELECT * FROM table1, table2" and I want to

read

>>> values

>>> out of a ResultSet.  What if the two tables have column names in

>>> common and

>>> I can't predict the column numbers?  Is there any way to get

table1.id

>>> and

>>> table2.id?  rs.getString tells me "The column name table1.id not

>>> found."

>>

>> Does this also happen when you explicitly name the columns?

>>

>>    SELECT table1.id, ..., table2.id, ...

>>    FROM table1, table2

>>

>> Or if that doesn't help, try if a column label with the AS

>> clause works:

>>

>> SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]

>>     * | expression [ AS output_name ] [, ...]

>> http://www.postgresql.org/idocs/index.php?sql-select.html

>>

>>   SELECT table.id AS id1, ..., table2.id AS id2

>>   FROM table1, table2

>>

>> And then rs.getString("id1");

>>

>> I think both solutions should work. Please let us know if they

>> don't.

>>

>> Regards,

>> René Pijlman

>>

>

>---------------------------(end of

broadcast)---------------------------

>TIP 4: Don't 'kill -9' the postmaster





Regards,

René Pijlman