| Subject | Re: [firebird-support] Quoted identifier |
|---|---|
| Author | Helen Borrie |
| Post date | 2005-06-08T01:36:51Z |
At 05:07 PM 7/06/2005 -0400, you wrote:
all in upper case. Identifiers created with this convention are restricted
to certain characters, cannot contain spaces and cannot be reserved
words. In SQL statements you can refer to these identifiers in uppercase,
lowercase or mixed case.
If you choose instead to use quoted identifiers, then those identifiers
will be case-sensitive and can contain spaces and other "illegal"
characters. However, all SQL statements that refer to quoted identifiers
must be both quoted AND case-correct.
One minor (and sometimes confusing) exception to this rule is where you
have a quoted identifier that was defined in all uppercase, with no illegal
characters and is not a reserved word. In that exceptional case, you will
not get an exception from the database engine if you fail to use the quotes.
What happens in tools and interfacing drivers is often quite
idiosyncratic. Some will enforce quoted identifiers in all cases, some
will give you the option to create objects with or without quoted
identifiers; some driver interfaces might even intecept all of your
statements and insert quotes around all identifiers. These tricks are what
you refer to as "preferences".
However, as far as the database engine is concerned, it doesn't know or
care about "preferences" that have been interposed by third-party
tools. So, if you define your databases with quoted identifiers, you are
stuck with this annoyance. To get rid of it, you will need to build a new
database *without* the quoted identifiers and pump the data from the
annoying version. If you want to retain the ability to create objects with
identifiers that are case-sensitive, or use illegal characters or illegal
words, then the annoyance comes with the territory.
./heLen
>Hello!By default, SQL identifiers are case-insensitive and Firebird stores them
>
>Everytime I'm writing a Query with fields that are in Lower Case, I need
>to put quote around the name which is very annoying. I am not using a DSN.
>So where can I change this preference??? (I am using IBExpert)
all in upper case. Identifiers created with this convention are restricted
to certain characters, cannot contain spaces and cannot be reserved
words. In SQL statements you can refer to these identifiers in uppercase,
lowercase or mixed case.
If you choose instead to use quoted identifiers, then those identifiers
will be case-sensitive and can contain spaces and other "illegal"
characters. However, all SQL statements that refer to quoted identifiers
must be both quoted AND case-correct.
One minor (and sometimes confusing) exception to this rule is where you
have a quoted identifier that was defined in all uppercase, with no illegal
characters and is not a reserved word. In that exceptional case, you will
not get an exception from the database engine if you fail to use the quotes.
What happens in tools and interfacing drivers is often quite
idiosyncratic. Some will enforce quoted identifiers in all cases, some
will give you the option to create objects with or without quoted
identifiers; some driver interfaces might even intecept all of your
statements and insert quotes around all identifiers. These tricks are what
you refer to as "preferences".
However, as far as the database engine is concerned, it doesn't know or
care about "preferences" that have been interposed by third-party
tools. So, if you define your databases with quoted identifiers, you are
stuck with this annoyance. To get rid of it, you will need to build a new
database *without* the quoted identifiers and pump the data from the
annoying version. If you want to retain the ability to create objects with
identifiers that are case-sensitive, or use illegal characters or illegal
words, then the annoyance comes with the territory.
./heLen