ANSI-Compliant Database Restrictions and Case Sensitivity

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page Other Syntax Segments > Owner Name >

ANSI-Compliant Database Restrictions and Case Sensitivity

The following table describes how the database server reads and stores owner when you create, rename, or access a database object.

Owner Name Specification	What the ANSI-Compliant Database Server Does
Omitted	Reads or stores owner exactly as the login name is stored in the system, but returns an error if the user is not the owner.
Specified without quotation marks	Reads or stores owner in uppercase letters
Enclosed between quotation marks	Reads or stores owner exactly as entered. See also Using Quotation Marks and Accessing Information from the System Catalog Tables.

If you specify the owner name when you create or rename a database object in an ANSI-compliant database, you must include the owner name in data access statements. You must include the owner name when you access a database object that you do not own.

Because the database server automatically shifts owner to uppercase letters if not between quotation marks, case-sensitive errors can cause queries to fail. For example, if you are user nancy and you use the following statement, the resulting view has the name nancy.njcust:

CREATE VIEW 'nancy'.njcust AS
   SELECT fname, lname FROM customer WHERE state = 'NJ';

The following SELECT statement fails because it tries to match the name NANCY.njcust to the actual owner and table name of nancy.njcust:

SELECT * FROM nancy.njcust;

In a Dynamic Server distributed query, if the owner name is not between quotation marks, the remote database follows the lettercase convention of the local database. If the local database is ANSI-compliant, then the remote database processes the owner name in uppercase. If the local database is not ANSI- compliant, then the remote database processes the owner name in lowercase.

Tip:

When you use the owner name as one of the selection criteria in a query (for example, WHERE owner = 'kaths'), make sure that the quoted string matches the owner name exactly as it is stored in the database. If the database server cannot find the database object or database, you might need to modify the query so that the quoted string uses uppercase letters (for example, WHERE owner = 'KATHS').

Because owner name is an authorization identifier, rather than an SQL identifier, you can enclose owner between single-quotation marks ( ' ) in SQL statements of a database where the DELIMIDENT environment variable specifies support for delimited identifiers, thereby requiring double-quotes ( " ) around SQL identifiers.