Hi Folks,

I'm working on a DB that's got a fair number of tables originally
created on Sybase (where I always use lower case for identifier
names--the merits of this lower case approach aren't really relevent
to this discussion, but lower case is demonstrably easier to read). At
any rate, now I'm trying to make code I already have work against a
Firebird 1.5 DB. I used IBaccess to create most of the tables, but now
I appear to have set myself up for a problem by using lower case for
identifiers.

The behavior I see is rather baffling, but apparently that's the way
IB/FB have always worked and (somewhat surprisingly to me, seems to be
quite natural to old hands). Here's the name of a table:

display_columns

I have this query in some client code:

select * from display_columns

But when running against my FB database, this selection returns an
error that the table:
DISPLAY_COLUMNS
does not exist (and this behavior doesn't seem to depend upon the type
of client I'm using--ODBC and JDBC work the same as what I see in
IBaccess).

Is the DB's SQL compiler really taking the lower case string passed to
it by the client in the query (display_columns) and converting that to
upper case (DISPLAY_COLUMNS) before attempting to look it up in
RDB$RELATIONS?

In the archives, I found this reply from Helen & Martijn Tonies to
another person surprised by this behavior:

> >select * from test ---> error (table does not exist)
> >select * from "test" ---> OK
> >select * from "TEST" ---> ok
> >select * from TEST ---> OK
> >
> >iow, if you see an object containing lowercase characters, then

case

> >matters and you have to quote the identifier. Any object in all

caps,
it's

> >optional to quote, as long as you stick to all caps.
> >
> >heLen
> >
> >
> Thanks for clarifying. Guess it's caps-lock:on from now on when

using

> ISQL :-)

> Not at all - simply don't use the double quotes.

OK; perhaps if I'd used ISQL to run the create table scripts, I'd have
seen that I was setting myself up for a problem. I didn't get tipped
off as I should have, perhaps, by the quotes added around the
identifiers when I extracted the DDL via the IBaccess Metadata
Extraction function.

BTW, what's "iow"?

So, it seems I've got to drop all the tables and recreate them, but
I'm not 100% sure I'm clear on what Helen meant by this (also
from the same thread):

> ..or re-do your database WITHOUT quoted identifiers and then case
> NEVER matters.

I'm guessing this "WITHOUT quoted identifiers" is not some kind of
mode I can set the DB in, but rather a question of putting in the
identifiers without quotes around them when creating the tables,
right? (And, if so, rats!) My problem is I used a GUI tool that
apprently insulated me from this up to a point.

Am I right about what's happening in the DB's SQL compiler? If so, can
I propose a modification that'll make a lot of sense for those of us
migrating from other DB's which don't make assumptions about unquoted
identifiers in SQL statements: could we have a mode in a future
version that skipped forcing identifiers parsed from SQL to upper case
when they're not quoted?

The current behavior is very odd on the face of it: that is, as the
case of identifiers is significant then how about leaving the case as
it appears in my query unless I ask for it to be changed? I don't mind
having to turn on some setting to get this behavior; but it sure would
be nice to have.... (Anyone working on case-insensitive collations,
I'd love to hear from you! Particularly if there's a way to hang one
on the system tables!)

Just to clarify: Helen's comment about "re-do[ing] your database
WITHOUT quoted identifiers" will result in all the identifiers
appearing in the system tables in upper case, right?

It appears that short of dropping all the tables and recreating them,
I'm out of luck here.... Anyone know a slick way to update the values
for the identifier names right in the system tables?

Thanks folks! Sorry for the long post :~\

John