If you use the owner name as one of the selection criteria to access
database object information from one of the system catalog tables,
the owner name is case sensitive. To preserve lettercase, you must
enclose
owner in single or double quotation marks, and you
must type the owner name exactly as it is stored in the system catalog
table. Of the following two examples, only the second successfully
accesses information on the table
Kaths.table1.
SELECT * FROM systables WHERE tabname = 'tab1' AND owner = 'kaths';
SELECT * FROM systables WHERE tabname = 'tab1' AND owner = 'Kaths';
User
informix is the owner of the system catalog tables,
and in an ANSI-compliant database you must specify
informix
as a qualifier when SQL statements reference system catalog tables,
unless you are user
informix:
SELECT * FROM "informix".systables WHERE tabname = 'tab1' AND owner = 'Kaths';
Informix® accepts
any of the following notations to specify a system catalog table of
an ANSI-compliant database:
- "informix".system_table
- informix.system_table
- 'informix'.system_table
Of these three formats, however, only the first, where the
owner is
specified as a delimited identifier, is directly interoperable with
most other database servers. For the format with no delimiters, the
ANSI/ISO standard for SQL upshifts the lowercase letters to
INFORMIX,
and the same standard does not support single ( ' ) quotation marks
as valid delimiters for owner names or for schema names.
In contrast, Informix treats
the name informix as a special case, and preserves lowercase
letters when informix is specified, with or without delimiters,
whether or not the database is ANSI-compliant. To write SQL code that
is portable to non-Informix database
servers, however, you should always delimit the owner names of database
objects between double ( " ) quotation marks.
The following SQL examples use undelimited owner names:
CREATE TABLE informix.t1(i SERIAL NOT NULL);
CREATE TABLE someone.t1(i SERIAL NOT NULL);
If these statements execute successfully, the first table has informix registered
in systables as the owner, and the second has SOMEONE registered
as the owner. When the owner name is delimited by quotation marks
in SQL statements, the specified lettercase of owner is preserved,
but the lettercase does not matter when the owner name is undelimited,
because Informix upshifts
most undelimited owner names, but downshifts the undelimited informix (or INFORMIX)
owner name to informix.
For example, suppose that after the previous two CREATE TABLE statements
execute successfully, user
informix issues the following statement:
CREATE TABLE INFORMIX.t1(i SERIAL NOT NULL);
This statement fails, because the combination of owner name
and table name is not unique, if the previously registered
table t1 that is owned by informix already exists in
the database.
Tip: The USER operator returns
the login name of the current user exactly as it is stored on the
system. If the owner name is stored differently from the
login name (for example, a mixed-case owner name and an all lowercase
login name), the owner = USER syntax fails.