Using Caché SQL
[Home] [Back] [Next]
InterSystems: The power behind what matters   

An identifier is the name of an SQL entity, such as a table, a view, a column (field), a schema, a table alias, a column alias, an index, a stored procedure, a trigger, or some other SQL entity. An identifier name must be unique within its context; for example, two tables in the same schema, or two fields within the same table cannot have the same name. However, two tables in different schemas, or two fields in different tables can have the same name. In most cases, the same identifier name can be used for SQL entities of different types; for example, a schema, a table in that schema, and a field in that table can all have the same name without conflict. However, a table and a view in the same schema cannot have the same name.

Identifiers follow a set of naming conventions, which may be further restricted according to the use of the identifier. Identifiers are not case-sensitive.
An identifier may be either a simple identifier or a delimited identifier. The Caché SQL default is to support both simple identifiers and delimited identifiers.
Simple Identifiers
A simple identifier has the following syntax:
simple-identifier ::= identifier-start { identifier-part }  
   identifier-start ::= letter | % | _  
   identifier-part ::=  letter | number | _ | @ | # | $
Naming Conventions
The identifier-start is the first character of an SQL identifier. It must be one of the following:
The identifier-part is any of the subsequent characters of an SQL identifier. These remaining characters may consist of zero or more:
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.
SQL cursor names do not follow identifier naming conventions. For details on cursor naming conventions, refer to the DECLARE statement.
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() 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() method of the %SYSTEM.SQL 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").
Namespace Names
A namespace name (also referred to as a database name) follows identifier naming conventions, with additional restrictions on punctuation characters and maximum length. For further details, refer to the CREATE DATABASE command.
A namespace name can be delimited identifier and can be the same as an SQL reserved word. However, the same namespace name punctuation restrictions apply to both simple identifiers and delimited identifiers.
Identifiers and Class Entity Names
SQL table names, view names, field names, index names, trigger names, and procedure names are used to generate corresponding persistent class entities by stripping out non-alphanumeric characters. The generated names of class entities and globals follow these translation rules.
Namespace names and SQL schema names and corresponding package names do not follow these translation rules.
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. Use the SetDDLIdentifierTranslations() method of the %SYSTEM.SQL class. To determine the current setting, call $SYSTEM.SQL.CurrentSettings().
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.
Specifying SQL Names in a Class Definition
When you define a persistent class that projects SQL entities, the name of each SQL entity are the same as the name of its corresponding persistent class definition element. To make an SQL table, field, index, or procedure classmethod name different, use the SqlTableName, SqlFieldName, or SqlName (for an index) keyword to specify the SQL name within your class definition. For example:
Property LName As %String [SqlFieldName = "Family#Name"];
Index NameIdx As %String [SqlName = "FullNameIndex"];
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.
Delimited Identifiers
A delimited identifier has the following syntax:
delimited-identifier ::= " delimited-identifier-part { delimited-identifier-part } "
   delimited-identifier-part ::= non-double-quote-character | double-quote-symbol  
   double-quote-symbol ::= ""
A delimited identifier is a unique identifier enclosed by delimiter characters. Caché SQL supports double quote characters (") as delimiter characters. Delimited identifiers are generally used to avoid the naming restrictions of simple identifiers.
Note that Caché SQL uses single quote characters (') to delimit literals. For this reason, delimited identifiers must be specified using double quote characters ("), and literals must be specified using single quote characters ('). For example, '7' is the numeric literal 7, but "7" is a delimited identifier.
Delimited Identifier Valid Names
A delimited identifier must be a unique name. Delimited identifiers are not case-sensitive; by convention, identifiers are represented with initial capital letters.
A delimited identifier can be the same as an SQL reserved word. Delimited identifiers are commonly used to avoid concerns about naming conflicts with SQL reserved words.
A delimited identifier may contain almost any printable character, including blank spaces. Most delimited identifier names cannot contain the following characters: comma (,), period (.), caret (^), and the two-character arrow sequence (->); however delimited identifier role names and user names may contain these characters. A delimited identifier classname may contain periods (.). No delimited identifier may begin with an asterisk (*). The following term cannot be used as a delimited identifier: %vid. Violating these naming conventions results in an SQLCODE -1 error.
A delimited identifier used as a table, schema, column, or index name must be able to be converted to a valid class entity name. Therefore, it must contain at least one alphanumeric character. A delimited identifier that begins with a number (or punctuation followed by a number) generates a corresponding class entity name with the letter “n” prefix.
The following example shows a query that makes use of delimited identifiers for both column and table names:
SELECT "My Field" FROM "My Table" WHERE "My Field" LIKE 'A%'
Note that the delimited identifiers are delimited with double quotes, and the string literal A% is delimited with single quotes.
When specifying a delimited identifier for a table name, you must separately delimit the table name and the schema name. Thus, "schema"."tablename" or schema."tablename" are valid identifiers, but "schema.tablename" is not a valid identifier.
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:
To determine the current setting, call $SYSTEM.SQL.CurrentSettings().
SQL Reserved Words
SQL includes a long list of reserved words that cannot be used as simple identifiers, but can be used as delimited identifiers. For a list of these reserved words, see the Reserved Words section in the Caché SQL Reference.

Send us comments on this page
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA