RE: table owners

Are You working with ANSI database? Then, this is well-known problem with several ODBC applications (Business Objects, MS Access, etc): these applications use object owners without quotes (like my_user.my_table instead of 'my_user.my_table'). In Informix, user names are case sensitive, while table names are case-insensitive. Informix server converts all SQL statements to the upper case before processing. As a result, my_user.my_table becomes MY_USER.MY_TABLE, and server complains, that database object doesn't exist. There are two workarounds: - use quotes around object owners if possible. - create SYNONYMS for UPPERCASED usernames: CREATE SYNONYM MY_USER.MYTABLE for 'my_user'.my_table ------------------------------------------ Alexey Sonkin > -----Original Message----- > From: Chewbacca [mailto:ct@sylob.com] > Sent: Wednesday, June 02, 2004 2:36 AM > To: informix-list@iiug.org > Subject: table owners > > Hi everyone. > Because of ODBC links with MS Excel, I have to change INFORMIX tables > owners on a new database i'm installing. But i don't know if i have to > change only informations on systables, or if there's links with another > system tables to have an upright DB system. > Please heeeelp ! > sending to informix-list

Alexey Sonkin wrote: > Are You working with ANSI database? > > Then, this is well-known problem with several ODBC > applications (Business Objects, MS Access, etc): > these applications use object owners without quotes > (like my_user.my_table instead of 'my_user.my_table'). > In Informix, user names are case sensitive, while table names are > case-insensitive. There are three problems in those statements. 1. (Typo) 'my_user'.my_table 2. More subtle: should be "my_user".my_table (using double quotes because it is technically a delimited identifier rather than a string). Most people can ignore this subtlety most of the time! 3. User names are not case sensitive unless enclosed in quotes. One of the differences between a MODE ANSI and a regular Informix database is that owner names are converted to upper case unless enclosed in quotes. That is, if I write: CREATE TABLE mine.field (...); Then, in a MODE ANSI database, the systables entry for owner will contain "MINE" but in a non-ANSI database, the entry will be "mine". [Caveat: this is strictly working from memory - I've not formally tested my observations before writing this, thus leaving myself open to demolition by counter-example.] In either type of database, as long as you write mine.field consistently without quotes, you will access the correct table. In a non-ANSI database, simply writing field (no owner name) will also access the correct table. However, if you write CREATE TABLE "mine".field, then you need to be absolutely consistent in using "mine".field to refer to the table (when you add an owner at all) to be safe. In a non-ANSI database, you would get away with writing mine.field, but in an ANSI database, you will not. Conversely, if you wrote CREATE TABLE "MINE".field, then in a non-ANSI database when you referenced the table with an owner, you would need to include the quotes - "MINE".field, but in a MODE ANSI database, you could get away with writing mine.field and still access the correct table. Oh, and for good measure, user name 'informix' gets special treatment; it is not case-converted to upper-case even in a MODE ANSI database, so informix.systables always refers to the table with tabid = 1. > Informix server converts all SQL statements to the upper case > before processing. As a result, my_user.my_table becomes > MY_USER.MY_TABLE, and server complains, that database object > doesn't exist. Not quite. In a MODE ANSI database, the owner name becomes MY_USER, but the table name is in lower case - my_table. > There are two workarounds: > - use quotes around object owners if possible. This works as long as you are consistent. Use double quotes for preference (because it works correctly whether DELIMIDENT is set or not, unlike single quotes). > - create SYNONYMS for UPPERCASED usernames: > > CREATE SYNONYM MY_USER.MYTABLE for 'my_user'.my_table This also works. It seems like overkill, but it works. Or the third workaround: > - always reference the table with no quotes around the object owner. What doesn't work is messing around, sometimes quoting the owner, sometimes not quoting the owner. Inconsistency is going to cause trouble. Note that (in a MODE ANSI database) one of the places where it is critical to specify the owner is in the statement that creates the object; if you don't do that, then the implicit owner is the logged in user (technically, the session authorization identifier), and that is normally in lower-case. So, even if you're logged in as 'mine', it is still important to write: CREATE TABLE mine.field ( ... ); If you omit that qualifier, the entry in the system catalog is left in lower-case. >>-----Original Message----- >>From: Chewbacca [mailto:ct@sylob.com] >> >>Because of ODBC links with MS Excel, I have to change INFORMIX tables >>owners on a new database i'm installing. But i don't know if i have to >>change only informations on systables, or if there's links with another >>system tables to have an upright DB system. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/