Informix Case Sensitivity
Poster wanted uppercase table and column names in IDS 7.3, but Informix folds identifiers to lowercase. Answer: setting the DELIMIDENT environment variable and quoting identifiers with double quotes will preserve case, but several respondents (Leffler, Kagel) strongly advised against it: everything breaks if DELIMIDENT isn't set, all names then need double quotes while strings need single quotes, owner/MODE ANSI issues arise, and system catalog names stay lowercase anyway. Suggested fix was to make the case-sensitive client app use lowercase instead. No final outcome from the poster is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Quick Question... I'm trying to set up a database on Informix Dynamic Server 7.3 and I want the tables names and column names to be in Uppercase. Whenever I type in the table and column names, they are converted to lowercase. Does anyone know how to solve this problem? Thanks! -- _____________________________________________ JEREMY M. SILVER Synchrologic, Inc. 770.754.5600 Ext. 140
In article <19on3.138$kZ2.360@newsfeed.slurp.net>, Jeremy M. Silver <silver.jeremy@synchrologic.com> wrote: >Quick Question... >I'm trying to set up a database on Informix Dynamic Server 7.3 and I want >the tables names and column names >to be in Uppercase. Whenever I type in the table and column names, they are >converted to lowercase. Does anyone know >how to solve this problem? Live with it. Dbspace names must be in lower case, and table names are smashed to lower case. You're also limited to 18 characters ... Jim -- W. Jim Jordan, Nortel Networks, Stop 29CA3A08 | +1 613 763 1568 PO Box 3511 Stn C, Ottawa, ON K1Y 4H7 Canada | wjjordan@nortelnetworks.com Opinions expressed are not necessarily those of Nortel Networks. Inbound spam filtering is in place. Don't send what I won't see.
Jeremy, check the manuals for the DELIMIDENT environment variable. This should allow you to do what you are trying to do. Heiko "Jeremy M. Silver" wrote: > Quick Question... > > I'm trying to set up a database on Informix Dynamic Server 7.3 and I want > the tables names and column names > to be in Uppercase. Whenever I type in the table and column names, they are > converted to lowercase. Does anyone know > how to solve this problem? > > Thanks! > > -- > _____________________________________________ > JEREMY M. SILVER > Synchrologic, Inc. > 770.754.5600 Ext. 140
Heiko Giesselmann wrote: > check the manuals for the DELIMIDENT environment variable. This > should allow you to do what you are trying to do. > > "Jeremy M. Silver" wrote: > > Quick Question... > > > > I'm trying to set up a database on Informix Dynamic Server 7.3 and > > I want the tables names and column names to be in Uppercase. > > Whenever I type in the table and column names, they are > > converted to lowercase. Does anyone know > > how to solve this problem? Heiko is correct, but you almost certainly don't want to do it. Why do you need the names in upper case? You can write them in upper case in SQL code and it will work, even though the names in the system catalog are all in lower case. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>
What exactly is bad about using the DELIMIDENT environment variable? I need uppercase names because the software I use against this informix database is case sensitive (beyond normal SQL calls). Thanks! Jonathan Leffler <jleffler@earthlink.net> wrote in message news:379E8D83.7412@earthlink.net... > Heiko Giesselmann wrote: > > check the manuals for the DELIMIDENT environment variable. This > > should allow you to do what you are trying to do. > > > > "Jeremy M. Silver" wrote: > > > Quick Question... > > > > > > I'm trying to set up a database on Informix Dynamic Server 7.3 and > > > I want the tables names and column names to be in Uppercase. > > > Whenever I type in the table and column names, they are > > > converted to lowercase. Does anyone know > > > how to solve this problem? > > Heiko is correct, but you almost certainly don't want to do it. > Why do you need the names in upper case? You can write them in > upper case in SQL code and it will work, even though the names in > the system catalog are all in lower case. > -- > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN > #include <disclaimer.h> >
DON'T DO IT! There are easier ways to trigger a stroke! Honest. This is nothing but trouble. There will be several situations you will run into that will have you banging your head chanting "Why didn't I listen to Jonathan? Why didn't I listen to Jonathan?" for hours. I can't think of anything short of querying systables directly looking for upper case table names that will not work with Informix's case insensitive table names. Why not just teach that recalcitrant software to use lower case? Art S. Kagel "Jeremy M. Silver" wrote: > > What exactly is bad about using the DELIMIDENT environment variable? I need > uppercase names because > the software I use against this informix database is case sensitive (beyond > normal SQL calls). > > Thanks! > > Jonathan Leffler <jleffler@earthlink.net> wrote in message > news:379E8D83.7412@earthlink.net... > > Heiko Giesselmann wrote: > > > check the manuals for the DELIMIDENT environment variable. This > > > should allow you to do what you are trying to do. > > > > > > "Jeremy M. Silver" wrote: > > > > Quick Question... > > > > > > > > I'm trying to set up a database on Informix Dynamic Server 7.3 and > > > > I want the tables names and column names to be in Uppercase. > > > > Whenever I type in the table and column names, they are > > > > converted to lowercase. Does anyone know > > > > how to solve this problem? > > > > Heiko is correct, but you almost certainly don't want to do it. > > Why do you need the names in upper case? You can write them in > > upper case in SQL code and it will work, even though the names in > > the system catalog are all in lower case. > > -- > > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > > Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN > > #include <disclaimer.h> > >
Jeremy M. Silver wrote: > What exactly is bad about using the DELIMIDENT environment variable? > I need uppercase names because the software I use against this > informix database is case sensitive (beyond normal SQL calls). How? Why? Anyway, the main problem is that if you ever try running without DELIMIDENT set, nothing will work. Also, you have to be careful to use double quotes around all the upper case names, and single quotes when you want strings. So, for example, you have to write: SELECT "COL1", 'String', "COL2" FROM "owner"."TABLENAME" WHERE "COL3" = 'Ugh!'; Or maybe "owner" should be "OWNER"; that depends on how the CREATE TABLE statement is written. Oh, and using a MODE ANSI database can confuse the issue. Don't forget that the system catalogue tables will all be in lower case regardless, and that's the most probable place that the application could be looking for case-sensitive data. So, there's no way I'd try to do it without a detailed justification of why your application really, really, absolutely has to use upper case table and column names. And the changes required to the application are likely to be such that it is easier to relax the requirement for upper-case names than to make it work properly with upper-case names. > Jonathan Leffler <jleffler@earthlink.net> wrote: > > Heiko Giesselmann wrote: > > > check the manuals for the DELIMIDENT environment variable. This > > > should allow you to do what you are trying to do. > > > > > > "Jeremy M. Silver" wrote: > > > > Quick Question... > > > > > > > > I'm trying to set up a database on Informix Dynamic Server 7.3 > > > > and I want the tables names and column names to be in Upper > > > > case. Whenever I type in the table and column names, they are > > > > converted to lowercase. Does anyone know how to solve this > > > > problem? > > > > Heiko is correct, but you almost certainly don't want to do it. > > Why do you need the names in upper case? You can write them in > > upper case in SQL code and it will work, even though the names in > > the system catalog are all in lower case. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>