Re: JDBC bug?

On Mon, Nov 26, 2001 at 04:24:46PM -0500, Colin Freas wrote:
>
> Barry,
>
> 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

Ah, here's the real problem: MS-Access 'helpfully' quotes all the identifiers
for you, behind your back. Kind of like the 'helpful' things Word does as you
type. I got bit by the same behavior: I created same tables from inside
Access, and got to spend some time finding all the MiXedCase BuGs in PostgreSQL
v 6.2 (or so).

> 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.

Indeed.

BTW, as long as you stay away from software that meses with your queries,
you can get away with writing:

select * from response where questionID=16;

As long as the table was created the same way (i.e., no quotes):

create table response (questionID int , ...)

The only problem is that the column name returned by the system will
be questionid.

Ross