At 9:50 PM -0300 10/17/05, [EMAIL PROTECTED] wrote:
I need to use a quoted identifiers and sqlite
support it well but when i obtain
the data from the database the cursos give to me
the fields with quotes. When i
do this directly there is no problem because i can remove the quotes, but i
access to sqlite through delphi using zeosdbo and i can´t touch the resultset
directly.
I don´t think this a useful thing, there isn´t any other database which
behavior like that, maybe the next versions will avoid that.
It sounds like Delphi needs to be fixed, then;
having quotes *is* standard, and many databases
support it, including both MySQL and Oracle.
In the SQL:2003 standard, delimited/quoted
identifiers are a different beast than bareword
identifiers. Delimited identifiers can contain
any characters at all, just as a string literal
can, including whitespace and punctuation, and
they are case-sensitive; they are always defined
and invoked using the delimiters, which are (")
usually, but MySQL uses (`) instead, and both are
different than the string literal delimiter of
('). Non-delimited identifiers can not contain
whitespace or most punctuation and can not be
plain numbers, and they are case-insensitive;
likewise, usually defined and invoked without
delimiters. As far as I know, SQL:2003 allows
you to invoke non-delimited identifiers using the
delimited format, in which case any non-delimited
identifiers match in their folded-to-uppercase
form; in this respect, non-delimited is a full
and clearly defined subset of delimited, so if
they are internally stored like character
strings, everything would just work.
And before anyone says that identifiers
containing whitespace is a stupid idea, I counter
that thought. From a non-programmer user
perspective, people who create databases using
GUI tools and that type names of tables and
fields into individual GUI form boxes, it is just
as natural to use spaces and punctuation as it is
to put those in file system file names, usually
done in a GUI. So it is all well and proper to
support this internally and expose it where
possible. Database design is not programming and
should not be subjected to the same limitations;
those elements are not variable or function
names. And even when we are programming to a
database, we are often writing programs that use
a data dictionary and/or are data driven, and
used by non-programmers. When we are composing
SQL directly, adding those delimiters is very
easy.
SQLite needs to default to the most compatible
and representitive format there is, which is the
delimited identifiers. Alternate output
behaviour can be accomplished by a connection or
statement specific pragma, or a wrapper.
-- Darren Duncan
