Home | Previous Page | Next Page   SQL Features > Naming Database Objects >

Non-ASCII Characters in Identifiers

Informix database servers support non-ASCII (wide, 8-bit, and multibyte) characters from the code set of the database locale in most SQL identifiers, such as the names of columns, connections, constraints, databases, indexes, roles, SPL routines, sequences, synonyms, tables, triggers, and views.

Extended Parallel Server

On Extended Parallel Server, use only single-byte characters in the following identifiers:

Use only ASCII alphanumeric 7-bit names for the following identifiers:

End of Extended Parallel Server
Dynamic Server

On Dynamic Server, you can use non-ASCII characters (8-bit and multibyte characters) when you create or refer to any of these database server names:

End of Dynamic Server

The following restrictions affect the ability of the database server to generate filenames that contain non-ASCII characters:

In a database with a nondefault locale, whose code set supports multibyte (or other non-ASCII) characters, you can use those non-ASCII characters when you declare most SQL identifiers, as listed in Table 4.

In the following table, the Type of Identifier column lists various categories of objects that can have SQL identifiers or operating-system identifiers. The SQL Segment column shows the segment that provides the syntax of the identifier in the IBM Informix: Guide to SQL Syntax. The Example Context column lists an SQL statement that can declares or can reference the identifier.

Table 4. SQL Identifiers That Support Non-ASCII Characters
Type of Identifier SQL Segment Example Context
Alias Identifier SELECT
Cast (IDS Only) Expression CREATE CAST
Column name Identifier CREATE TABLE
Connection name Quoted String CONNECT
For more information, see Specifying Quoted Strings.
Constraint name Database Object Name CREATE TABLE
Cursor name Identifier DECLARE
For more information, see Handling Non-ASCII Characters.
Database name Database Object Name CREATE DATABASE
Distinct data type name (IDS Only) Identifier,
Data Type		 CREATE DISTINCT
Filename None LOAD
Function name (IDS Only) Database Object Name CREATE FUNCTION
Host variable None FETCH
For more information, see Handling Non-ASCII Characters.
Index name Database Object Name CREATE INDEX
Opaque data type name (IDS Only) Identifier,
Data Type		 CREATE OPAQUE TYPE
Operator-class name (IDS Only) Database Object Name CREATE OPCLASS
Partition
(IDS Only)		 Identifier ALTER FRAGMENT
Routine name (IDS Only) Database Object Name CREATE FUNCTION
Routine name Database Object Name CREATE PROCEDURE
Role name
(IDS Only)		 Identifier CREATE ROLE
Row data type (IDS Only) Identifier CREATE ROW TYPE
Sequence name (IDS Only) Database Object Name CREATE SEQUENCE
SQL Statement identifier Identifier PREPARE )For more information, see Handling Non-ASCII Characters.)
SPL routine name Database Object Name CREATE PROCEDURE
SPL routine variables None
(language-specific)		 CREATE PROCEDURE FROM
Synonym Database Object Name CREATE SYNONYM
Table name Database Object Name CREATE TABLE
Trigger correlation name Database Object Name CREATE TRIGGER
Trigger name Database Object Name CREATE TRIGGER
View name Database Object Name CREATE VIEW

Qualifiers of SQL Identifiers

The SQL Segment column in Table 4 refers to the segment in the IBM Informix: Guide to SQL Syntax that describes the syntax of the identifier. In many cases, the complete syntax can include other identifiers. For example, the Database Object Name segment shows that the syntax of an index name can also include a database name, a database server name, and an owner name, as well as the unqualified name of the index.

Keep in mind that even if the simple, unqualified name of a database object accepts multibyte characters, other identifiers in the fully-qualified name of that object, such as database@server:owner.index, can include multibyte characters only if they also appear in the previous table. In this example, the database qualifier within the fully-qualified index name can include multibyte characters, but the identifier of the database server that qualifies the index name cannot include multibyte characters.

Owner Names

The owner name is the name of the user (or of a pseudo-user, for an owner like informix that does not correspond to the login name of an actual user) who is associated with the creation of a database object. The owner name qualifies the identifier of the database object, which the owner typically can modify or drop. A synonym for the term owner name is authorization identifier.

American National Standards Institute

The ANSI term for owner name is schema name. In an ANSI-compliant database, you must specify the owner name as a qualifier of the identifier of any database object that you do not own.

End of American National Standards Institute

Non-ASCII characters are not valid in an owner name unless your operating system supports those characters in user names.

UNIX Only

If your database server is on a UNIX system, the owner-name qualifier defaults to the UNIX login ID. Most versions of UNIX, however, do not support multibyte characters in UNIX login IDs.

Warning:
You specify multibyte characters in an owner name at your own risk. If a UNIX login ID is used to match the owner name, the match might fail if the UNIX system does not support multibyte characters in login ID names. In this situation, if you create a database object without explicitly specifying an owner name, the owner name defaults to the UNIX login ID. It will attempt to reference the same database object by qualifying its identifier with an owner name that includes multibyte characters and fail because a string of only single-byte characters cannot match any string containing multibyte characters.
End of UNIX Only

In some East Asian locales, an owner name can include multibyte characters when you create database objects and specify an explicit owner. For example, you can assign an owner name that contains multibyte characters when you specify the owner of an index (within single quotes) in a CREATE INDEX statement. The following statement declares an index with a multibyte owner name. In this example, the owner name consists of three 2-byte characters: 

CREATE INDEX 'A1A2B1B2C1C2'.myidx ON mytable (mycol)

The preceding example assumes that the client locale supports a multibyte code set and that A1A2, B1B2, and C1C2 are valid characters in this code set.

Pathnames and Filenames

Valid pathnames and filenames are operating system-dependent; see Handling Non-ASCII Characters. Multibyte characters in hard-coded pathnames, for example, limits the portability of your application to operating systems that can support multibyte filenames.

Extended Parallel Server

For Extended Parallel Server, only ASCII alphanumeric 7-bit characters are valid in pathnames or in filenames.

End of Extended Parallel Server

Delimited Identifiers

A delimited identifier is an identifier that is enclosed in double quotes. When the DELIMIDENT environment variable is set, the database server interprets strings of characters in double ( " ) quotes as delimited identifiers and strings of characters in single ( ' ) quotes as data strings. This interpretation of single- and double-quotes is compliant with the ANSI/ISO standard for SQL.

In a nondefault locale, you can specify valid non-ASCII characters of the current code set in most delimited identifiers. You can put non-ASCII characters in a delimited identifier if you can put non-ASCII characters in the undelimited form of the same identifier.

For example, Table 4 indicates that you can specify non-ASCII characters in the declaration of an index name. Thus, you can include non-ASCII characters in an undelimited index name, or in an index name that you have enclosed in double quotes to make it a delimited identifier, as in the following SQL statement: 

CREATE INDEX "A1A2#B1B2" ON mytable (mycol)

For a description of delimited identifiers, see the Identifier segment in the IBM Informix: Guide to SQL Syntax.

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]