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
