Using Caché SQL
Defining Tables
[Back] [Next]
   
Server:docs1
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

This chapter describes how you create tables in Caché SQL. It discusses the following topics:

Table Names and Schema Names
When you specify a table name, Caché uses the table name to generate a corresponding class name. Caché uses the schema name to generate a corresponding package name. For further details, refer to Defining and Compiling Classes in the Using Caché Objects manual.
A table name may be unqualified or qualified. An unqualified table name is a simple identifier: MyTable. A qualified table name consists of two simple identifiers, a schema name and a table name, separated by a period: MySchema.MyTable. Table names and schema names are not case-sensitive.
A qualified table name (schema.name) is assigned to the specified schema (package). When you use the CREATE TABLE command to create a table by specifying a qualified table name, Caché determines if the specified schema already exists. If the schema does not exist, Caché creates the specified schema and places the table in that schema.
The following table name length limits apply:
System-wide Default Schema
If you create a table with an unqualified name, Caché assigns it the system-wide default SQL schema name. The following description of schema name resolution applies to table names, view names, and stored procedure names.
To return the current default schema name, invoke the $SYSTEM.SQL.DefaultSchema() method:
  WRITE $SYSTEM.SQL.DefaultSchema()
 
The initial system-wide default SQL schema name is SQLUser. You can change this default using either of the following:
If you set the default SQL schema name to _CURRENT_USER, the default schema is the username the process used to log into SQL; if the process did not log into SQL, SQLUser is the default schema. If you set the default SQL schema name to _CURRENT_USER/Name, the default schema is the username the process used to log into SQL; if the process did not log into SQL, Name is the default schema.
Caution:
When you change the default SQL schema name, Caché automatically purges all cached queries in all namespaces on the system. By changing the default schema name, you change the meaning of all queries that contain unqualified table, view, or stored procedure names. It is strongly recommended that the default SQL schema name be established at Caché installation and not subsequently modified.
Schema Naming Considerations
Schema names follow identifier conventions, with significant considerations concerning the use of non-alphanumeric characters. 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 use the CREATE TABLE command to create a 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:
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:
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.
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.
RowID Field
The RowID field is a system-generated field that uniquely identifies each record with a sequential integer. When you define a table, Caché SQL automatically defines this field. When a table is populated with data, Caché assigns sequential integers to this field, starting with 1. RowID data values are defined as required, unique, non-null, and non-modifible.
By default, Caché names this field “ID”. However this field name is not reserved. If the user defines a field named “ID”, Cache names the RowId as “ID1”. If, for example, the user then uses ALTER TABLE to define a field named “ID1”, Caché renames the RowID as “ID2”, and so forth. For this reason, Caché provides the %ID pseudo-column name (alias) which always returns the RowID (object ID) value, regardless of the name assigned to the RowID.
By default, Caché defines this field as column number 1.
ALTER TABLE cannot modify or delete the RowID field definition.
Row ID values always increment. They are not reused. Therefore, if the table data has been modified by delete and insert operations, the row ID values will be in ascending numeric sequence (the order of insert), but will not be numerically contiguous. To determine if a specific ID value exists, invoke the table’s %ExistsId() method.
By default, the RowID is hidden (not displayed by SELECT *) and PRIVATE. When you create a table you can specify the %PUBLICROWID keyword to make the RowId not hidden and public. Because this keyword specifies that the table’s RowID is PUBLIC, the RowID can therefore be used as a foreign key reference. If you specify the %PUBLICROWID keyword, the class corresponding to the table is defined with “Not SqlRowIdPrivate”. This optional keyword can be specified anywhere in the CREATE TABLE comma-separated list of table elements. It cannot be specified in ALTER TABLE.
Note:
Most of the example tables supplied in the Samples namespace are defined with %PUBLICROWID.
To list the field names (hidden and non-hidden) in a table , refer to Column Names and Numbers.
By default, RowID values are not user-modifiable. Modifying RowId values can have serious consequences and should only be done in very specific cases and with extreme caution. The Config.SQL.AllowRowIDUpdate property allows RowID values to be user-modifiable.
For further details, refer to The RowID Field and %PUBLICROWID in the CREATE TABLE reference page.
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 integer to this field each time the row data is modified (insert, update, or %Save). Because this counter is namespace-wide, changes to the data in multiple tables that have a RowVersion field increment the same counter.
You create a RowVersion field by specifying a field of data type ROWVERSION. You can only specify one ROWVERSION data type field per table. This field can have any name and can appear in any column position. A namespace can contain tables with the 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 the 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. Deleting all table data or deleting all RowVersion tables does not reset the RowVersion 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).
Defining a Table by Creating a Persistent Class
The primary way to define tables within Caché is to use Caché Studio to create persistent class definitions. When these classes are saved and compiled within the Caché database, they automatically create a relational table that corresponds to the class definition: each class represents a table; each property represents a column, and so on. The maximum number of properties (columns) definable for a class (table) is 1000.
For example, the following defines the persistent class MyApp.Person within the MyApp package:
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);
}
When compiled, this creates a relational table, Person within the MyApp schema. This table could have been defined using DDL (which would still result in a persistent class and the corresponding SQL table being created):
CREATE TABLE MyApp.Person (
    Name VARCHAR(50) NOT NULL,
    SSN VARCHAR(15) DEFAULT 'Unknown',
    DateOfBirth DATE,
    Sex VARCHAR(1)
)
CREATE TABLE does not specify an explicit StorageStrategy in the corresponding class definition. It instead takes the defined default storage strategy.
By default, CREATE TABLE specifies the Final class keyword in the corresponding class definition, indicating that it cannot have subclasses.
For an introduction to how the object view of the database corresponds to the relational view, see Introduction to the Default SQL Projection in the chapter “Introduction to Persistent Objects” of Using Caché Objects.
Note that a persistent class definition such as the one shown above creates the corresponding table when it is compiled, but this table definition cannot be modified or deleted using SQL DDL commands (or by using the Management Portal Drop action), which give you the message “DDL not enabled for class 'schema.name'...”). You must specify [DdlAllowed] in the table class definition to permit these operations:
Class MyApp.Person Extends %Persistent [DdlAllowed]
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.
Defining a Table by Using DDL
You can define tables in Caché SQL using standard DDL commands:
Available DDL Commands in Caché SQL
ALTER Commands CREATE Commands DROP Commands
These are described in the Caché SQL Reference.
You can execute DDL commands in a variety of ways, including:
Using DDL in Embedded SQL
Within a Caché 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:
Using a Class Method to Execute DDL
Within Caché ObjectScript or Caché Basic, you can use the Dynamic SQL %SQL.Statement 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() method interactively from a Terminal session, or the DDLImport("CACHE") 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.SQL class for details (particularly the Oracle(), Sybase(), and MSSQLServer() methods).
For example, to load an Oracle DDL file from the Caché command line:
  1. Start a Caché 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.
External Tables
In Caché SQL, you can also have “external tables,” tables that are defined within the Caché dictionary but are stored within an external relational database. External tables act as if they were native Caché tables: you can issue queries against them and perform INSERT, UPDATE, and DELETE operations. The access to external database is provided by the Caché SQL Gateway, which offers transparent connectivity using ODBC or JDBC. See Using the Caché SQL Gateway for more details.
Column Names and Numbers
You can list all of the column names (field names) for a specified table in three ways:
The GetColumns() Method
To list the names of the columns in a table in column number order, you can use the GetColumns() 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.
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" }