Re: case sensitivity when creating a table. (corrected)
On Thu, Feb 20, 2003 at 02:36:04PM -0800, Jeff Zucker wrote: Jeff Zucker wrote: There is no database independent way to match a delimited identifier to an undelimited identifier. That part of my previous post is correct, but my examples were bad. Here is a better explanation with examples from two differing implementations: In the ANSI standard, a delimited identifier is equal to an undelimited identifier if it matches *case-sensitively* to the *upper-cased* undelimited identifier. Because undelimited identifiers are stored in the catalogs uppercased. In PostgreSQL, a delimited identifier is equal to an undelimited identifier if it matches *case-sensitively* to the *lower-cased* undelimited identifier. Because undelimited identifiers are stored in the catalogs lowercased. In ODBC, a delimited identifier is equal to an undelimited identifiers if it matches *case-insenitively*. By ODBC I presume you mean windows ODBC using windows database like Access or MSSQL, because on windows case-insenitivity is the norm. I don't think it's part of the ODBC standard - but I've not checked (please correct me if I'm wrong). Using ODBC to talk to Oracle or PostgreSQL etc I think you'll find the other behaviours described above. Tim.
Re: case sensitivity when creating a table. (corrected)
Tim Bunce wrote: In ODBC, a delimited identifier is equal to an undelimited identifiers if it matches *case-insenitively*. By ODBC I presume you mean windows ODBC using windows database like Access or MSSQL, because on windows case-insenitivity is the norm. Yes, correct, I checked against the ODBC jet text driver and the ODBC MS-Access driver on windows and was sloppy not to specify I was talking about those rather than ODBC in general. I don't think it's part of the ODBC standard - but I've not checked I think you're probably right, but I've not checked either :-). -- Jeff
Re: case sensitivity when creating a table.
Francois Desarmenien wrote: On Thu, 20 Feb 2003 14:44:02 -0500 Jose Blanco [EMAIL PROTECTED] wrote: Any ideas why SQL statements like this one: SELECT collid FROM Collection where collid= '123' and (userid = 'JoseA' or userid = 'JoseB') Are not working with SQL::Statement version 1.005, but are with version 0.1021? Is this another bug with this release. I'll investigate and let you know. Bug or not, it is dartabase dependend and you should double quote Collection. It's the safe portable way when mixing uppercases and lowercases in table names (or columns). That is incorrect. The undelimited table name Collection matches an undelimited table of that name in any case (e.g. COLLECTION, collection, Collection). And delimiting the table name is not a portable way to match it to an undelimited name. There is no database independent way to match a delimited identifier to an undelimited identifier. The ANSI standard specifies that a delimited identifier is equal only to an *upper case* non-delimited identifier thus Collection matches only COLLECTION and Collection, not Collection in the standard. In practice though, implemnetations vary so in some Collection matches COLLECTION and in some in matches collection. In SQL::Statement (as specified in its documentation) it matches only if the table name was declared as a delimited identifier in the first place. -- Jeff
Re: case sensitivity when creating a table.
On Thu, 20 Feb 2003, Jeff Zucker wrote: Francois Desarmenien wrote: On Thu, 20 Feb 2003 14:44:02 -0500 Jose Blanco [EMAIL PROTECTED] wrote: Any ideas why SQL statements like this one: SELECT collid FROM Collection where collid= '123' and (userid = 'JoseA' or userid = 'JoseB') Are not working with SQL::Statement version 1.005, but are with version 0.1021? Is this another bug with this release. I'll investigate and let you know. Bug or not, it is dartabase dependend and you should double quote Collection. It's the safe portable way when mixing uppercases and lowercases in table names (or columns). That is incorrect. The undelimited table name Collection matches an undelimited table of that name in any case (e.g. COLLECTION, collection, Collection). And delimiting the table name is not a portable way to match it to an undelimited name. There is no database independent way to match a delimited identifier to an undelimited identifier. The ANSI standard specifies that a delimited identifier is equal only to an *upper case* non-delimited identifier thus Collection matches only COLLECTION and Collection, not Collection in the standard. In I am confused. IIRC, SQL-99 (I don't have a 92 ref) says that delimited identifiers are case sensitive so Collection only matches Collection; however, COLLECTION will match Collection (collection COLLECTION) because each character of the regular identifier Collection is replaced with its upper case equivalent before being compaired with the delimited identifier COLLECTION. -r
Re: case sensitivity when creating a table.
Rudy Lippan wrote: I am confused. IIRC, SQL-99 (I don't have a 92 ref) says that delimited identifiers are case sensitive so Collection only matches Collection; however, COLLECTION will match Collection (collection COLLECTION) because each character of the regular identifier Collection is replaced with its upper case equivalent before being compaired with the delimited identifier COLLECTION. Yes, you are correct. My examples were bad in my first post. I realized that shortly after sending and sent a correction. See my corrected post which agrees with your understanding of the standard but which also shows that the standard is ignored in at least two common implementations. -- Jeff
Re: case sensitivity when creating a table. (corrected)
Jeff Zucker wrote: There is no database independent way to match a delimited identifier to an undelimited identifier. That part of my previous post is correct, but my examples were bad. Here is a better explanation with examples from two differing implementations: In the ANSI standard, a delimited identifier is equal to an undelimited identifier if it matches *case-sensitively* to the *upper-cased* undelimited identifier. In PostgreSQL, a delimited identifier is equal to an undelimited identifier if it matches *case-sensitively* to the *lower-cased* undelimited identifier. In ODBC, a delimited identifier is equal to an undelimited identifiers if it matches *case-insenitively*. Foo = FOO (in ANSI and ODBC but not in PG) Foo = foo (in PG and ODBC but not in ANSI) Foo = Foo (in ODBC but not in PG or ANSI) -- Jeff
