Identifiers

Naming Conventions

The identifier-start is the first character of an SQL identifier. It must be one of the following:

• An uppercase or lowercase letter. A letter is defined as any character that passes validation by the ObjectScript $ZNAME function; by default these are the uppercase letters A through Z (ASCII 65–90), the lowercase letters a through z (ASCII 97–122), and the letters with accent marks (ASCII 192–255, exclusive of ASCII 215 and 247). If you have installed the Unicode version of Caché, you can use any valid Unicode (16-bit) letter character within an SQL identifier. Simple identifiers are not case-sensitive (however, see below). By convention they are represented with initial capital letters.

  The Japanese locale does not support accented Latin letter characters in identifiers. Japanese identifiers may contain (in addition to Japanese characters) the Latin letter characters A-Z and a-z (65–90 and 97–122), and the Greek capital letter characters (913–929 and 931–937).

• An underscore (_).

• A percent sign (%). Caché names beginning with a % character (except those beginning with %Z or %z) are reserved as system elements and should not be used as identifiers. For further details, refer to the chapter “Rules and Guidelines for Identifiers” in the Caché Programming Orientation Guide.

The identifier-part is any of the subsequent characters of an SQL identifier. These remaining characters may consist of zero or more:

• Letters (including Unicode characters).

• Numbers. A number is defined as the digits 0 through 9.

• Underscores (_).

• At signs (@).

• Pound signs (#).

• Dollar signs ($).

Some symbol characters are also used as operators. In SQL, the # sign is used as the modulo operator. In SQL, the underscore character can be used to concatenate two strings; this usage is provided for compatibility with ObjectScript, the preferred SQL concatenation operator is ||. The interpretation of a symbol as an identifier character always take precedence over its interpretation as an operator. Any ambiguity concerning the correct parsing of a symbol character as an operator can be resolved by adding spaces before and after the operator.

A simple identifier cannot contain blank spaces or non-alphanumeric characters (other than those symbol characters specified above). The InterSystems SQL import tool removes blank spaces from imported table names.

Caché SQL includes reserved words that cannot be used as simple identifiers. For a list of these reserved words, see the “Reserved Words” section in the Caché SQL Reference; to test if a word is a reserved word use the $SYSTEM.SQL.IsReservedWord()Opens in a new tab method. However, a delimited identifier can be the same as an SQL reserved word.

Any identifier that does not follow these naming conventions must be represented as a delimited identifier within an SQL statement.

Case of Letters

Caché SQL identifiers by default are not case-sensitive. Caché SQL implements this by comparing identifiers after converting them to all uppercase letters. This has no effect on the actual case of the names being used. (Note that other implementations of SQL may handle case sensitivity of identifiers differently. For this reason, it is recommended that you avoid case-based identifiers.)

Note that cursor names and passwords in Caché SQL are case-sensitive.

Testing Valid Identifiers

Caché provides the IsValidRegularIdentifier()Opens in a new tab method of the %SYSTEM.SQLOpens in a new tab class, which tests whether a string is a valid identifier. It tests both for character usage and for reserved words. It also performs a maximum length test of 200 characters (this is an arbitrary length used to avoid erroneous input; it is not an identifier validation). The following ObjectScript example shows the use of this method:

   WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("Fred")
   WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("%Fred#123")
   WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("%#$@_Fred")
   WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("_1Fred")
   WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("%#$")

   WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("1Fred")
   WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("Fr ed")
   WRITE !,$SYSTEM.SQL.IsValidRegularIdentifier("%alphaup")

The first three method calls return 1, indicating a valid identifier. The fourth and fifth method calls also return 1; these are valid identifiers, although they are not valid for use as table or field names. The last three method calls return 0, indicating an invalid identifier. Two of these are invalid because they violate the character rules — in these cases by beginning with a number or containing a blank. The final method call returns 0 because the specified string is a reserved word. Note that these rule tests are a minimum requirement; they do not certify an identifier as valid for all SQL uses.

This method can also be called as a stored procedure from ODBC or JDBC: %SYSTEM.SQL_IsValidRegularIdentifier("nnnn").

Identifiers and Class Entities

Table names, view names, field names, and index names are used to generate corresponding classes, properties, and indices by stripping out non-alphanumeric symbol characters. This imposes additional restrictions on the use of symbol characters in the names of these SQL entities:

• Identifiers that differ only in their inclusion of symbol characters are valid. Caché generates corresponding unique names by replacing the last alphanumeric character with an integer suffix. For example, myname and my_name generate myname and mynam0, adding my#nam0@ generates mynam1. If you have already defined a name that ends in an integer, Caché handles unique name generation by incrementing to the next unused integer. Refer to the Field Name section of the CREATE TABLE statement for further details.

• Identifiers that have a punctuation character as the first character and a number as the second character are not valid for schema, table, view, and field names. They are valid for index names; see CREATE INDEX for details.

• Identifiers that consist entirely of punctuation characters, or begin with two underscore characters (__name), or contains two pound signs together (nn##nn) are generally invalid as SQL entity names and should be avoided in all contexts.

• Uses of symbol characters in schema and table names are subject to additional considerations and restrictions. Refer to Table Names and Schema Names in the “Defining Tables” chapter of this manual.

By default, the name of an SQL entity (when stripped of its non-alphanumeric symbol characters) is the same as the name of its corresponding entity within a class definition. To make the SQL name different, specify an SQL alias within your class definition. For example:

Property Insert As %String [SqlFieldName = "X_Insert"];

You can configure translation of specific characters in SQL identifiers to other characters in corresponding object identifiers. This facilitates the use of identifiers across environments where the rules for permitted identifier characters differ.

Go to the Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then General SQL Settings (System, Configuration, General SQL Settings). On this screen you can view and edit the current settings of Identifier Translation — From and Identifier Translation — To. When converting an SQL identifier to an Objects identifier at DDL runtime, the characters in the “From” string are converted to the characters in the “To” string.

You can also use the SetDDLIdentifierTranslations()Opens in a new tab method of the %SYSTEM.SQLOpens in a new tab class.

Identifier Length Considerations

The maximum length for SQL identifiers is 128 characters. When Caché maps an SQL identifier to the corresponding object entity, it creates the corresponding property, method, query, or index name with a maximum of 96 characters. If two SQL identifiers are identical for the first 96 characters, Caché replaces the 96th character of the corresponding object name with an integer (beginning with 0) to create a unique name.

The maximum length for schema and table names is subject to additional considerations and restrictions. Refer to Table Names and Schema Names in the “Defining Tables” chapter of this manual.

Disabling Delimited Identifier Support

By default, support is enabled for delimited identifiers.

When delimited identifier support is disabled, characters within double quotes are treated as string literals.

You can set delimited identifier support system-wide using the following:

• The SetDelimitedIdentifiers()Opens in a new tab method of the %SYSTEM.SQLOpens in a new tab class. This method changes both the current system-wide value and the configuration file setting.

• The Management Portal. From System Administration, select Configuration, then SQL and Object Settings, then General SQL Settings. On this screen you can view and edit the current setting of Support Delimited Identifiers.

• The SET OPTION command with the SUPPORT_DELIMITED_IDENTIFIERS keyword.

To determine the current setting, call $SYSTEM.SQL.CurrentSettings()Opens in a new tab.