Re: SQL server application porting headache

On Mon, 24 Jun 2002, Curt Sampson wrote:

> On Sun, 23 Jun 2002, Oskar Berggren wrote:
>
> > >> SELECT "OperatorID" FROM "IntParams" WHERE OperatorID=0;
> > >> ^^^^^^^^^^
> > >> notice no quotes here

Well, it's worse than I thought. Using SQL Server 7.0, I created a table
test1 with a column name "ColOne". In the Query Analyzer:

SELECT "ColOne" FROM test1 WHERE "ColOne" = 7

All right so far. But removing either set of quotes also works. Until
you change case, where it breaks even without quotes. (I guess I should
have tested with fully uppercase and fully lowercase column names, too,
but subsequent events addled me so much I lost the thought.)

So after this I move to isql, where doing it without quotes also works,
so long as I get the case right. (Wrong case without quotes still
fails.) But better yet, you can quote "ColOne" in the SELECT part of
the statement, but if you try to quote "ColOne" in the WHERE portion of
the statement, it fails with some error message about being unable to
convert a varchar to a column type or something. So you can't quote in
the WHERE clause at all, except with [] instead of "".

At this point I started to realise why the application designers may
have done their queries in the way shown above. I then poured myself a
very large Suntory whisky, booted back into NetBSD, and left it at that.

Congratuations; this is the first time I've booted Windows in months,
and it was as pleasurable as ever.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC