Importing Caché SQL

You can import Caché SQL code from a text file using either of the following methods:

DDLImport()Opens in a new tab is a general-purpose SQL import method. This method runs as a background (non-interactive) process. To import Caché SQL you specify “CACHE” as the first parameter.
Cache()Opens in a new tab is a Caché SQL import method. This method runs interactively from the Terminal. It prompts you to specify the location of the import text file, the location to create the Errors.log file and the Unsupported.log file, and other information.

The following example imports the Caché SQL code file mysqlcode.txt, executing its SQL statements in the current namespace:

  DO $SYSTEM.SQL.DDLImport("CACHE",$USERNAME,"c:\temp\mysqlcode.txt",,1)

By default, DDLImport() creates an errors log file. This example creates a file named mysqlcode_Errors.log in the same directory as the SQL code file. The fifth parameter is a boolean specifying whether or not to create a file that lists unsupported statements. The default is 0. In this example, the fifth parameter is set to 1, creating a file named mysqlcode_Unsupported.log in the same directory as the SQL code file. These log files are created even when there is nothing written to them.

When executing DDLImport() from the Terminal, it first lists the input file, the error log file, and the unsupported log file. Then for each SQL command it displays a listing such as the following:

SQL statement to process (number 1):
     CREATE TABLE Sample.MyStudents (StudentName VARCHAR(32),
     StudentDOB DATE)
      Preparing SQL statement...
      Executing SQL statement...
  DONE

If an error occurs in any SQL command, the Terminal display the error, as shown in the following example:

SQL statement to process (number 3):
       INSERT INTO Sample.MyStudents (StudentName,StudentDOB) SELECT Name,
       DOB FROM Sample.Person WHERE Age <= '21'
    Preparing SQL statement...
ERROR #5540: SQLCODE: -30 Message:  Table 'SAMPLE.PERSON' not found
       Pausing 5 seconds - read error message!  (Type Q to Quit)

If you do not Quit within 5 seconds, DDLImport() proceeds to execute the next SQL command. The error is recorded in the errors log file with a timestamp, the user name, and the namespace name.

Import File Format

An SQL text file must be an unformatted file such as a .txt file. Each SQL statement must begin on its own line. An SQL statement may be broken into multiple lines and indentation is permitted. By default, each SQL statement must be followed by a GO statement on its own line.

The following is an example of a valid Caché SQL import file text:

  CREATE TABLE Sample.MyStudents (StudentName VARCHAR(32),StudentDOB DATE)
GO
  CREATE INDEX NameIdx ON TABLE Sample.MyStudents (StudentName)
GO
  INSERT INTO Sample.MyStudents (StudentName,StudentDOB) SELECT Name,
  DOB FROM Sample.Person WHERE Age <= '21'
GO
  INSERT INTO Sample.MyStudents (StudentName,StudentDOB) 
          VALUES ('Jones,Mary',60123)
GO
  UPDATE Sample.MyStudents SET StudentName='Smith-Jones,Mary' WHERE StudentName='Jones,Mary'
GO
  DELETE FROM Sample.MyStudents WHERE StudentName %STARTSWITH 'A'
GO

By setting the DDLImport("CACHE") deos seventh parameter, this method can accept (but does not require) a specified end-of-statement delimiter, commonly a semicolon (;), at the end of each SQL statement. The default is to not support an end-of-statement delimiter. The “GO” statement on the line following an SQL statement is always supported, but is not required if deos specifies an end-of-statement delimiter.

Supported SQL Statements

Not all valid Caché SQL code statements can be imported. The following is a list of supported Caché SQL commands:

CREATE TABLE, ALTER TABLE, DROP TABLE
CREATE VIEW, ALTER VIEW, DROP VIEW
CREATE INDEX all index types, except bitslice
CREATE USER, DROP USER
CREATE ROLE
GRANT, REVOKE
INSERT, UPDATE, INSERT OR UPDATE, DELETE
SET OPTION
SELECT for optimizer plan mode only