creating users with upper lower case mixture

Folks Struck a problem I have never met before. I created a connection for a user JBoss - yes they insisted mixture of upper/lower case. This was accepted ok however when looking at sysusers table it had been converted to all lower case. So to get informix to accept the upper case i had to enter the name in quotes as "JBoss" this then worked, Why is this ?what governs the conversion to lower case. Could we expereince problems in the future with access by this user ? I have done the normal google serach but cant find anything

Unless DELIMIDENT is set in the server's environment all identifiers are converted to lower case and cannot contain 'special' characters other than underscore. Always worked that way. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Dec 8, 2010 at 3:55 PM, KARL OLIVER <karl.oliver@maf.govt.nz> wrote: > Folks > Struck a problem I have never met before. > I created a connection for a user JBoss - yes they insisted mixture of > upper/lower case. > This was accepted ok however when looking at sysusers table it had been > converted to all lower case. So to get informix to accept the upper case i > had > to enter the name in quotes as "JBoss" this then worked, > Why is this ?what governs the conversion to lower case. Could we expereince > problems in the future with access by this user ? > I have done the normal google serach but cant find anything > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd23cd0ffd89f0496ec71a6

Art Thanks for that information

On 08/12/2010 20:55, KARL OLIVER wrote: > Folks > Struck a problem I have never met before. > I created a connection for a user JBoss - yes they insisted mixture of > upper/lower case. > This was accepted ok however when looking at sysusers table it had been > converted to all lower case. So to get informix to accept the upper case i had > to enter the name in quotes as "JBoss" this then worked, > Why is this ?what governs the conversion to lower case. Could we expereince > problems in the future with access by this user ? Google DELIMIDENT -- Cheers, Obnoxio The Clown [1]http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish. References 1. http://obotheclown.blogspot.com/

On Wed, Dec 8, 2010 at 12:55, KARL OLIVER <karl.oliver@maf.govt.nz> wrote: > Struck a problem I have never met before. > I created a connection for a user JBoss - yes they insisted mixture of > upper/lower case. > This was accepted ok however when looking at sysusers table it had been > converted to all lower case. So to get informix to accept the upper case I > had > to enter the name in quotes as "JBoss" this then worked, > Why is this ?what governs the conversion to lower case. Could we experience > problems in the future with access by this user ? > I have done the normal google search but cant find anything > Let me illustrate (because I don't agree with the other answers you've been given): This happens to be a logged database; the DELIMIDENT environment variable is not set. SQL[1178]: create table "xP[2]".table(s SERIAL PRIMARY KEY, v VARCHAR(255) NOT NULL); SQL[1179]: create table 'xP[2]'.decimal(i integer, d date); SQL[1180]: insert into table values(10101, "what is this for"); SQL[1181]: insert into decimal values(1, '2001-01-01'); SQL[1182]: create table JL.UPPER_CASE(S SERIAL, V VARCHAR(255) NOT NULL); SQL[1183]: info tables where tabid > 105; xP[2]|decimal|T|107 xP[2]|table|T|106 jl|upper_case|T|108 SQL[1184]: What this shows is that you can enclose an owner name in quotes - single or double quotes - and that name is then used case-sensitively. When you quote a name, it must be of the correct case. In the first two statements, the table owner is the same 'xP[2]' character...only a DBA can create table on behalf of other people, of course, so I'm running as a DBA in this database. The third statement shows that in a 'normal' (meaning not MODE ANSI) database, if you enter the name without quotes, it is case-insensitive and the name is converted to lower-case before being stored in the system catalog. If I switch to a MODE ANSI database and re-execute the last create table statement, I get: SQL[1186]: connect to 'mode_ansi' wct; SQL[1187]: create table JL.UPPER_CASE(S SERIAL, V VARCHAR(255) NOT NULL); SQL[1188]: r 1184; JL|upper_case|T|106 (I'm using SQLCMD, of course, and "wct" is an abbreviation for WITH CONCURRENT TRANSACTIONS; I'm too lazy to type it all). Here, you can see that the unquoted user name JL is stored in upper-case. I could have typed jl instead of JL and it would still have been case-converted. If the name is in quotes, it would be stored unchanged, though. Note that the table name is still stored in lower case - in MODE ANSI and non-ANSI databases. Also, by virtue of special treatment, the user name informix, unquoted, is left in lower case and not converted to upper case. Note that column names are also case-converted to lower case. Now, what about DELIMIDENT? Well, with DELIMIDENT set, strings must be enclosed in single quotes; anything inside double quotes is taken to be a 'delimited identifier', not a string. (This is what the SQL standard requires.) And, with DELIMIDENT set, you can have case-sensitive column names and table names. Interestingly, though, the owner names can still be single quoted or double quoted. Delimited identifiers are not case-converted. This is in the logged database again: + create table "xP[2]".table(s SERIAL PRIMARY KEY, v VARCHAR(255) NOT NULL); + create table 'xP[2]'.decimal(i integer, d date); + insert into table values(10101, 'what is this for'); + insert into decimal values(1, '2001-01-01'); + create table "JL"."UPPER_CASE"(S SERIAL, V VARCHAR(255) NOT NULL); + info tables where tabid > 105; + SELECT Owner, TabName, TabType, TabID FROM "informix".SysTables T WHERE TabID >= 100 AND TabType IN ('T', 'V', 'P', 'S') AND ( tabid > 105) ORDER BY TabName, Owner JL|UPPER_CASE|T|118 xP[2]|decimal|T|117 xP[2]|table|T|116 Note that I had to change the quotes around "what is this for" to single quotes. The upshot is that as long as you are consistent, you won't run into trouble. If you are not consistent, mixed case names, or names with spaces etc in them, can wreak havoc on the unprepared (such as DB-Export etc). As an extreme example of a delimited identifier, you can create a table owned by the user with the name consisting of 32 double quotes - specified by an opening double quote, 32 pairs of double quotes, plus a closing double quote. You can also create a table with the name consisting of 128 double quotes - specified by an open double quote, 128 pairs of double quotes, and a closing double quote. That requires a total of 324 bytes for the name, plus 2 more (one for the dot and the other for the terminating null. Few people allocate enough space to handle that name; the DB-Export and DB-Import tools also dislike the names (but IDS itself works very happily with the names). -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --00504502e485f9ec7b04973ec051