Defining Tables

System-wide Default Schema

A table name is either qualified (schema.tablename) or unqualified (tablename).

• When performing a DDL operation, such as creating or deleting a table, view, trigger, or stored procedure, an unqualified name is supplied the system-wide default schema name. Schema search path values are ignored.

• When performing a DML operation, such as a SELECT, CALL, INSERT, UPDATE, or DELETE to access an existing table, view, or stored procedure, an unqualified name is supplied the schema name from the schema search path (if provided). If there is no schema search path, or the named item is not located using the schema search path, the system-wide default schema name is supplied.

If you create a table or other item with an unqualified name, Caché assigns it the system-wide default schema name, and the corresponding persistent class package name. If a named or default schema does not exist, Caché creates the schema (and package) and assigns the created item to the schema. If you delete the last item in a schema, Caché deletes the schema (and package). The following description of schema name resolution applies to table names, view names, and stored procedure names.

The initial system-wide default schema name is SQLUser. The corresponding persistent class package name is User. Therefore, either the unqualified table name Employee or the qualified table name SQLUser.Employee would generate the class User.Employee. For this reason, attempting to specify a schema name of User results in an SQLCODE error.

To return the current system-wide default schema name, invoke the $SYSTEM.SQL.DefaultSchema()Opens in a new tab method:

  WRITE $SYSTEM.SQL.DefaultSchema()

You can change the schema default using either of the following:

• The $SYSTEM.SQL.SetDefaultSchema()Opens in a new tab method.

• Go to 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 Default SQL Schema Name.

The schema name is used to generate the corresponding class package name. Because these names have different naming conventions, they may not be identical.

Schema Search Path

When accessing an existing table (or view, or stored procedure) for a DML operation, an unqualified name is supplied the schema name from the schema search path. Schemas are searched in the order specified and the first match is returned. If no match is found in the schemas specified in the search path, or no search path exists, the system-wide default schema is used. (Note that the #Import macro directive uses a different search strategy and does not “fall through” to the system-wide default schema.)

• In Embedded SQL you can use the #SQLCompile Path macro directive or the #Import macro directive to supply a schema search path that Caché uses to resolve unqualified names. #SQLCompile Path resolves an unqualified name with the first match encountered. #Import resolves an unqualified name if there is exactly one match for all the schemas listed in the search path.

• The following example provides a search path containing two schema names:

  #SQLCompile Path=Customers,Employees

  

  For further details, refer to “ObjectScript Macros and the Macro Preprocessor” in Using Caché ObjectScript.

• In Dynamic SQL you can use the %SchemaPath property to supply a schema search path that Caché uses to resolve unqualified table names. You can specify the %SchemaPath property directly or specify it as the second parameter of the %SQL.StatementOpens in a new tab %New()Opens in a new tab method. The following example provides a search path containing two schema names:

    SET tStatement = ##class(%SQL.Statement).%New(0,"Customers,Employees")

  

  For further details, refer to “Using Dynamic SQL” in Using Caché SQL.

• In SQL Shell you can set the PATH SQL Shell configuration parameter to supply a schema search path that Caché uses to resolve unqualified names.

If the unqualified name does not match any of the schemas specified in the schema search path or the system-wide schema default, an SQLCODE -30 error is issued, such as the following: SQLCODE: -30 Message: Table 'PEOPLE' not found within schemas: CUSTOMERS,EMPLOYEES,SQLUSER.

Schema Naming Considerations

Schema names follow identifier conventions, with significant considerations concerning the use of non-alphanumeric characters. A schema name cannot be specified as a delimited identifier. Attempting to specify “USER” or any other SQL reserved word as a schema name results in an SQLCODE -312 error. The INFORMATION_SCHEMA schema name and the corresponding INFORMATION.SCHEMA package name are reserved in all namespaces. Users should not create tables/classes within this schema/package.

When you issue a create operation, such as CREATE TABLE, that specifies a schema that does not yet exist, Caché creates the new schema. Caché uses the schema name to generate a corresponding package name. Because the naming conventions for schemas and their corresponding packages differ, the user should be aware of name conversion considerations for non-alphanumeric characters. These name conversion considerations are not the same as for tables:

• Initial character:

  • % (percent): Specify % as the first character of a schema name denotes the corresponding package as a system package, and all of its classes as system classes. This usage requires appropriate privileges; otherwise, this usage issues an SQLCODE -400 error with the %msg indicating a <PROTECT> error.

  • _ (underscore): If the first character of a schema name is the underscore character, this character is replaced by a lowercase “u” in the corresponding package name. For example, the schema name _MySchema generates the package name uMySchema.

• Subsequent characters:

  • _ (underscore): If any character other than the first character of a schema name is the underscore character, this character is replaced by a period (.) in the corresponding package name. Because a period is the class delimiter, an underscore divides a schema into a package and a sub-package. Thus My_Schema generates the package My containing the package Schema (My.Schema).

  • @, #, $ characters: If a schema name contains any of these characters, these characters are stripped from the corresponding package name. If stripping these characters would produce a duplicate package name, the stripped package name is further modified: the final character of the stripped schema name is replaced by a sequential integer (beginning with 0) to produce a unique package name. Thus My@#$Schema generates package MySchema, and subsequently creating My#$Schema generates package MySchem0. The same rules apply to table name corresponding class names.

Platform-Specific Schema Names

When creating an ODBC-based query to run from Microsoft Excel via Microsoft Query on the Mac, if you choose a table from the list of those available, the generated query does not include the table’s schema (equivalent to the package for a class). For example, if you choose to return all the rows of the Person table from the Sample schema (in the Samples namespace), the generated query is:

SELECT * FROM Person

Because Caché interprets an unqualified table name as being in the SQLUser schema, this statement either fails or returns data from the wrong table. To correct this, edit the query (on the SQL View tab) to explicitly refer to the desired schema. The query should then be:

SELECT * FROM Sample.Person

Table Naming Considerations

Table names follow identifier conventions, with significant considerations concerning the use of non-alphanumeric characters.

When you use the CREATE TABLE command to create a table, Caché uses the table name to generate a corresponding class name. Because the naming conventions for tables and their corresponding classes differ, the user should be aware of name conversion considerations for non-alphanumeric characters:

• Initial character:

  • % (percent): Specify % as the first character of a table name denotes the corresponding package as a system class. This usage requires appropriate privileges; otherwise, this usage issues an SQLCODE -400 error with the %msg indicating a <PROTECT> error.

  • _ (underscore): If the first character of a table name is the underscore character, this character is stripped from the corresponding package name. For example, the table name _MyTable generates the class name MyTable.

  • Numbers: The first character of a table name cannot be a number. If the first character of the table name is a punctuation character, the second character cannot be a number. This results in an SQLCODE -400 error, with a %msg value of “ERROR #5053: Class name 'schema.name' is invalid” (without the punctuation character). For example, specifying the table name _7A generates the %msg “ERROR #5053: Class name 'User.7A' is invalid”.

• Subsequent characters:

  • Letters: A table name must include at least one letter. Either the first character of the table name or the first character after initial punctuation characters must be a letter. A character is a valid letter if it passes the $ZNAME test; $ZNAME letter validation differs for different locales. (Note that $ZNAME cannot be used validate SQL table and field names because SQL identifiers can contain the underscore character (_), which $ZNAME does not consider a valid name character.)

  • _ (underscore), @, #, $ characters: If a table name contains any of these characters, these characters are stripped from the corresponding class name. If stripping these characters would produce a duplicate class name, the stripped class name is further modified: the final character of the stripped table name is replaced by a sequential integer (beginning with 0) to produce a class name that is unique within that package. Thus My@#$_Table generates class MyTable, and subsequently creating My_#$Table generates package MyTabl0. Because generated class names do not include punctuation characters, it is not advisable to create table names that differ only in their punctuation characters.

• A table name must be unique within its schema. Attempting to create a table with a name that differs only in letter case from an existing table generates an SQLCODE -201 error.

  A view and a table in the same schema cannot have the same name.

  You can determine if a table name already exists using the $SYSTEM.SQL.TableExists()Opens in a new tab method. This method also returns the class name corresponding to the table name.

Attempting to specify “USER” or any other SQL reserved word as a table name or schema name results in an SQLCODE -312 error. To specify an SQL reserved word as a table name or schema name, you can specify the name as a delimited identifier. If you use a delimited identifier to specify a table or schema name that contains non-alphanumeric characters, Caché strips out these non-alphanumeric characters when generating the corresponding class or package name.

The following table name length limits apply:

• Uniqueness: Caché performs uniqueness checking on the first 59 alphanumeric characters of the table class name. The corresponding SQL table name may be more than 59 characters long, but, when stripped of non-alphanumeric characters, it must be unique within this 59 character limit. Caché performs uniqueness checking on the first 189 characters of a package name.

• Recommended maximum length: a table name should not exceed 128 characters. A table name may be much longer than 96 characters, but table names that differ in their first 96 alphanumeric characters are much easier to work with.

• Combined maximum length: a table name and its schema name (when added together) cannot exceed 220 characters, including the dot character separating the schema name and the table name.

For further details on table names, refer to the CREATE TABLE command in the Caché SQL Reference. For further details, on classes refer to “Caché Classes” in the Using Caché Objects manual.

RowVersion Field

The RowVersion field is an optional user-defined field that provides row-level version control, allowing you to determine the order in which changes were made to the data in each row namespace-wide. Caché maintains a namespace-wide counter, and assigns a unique incremental positive integer to this field each time the row data is modified (insert, update, or %Save). Because this counter is namespace-wide, an operation on one table with a ROWVERSION field sets the increment point for the ROWVERSION counter that is used for all other tables with a ROWVERSION field in the same namespace.

You create a RowVersion field by specifying a field of data type ROWVERSION. You can only specify one ROWVERSION data type field per table. Attempting to create a table with more than one ROWVERSION field results in a 5320 compilation error.

This field can have any name and can appear in any column position. The ROWVERSION (%Library.RowVersionOpens in a new tab) data type maps to BIGINT (%Library.BigIntOpens in a new tab).

This field receives a positive integer from an automatically incremented counter, starting with 1. This counter increments whenever data in any ROWVERSION-enabled table is modified by an insert, update, or %Save operation. The incremented value is recorded in the ROWVERSION field of the row that has been inserted or updated.

A namespace can contain tables with a RowVersion field and tables without this field. Only data changes to tables that have a RowVersion field increment the namespace-wide counter.

When a table is populated with data, Caché assigns sequential integers to this field for each inserted row. If you use ALTER TABLE to add a ROWVERSION field to a table that already contains data, this field is created as NULL for pre-existing fields. Any subsequent insert or update to the table assigns a sequential integer to the RowVersion field for that row. This field is read-only; attempting to modify a RowVersion value generates an SQLCODE -138 error: Cannot INSERT/UPDATE a value for a read only field. Therefore, a RowVersion field is defined as unique and non-modifible, but not required or non-null.

RowVersion values always increment. They are not reused. Therefore, inserts and updates assign unique RowVersion values in temporal sequence. Delete operations remove numbers from this sequence. Therefore, RowVersion values may not be numerically contiguous.

This counter is never reset. Deleting all table data does not reset the RowVersion counter. Even dropping all tables in the namespace that contain a ROWVERSION field does not reset this counter.

The RowVersion field should not be included in a unique key or primary key. The RowVersion field cannot be part of an IDKey index.

The RowVersion field is not hidden (it is displayed by SELECT *).

This is shown in the following example of three tables in the same namespace.

1. Create Table1 and Table3, each of which has a ROWVERSION field, and Table2 that does not have a ROWVERSION field.

2. Insert ten rows into Table1. The ROWVERSION values of these rows are the next ten counter increments. Since the counter has not previously been used, they are 1 through 10.

3. Insert ten rows into Table2. Because Table2 does not have a ROWVERSION field, the counter is not incremented.

4. Update a row of Table1. The ROWVERSION values for this row is changed to the next counter increment (11 in this case).

5. Insert ten rows into Table3. The ROWVERSION values of these rows are the next ten counter increments (12 through 21).

6. Update a row of Table1. The ROWVERSION values for this row is changed to the next counter increment (22 in this case).

7. Delete a row of Table1. The ROWVERSION counter is unchanged.

8. Update a row of Table3. The ROWVERSION values for this row is changed to the next counter increment (23 in this case).

Serial Field

You can use the SERIAL data type (%Library.CounterOpens in a new tab in a persistent class table definition) to specify one or more optional integer counter fields to record the order of inserts of records into a table. By default, this field receives a positive integer from an automatically incremented table counter whenever a row is inserted into the table. However, a user can specify an integer value for this field during an insert, overriding the table counter default.

• If an INSERT does not specify a value for the counter field, it automatically receives a positive integer counter value. Counting starts from 1. Each successive value is an increment of 1 from the highest allocated counter value for this field.

• If an INSERT specifies an integer value for the counter field, the field receives that value. It can be a positive or negative integer value, can be lower or higher than the current counter value, and can be an integer already assigned to this field. If this value is higher than any assigned counter value, it sets the increment starting point for the next automatically assigned counter to that value.

Attempting to UPDATE a counter field value results in an SQLCODE -105 error.

This counter is reset to 1 by the TRUNCATE TABLE command. It is not reset by a DELETE command, even when the DELETE command deletes all rows in the table.

Enumerated Property Values

Defining at table as a persistent class allows you to define properties (fields) that can only contain certain specified values. This is done by specifying the VALUELIST parameter, as shown in the following example:

Class MyApp.Person Extends %Persistent 
{
Property Name As %String(MAXLEN=50) [Required];
Property DateOfBirth As %Date;
Property Sex As %String(VALUELIST=",M,F");
}

If VALUELIST is specified, an INSERT or UPDATE can only specify one of the values listed in VALUELIST, or be provided with no value (NULL). VALUELIST valid values are case-sensitive. Specifying a data value that doesn’t match the VALUELIST values results in a field value failed validation error: SQLCODE -104 for INSERT or SQLCODE -105 for UPDATE.

CREATE TABLE does not provide this functionality.

Unique Values

CREATE TABLE allows you to define a field as UNIQUE. This means that every field value is a unique (non-duplicate) value.

Defining at table as a persistent class does not support a corresponding property keyword. Instead, you must define both the property and a unique index on that property. The following example provides for a unique Num value for each record.

  Class Sample.CaveDwellers Extends %Persistent [ DdlAllowed ]
  { 
  Property Num As %Integer;
  Property Troglodyte As %String(MAXLEN=50);
  Index UniqueNumIdx On Num [ Type=index,Unique ];
  }

Having a unique value field is necessary for using the INSERT OR UPDATE statement.

For reference material on class property keywords, refer to the “Property Keywords” chapter of Caché Class Definition Reference.

Computed Values

The following class definition example defines a table that includes a field (Birthday) that uses SqlComputed to compute its value when you initially set the DateOfBirth field value and SqlComputeOnChange to recompute its value when you update the DateOfBirth field value. The Birthday field value includes the current timestamp to record when this field value was computed/recomputed:

Class Sample.MyStudents Extends %Persistent [DdlAllowed]
{
  Property Name As %String(MAXLEN=50) [Required];
  Property DateOfBirth As %Date;
  Property Birthday As %String 
          [ SqlComputeCode = {SET {Birthday}=$PIECE($ZDATE({DateOfBirth},9),",")_
                              " changed: "_$ZTIMESTAMP},
                              SqlComputed, SqlComputeOnChange = DateOfBirth ];
}

Note that an UPDATE to DateOfBirth that specifies the existing DateOfBirth value does not recompute the Birthday field value. For the corresponding SQL code, refer to the COMPUTECODE section of the CREATE TABLE reference page.

For reference material on class property keywords, refer to the “Property Keywords” chapter of Caché Class Definition Reference.

Embedded Object (%SerialObject)

You can simplify the structure of a persistent table by referencing an embedded serial object class that defines properties. For example, you want the MyData.Person to contain address information, consisting of street, city, state, and postal code. Rather than specifying these properties in MyData.Person, you can define a serial object (%SerialObject) class that defines these properties, and then in MyData.Person specify a single Home property that references that embedded object. This is shown in the following class definitions:

Class MyData.Person Extends (%Persistent) [ DdlAllowed ]
{  Property Name As %String(MAXLEN=50);
   Property Home As MyData.Address;
   Property Age As %Integer;
} 

Class MyData.Address Extends (%SerialObject)
{  Property Street As %String;
   Property City As %String;
   Property State As %String;
   Property PostalCode As %String;
 }

You cannot access the data in a serial object property directly, you must access them through a persistent class/table that references it:

• To refer to an individual serial object property from the persistent table, use an underscore. For example, SELECT Name, Home_State FROM MyData.Person returns the State serial object property value as a string. Serial object property values are returned in the order specified in the query.

• To refer to all of the serial object properties from the persistent table, specify the referencing field. For example, SELECT Home FROM MyData.Person returns values of all of the MyData.Address properties as a %List structure. Serial object property values are returned in the order specified in the serial object: Home_Street, Home_City, Home_State, Home_PostalCode.

• A SELECT * for a persistent class returns all of the serial object properties individually, including nested serial objects. For example, SELECT * FROM MyData.Person returns Age, Name, Home_City, Home_PostalCode, Home_State, and Home_Street values (in that order); it does not return the Home %List structure value. Serial object property values are returned in collation sequence. SELECT * first lists all of the fields in the persistent class in collation sequence (commonly alphabetical order), followed by the nested serial object properties in collation sequence.

Note that an embedded serial object does not have to be in the same package as the persistent table that references it.

Defining embedded objects can simplify persistent table definitions:

• A persistent table can contain multiple properties that reference different records in the same embedded object. For example, the MyData.Person table can contain a Home and an Office property, both of which reference the MyData.Address serial object class.

• Multiple persistent tables can reference instances of the same embedded object. For example, the MyData.Person table Home property and the MyData.Employee WorkPlace property can both reference the MyData.Address serial object class.

• An embedded object can reference another embedded object. For example, the MyData.Address embedded object contains the Phone property that references the MyData.Telephone embedded object, containing CountryCode, AreaCode, and PhoneNum properties. From the persistent class you use multiple underscores to refer to a nested serial object property, for example Home_Phone_AreaCode.

For further details, refer to Introduction to Serial Objects in Defining and Using Classes.

For information on creating an index for a serial object property, refer to Indexing an Embedded Object (%SerialObject) Property.

Class Methods

You can specify class methods as part of a table definition, as shown in the following example:

Class MyApp.Person Extends %Persistent 
{
Property Name As %String(MAXLEN=50) [Required];
Property SSN As %String(MAXLEN=15) [InitialExpression = "Unknown"];
Property DateOfBirth As %Date;
Property Sex As %String(MAXLEN=1);
ClassMethod Numbers() As %Integer [ SqlName = Numbers, SqlProc ]
  {
   QUIT 123
  }
}

In a SELECT query you can invoke this method as follows:

SELECT Name,SSN,MyApp.Numbers() FROM MyApp.Person

Using DDL in Embedded SQL

Within an ObjectScript method or routine, you can use embedded SQL to invoke DDL commands.

For example, the following method creates a TEST.EMPLOYEE table:

ClassMethod CreateTable() As %Integer
{
 &sql(CREATE TABLE TEST.EMPLOYEE (
    EMPNUM              INT NOT NULL,
    NAMELAST            CHAR (30) NOT NULL,
    NAMEFIRST           CHAR (30) NOT NULL,
    STARTDATE           TIMESTAMP,
    SALARY              MONEY,
    ACCRUEDVACATION     INT,
    ACCRUEDSICKLEAVE    INT,
    CONSTRAINT EMPLOYEEPK PRIMARY KEY (EMPNUM)))
        
 Write "SQL: ",SQLCODE,!
 QUIT SQLCODE
}

When this method is invoked it attempts to create a TEST.EMPLOYEE table (as well as the corresponding TEST.EMPLOYEE class). If successful, the SQLCODE variable is set to 0. If unsuccessful, SQLCODE contains an SQL Error Code indicating the reason for the failure.

The most common reasons that a DDL command such as this one will fail are:

• SQLCODE -99 (Privilege Violation): This error indicates that you do not have permission to execute the desired DDL command. Typically this is because an application has not established who the current user is. You can do this programmatically using the $SYSTEM.Security.Login()Opens in a new tab method:

   DO $SYSTEM.Security.Login(username,password)

  

• SQLCODE -201 (Table or view name not unique): This error indicates that you are attempting to create a new table using the name of a table that already exists.

Using a Class Method to Execute DDL

Within ObjectScript or Caché Basic, you can use the Dynamic SQL %SQL.StatementOpens in a new tab object to prepare and execute DDL commands using Dynamic SQL.

The following example defines a class method to create a table using Dynamic SQL:

  Class Sample.NewT
  {
  ClassMethod DefTable(user As %String,pwd As %String) As %Status [Language=cache]
    {
    DO ##class(%SYSTEM.Security).Login(user,pwd)
    SET myddl=2
    SET myddl(1)="CREATE TABLE Sample.MyTest "
    SET myddl(2)="(NAME VARCHAR(30) NOT NULL,SSN VARCHAR(15) NOT NULL)"
    SET tStatement=##class(%SQL.Statement).%New()
    SET tStatus=tStatement.%Prepare(.myddl)
      IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET rset=tStatement.%Execute()
    IF rset.%SQLCODE=0 {WRITE "Created a table"}
    ELSEIF rset.%SQLCODE=-201 {WRITE "table already exists"}
    ELSE {WRITE "Unexpected error SQLCODE=",rset.%SQLCODE}
    }
  }

This method is invoked as follows:

  DO ##class(Sample.NewT).DefTable("myname","mycachepassword")

As with the embedded SQL example, this method will fail if there is no current user logged in.

Running DDL Scripts from the Command Line

You can import Caché SQL DDL script files using either the Cache()Opens in a new tab method interactively from a Terminal session, or the DDLImport("CACHE")Opens in a new tab method as a background job. For further details, refer to the Importing SQL Code chapter of this guide.

If you are migrating tables from a relational database to Caché, you may have one or more DDL scripts within text files. Caché provides methods to help load such tables into Caché. Refer to the %SYSTEM.SQLOpens in a new tab class for details (particularly the Oracle()Opens in a new tab, Sybase()Opens in a new tab, and MSSQLServer()Opens in a new tab methods).

For example, to load an Oracle DDL file from the Caché command line:

1. Start a Terminal session using the Terminal command in the “Caché Cube” menu.

2. Switch to the namespace in which you wish to load the table definitions:

    ZN "MYNAMESPACE"

   

3. Invoke the desired DDL import method:

    DO $SYSTEM.SQL.Oracle()

   

   and follow the directions displayed at the terminal.

The GetColumns() Method

To list the names of the columns in a table in column number order, you can use the GetColumns()Opens in a new tab method, as follows:

  SET stat=##class(%SYSTEM.SQL).GetColumns("Sample.Person",.byname,.bynum)
  IF stat=1 {
    SET i=1
    WHILE $DATA(bynum(i)) { WRITE "name is ",bynum(i),"   col num is ",i,!
                            SET i=i+1 }
  }
  ELSE { WRITE "GetColumns() cannot locate specified table" }

GetColumns() lists all defined columns, including hidden columns. If a table references an embedded %SerialObject class, GetColumns() first lists all of the columns in the persistent class, including the property that references the %SerialObject, then lists all of the %SerialObject properties. This is shown in the following GetColumns() results:

name is ID   col num is 1
name is Age   col num is 2
name is Home   col num is 3
name is Name   col num is 4
name is x__classname   col num is 5
name is Home_City   col num is 6
name is Home_Phone   col num is 7
name is Home_Phone_AreaCode   col num is 8
name is Home_Phone_Country   col num is 9
name is Home_Phone_TNum   col num is 10
name is Home_PostalCode   col num is 11
name is Home_State   col num is 12
name is Home_Street   col num is 13

You can also use this method to determine the column number for a specified column name, as follows:

  SET stat=##class(%SYSTEM.SQL).GetColumns("Sample.Person",.byname)
  IF stat=1 {
         WRITE "Home_State is column number ",byname("Home_State"),!  }
  ELSE { WRITE "GetColumns() cannot locate specified table" }