IDS Forum

Re: creating users with upper lower case mixture

Posted By: Jonathan Leffler
Date: Sunday, 12 December 2010, at 6:14 p.m.

In Response To: creating users with upper lower case mixture (KARL OLIVER)

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

Messages In This Thread

• creating users with upper lower case mixture
  KARL OLIVER -- Wednesday, 8 December 2010, at 3:55 p.m.
  • Re: creating users with upper lower case mixture
    Art Kagel -- Wednesday, 8 December 2010, at 4:02 p.m.
  • Re: creating users with upper lower case mixture
    KARL OLIVER -- Wednesday, 8 December 2010, at 4:37 p.m.
  • Re: creating users with upper lower case mixture
    obnoxio@serendipita.com -- Thursday, 9 December 2010, at 8:12 p.m.
  • Re: creating users with upper lower case mixture
    Jonathan Leffler -- Sunday, 12 December 2010, at 6:14 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.