Re: JDBC bug?

On Mon, Nov 26, 2001 at 03:41:28PM -0500, Colin Freas wrote:
> > via quoted mixed case identifiers.  I haven't seen any evidence that
> > basic SQL operations (select, insert, update, delete) have a bug in them.
>
> I'd peg a where clause as a basic SQL operation, and be it bug, feature,
> annoyance, whatever, it wasn't functioning because of the case of a column
> name.
>
> This worked:
> 1.  rs = stmt.executeQuery("select * from response");
>
> But I wanted to do this, which didn't work:
> 2.  rs = stmt.executeQuery("select * from response where questionID=16");
>
> This is how I got it to work:
> 3.  rs = stmt.executeQuery("SELECT \"response\".\"questionID\",
> \"response\".\"respondentID\", \"response\".\"answer\", \"response\".\"ID\"
> FROM \"response\" WHERE "\response\".\"questionID\"=16");

How about:
4.  rs = stmt.executeQuery("select * from response where \"questionID\"=16");

Quote what you must, and no more.

>
> Sorry, does anyone think the third is better than the second?
>

Yes, since it conforms to SQL92. If you'd bothered to check the postgresql
log, you would have seen error messages of the form:
test=# select * from response where questionID=16;
ERROR:  Attribute 'questionid' not found

Which would have clued you into what the problem was. Alternatively,
try your queries in psql so you can _see_ the responses.

Ross
--
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Executive Director                                  phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics              fax: 713-348-6182
Rice University MS-39
Houston, TX 77005