Re: JDBC bug? - Mailing list pgsql-jdbc

Barry,
I see what you're saying, and you're right.  But as for not using mixed case
identifiers as being the better solution, I have to disagree.  It's not like
I'm asking to put spaces in my column names and have the parser read my
mind.  I just think mixed case identifiers are easier on the eyes.
Although, the \"'s obviously aren't.  And is it \"s or \"'s?

I also just wanted to make sure I understand what's going on.  Postgres
stores the identifiers in a case insensitve manner by default, but if you
mix case when creating the tables or what have you, it stores them as case
sensitive.  Then, the JDBC driver would take 'select * from z where XyZZy =
"Fool!"' and send it as '...xyzzy...', which would...  what?  Is that going
to throw an error, or just not return anything?  pgadmin II says attribute
undefined or somesuch, and that is what quickly led me to the solution.

I'm just saying that it's frustrating because in one of my earlier checks to
make sure my SQL was ok, I used Access 2000 with some linked tables to my
Postgres db.  When I ran 'select * from response where questionID=16'
through that, it worked without a hitch (actually, the 16 needed to be
'16'), so I figured the problem was somewhere else.  Plus it was such simple
SQL, I mean, what could be wrong with it?  I'd be the first to admit I
don't...  well, I didn't... know the nuances of the SQL9X casing rules.  My
issue, as stated in my initial note, was more that nobody (jdbc, postgres,
tomcat) told me that my where clause was, essentially, bogus.  I mean, what
am I catching that SQLException for?

And Bruce wrote...
>rs = stmt.executeQuery("select * from response where \"questionID\"=16");

That works.

Doh!
Colin

------

Barry wrote:

Colin,

Whether it is better or not isn't the issue.  That is how the SQL
standard says it is supposed to work.  If you create the objects with
quoted mixed case identifiers you must use quoted mixed case identifiers
to access the objects.  A better solution to your problem would be to
create the tables without using quoted indentifiers and then your
original query would work.

thanks,
--Barry

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");
>
> Sorry, does anyone think the third is better than the second?