This chapter describes how to import SQL code from a text file into Caché SQL. When you import SQL code, Caché prepares and executes each line of SQL using the %Library.ResultSet
dynamic SQL class. If it encounters a line of code it cannot parse, SQL import skips over that line of code and continues to prepare and execute subsequent lines until it reaches the end of the file. All SQL code import operations import to the current namespace.
SQL Import is primarily used to import Data Definition Language (DDL) statements, such as CREATE TABLE, and to populate tables using INSERT, UPDATE, and DELETE statements. SQL import does prepare and execute SELECT statements, but does not create a result set.
SQL import can be used to import Caché SQL code. It can also be used for code migration, to import SQL code from other vendors (FDBMS, Informix, Interbase, MSSQLServer, MySQL, Oracle, Sybase). Code from other vendors is converted to Caché SQL code and executed. SQL import cannot import all SQL statements into Caché SQL. It imports those statements and clauses that are compatible with the Caché implementation of the SQL standard. Incompatible features are commonly parsed, but ignored.
Successfully executed SQL statements create a corresponding cached query
, where appropriate.
You perform SQL code import by invoking the appropriate method from the %SYSTEM.SQL
class. When importing SQL code, these methods can create two other files: an Errors.log file which records errors in parsing SQL statements, and an Unsupported.log file, which contains the literal text of lines that the method does not recognize as an SQL statement.
This chapter describes importing different types of SQL code:
You can import Caché SQL code from a text file using either of the following methods:
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.
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:
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.
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),
INSERT INTO Sample.MyStudents (StudentName,StudentDOB) SELECT Name,
DOB FROM Sample.Person WHERE Age <= '21'
INSERT INTO Sample.MyStudents (StudentName,StudentDOB)
INSERT OR UPDATE INTO Sample.MyStudents (StudentName,StudentDOB) VALUES ('Smith-Jones,Mary',60123)
DELETE FROM Sample.MyStudents WHERE StudentName %STARTSWITH 'A'
SELECT TOP 5 * FROM Sample.MyStudents
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.
Not all valid Caché SQL code statements can be imported. The following is a list of supported Caché SQL commands:
for optimizer plan mode only
You can import SQL code that is in the SQL format used by other vendors. Code from other vendors is converted to Caché SQL code and executed. The following methods are provided:
is a general-purpose SQL import method. This method runs as a background (non-interactive) process. To import SQL in a specific format you specify the name of that format as the first parameter: FDBMS, Informix, Interbase, MSSQLServer, MySQL, Oracle, or Sybase.
Individual interactive methods are provided to import the following types of SQL: FDBMS()
, and Sybase()
. These methods 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.
allow you to import SQL code from multiple files in a directory. This method runs as a background (non-interactive) process. It supports Informix, MSSQLServer, and Sybase. All files to be imported must have a .sql extension suffix.
allow you to import SQL code from multiple files in a directory. Provides more options than DDLImportDir()
. This method runs as a background (non-interactive) process. It supports MSSQLServer, and Sybase. You can specify a list of allowed file extension suffixes.
© 1997-2019 InterSystems Corporation, Cambridge, MA