At 12:31 PM 18-04-02 -0700, you wrote:

>Thanks Roman. But see my earlier email, it means changing a lot of code.
>More than a
>1000 classes worked on by 100 developers. I was researching on replacing
>the Access
>database with Firebird. But encountered problems during conversion, as SQL2GDB
>converted my password field to passwordcol. Hence the problems of quoted
>fields etc.

Sanjay,
The problem you have encountered is a matter of the SQL standard, which
allows quote-delimited identifiers, not just for reserved words but also
for identifiers which contain blanks. It is a matter of the standard
specification that quoted identifiers also force case-sensitivity on
identifiers which receive that treatment. From InterBase 6/Firebird dialect
3 forward, implementing this standard makes it *possible* to inherit
previously illegal identifiers from other databases.

Unfortunately for you, when you did your conversion you took this option
and the tool applied quoted delimiters to all of your object names. It is
to the credit of the tool's developer that he included this capability,
since both Access and MSSQL have low conformity with standards. Access, in
particular. The appending of "col" to the end of column names which use
keywords is unnecessary when quoted delimiters are used - an area of
redundancy that I'm sure the developer would be happy to have pointed out.

On the matter of case-sensitivity, if your case-sensitive identifiers are
in upper case, you can use them in SQL without the quotes, provided they
don't break the other rules. This would make it OK to use your table name
in caps if it had been defined in caps....but it wasn't, of course. Your
"Password" would not be accepted unquoted under any circumstances, because
PASSWORD in any case is a keyword.

In your situation, to avoid massive recoding of your Java app, I would
reconvert the Access data, with awareness of the requirements of the
standard. That would be a much smaller task. If the tool you used doesn't
do the job, it is a trivial programming task to read your Access specs and
generate a DDL script to create the metadata as you want them to
be. Creating a script is by far the most responsible method to create a
true SQL database, anyway, since it provides you not just with the means to
recreate your schema at any time, but also with active documentation of
your schema. There are datapump tools around; but it isn't difficult to
write your own.

If you go this route - avoiding quoted delimiters entirely and replacing
identifiers that use keywords with legal words - you will reduce your
recoding to a minimum and also have a database in which future code can
totally ignore escaping the identifiers on every statement. In complex
statements, this is a *major* bind.

Commenting on Daniel's remarks about generators: there is no need to
quote-delimit them if you choose non-keywords with no spaces. Settle on a
naming scheme for them, e.g. I use GEN_ plus a marker indicating its
purpose. For example, GEN_PK_SALES_ITEM might be the generator for the
primary key column of a table named SALES_ITEM.

Helen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________