Skip to main content
Previous sectionNext section

Importing SQL Code

This chapter describes how to import SQL code from a text file into InterSystems SQL. When you import SQL code, InterSystems IRIS® data platform prepares and executes each line of SQL using Dynamic SQL. 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) commands, such as CREATE TABLE, and to populate tables using INSERT, UPDATE, and DELETE commands. SQL import does prepare and execute SELECT queries, but does not create a result set.

SQL import can be used to import InterSystems 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 InterSystems SQL code and executed. SQL import cannot import all SQL commands into InterSystems SQL. It imports those commands and clauses that are compatible with the InterSystems IRIS implementation of the SQL standard. Incompatible features are commonly parsed, but ignored.

SQL import can successfully prepare an SQL query — creating a corresponding cached query, where appropriate — but it does not execute the query.

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 commands, and an Unsupported.log file, which contains the literal text of lines that the method does not recognize as an SQL command.

This chapter describes importing different types of SQL code:

Importing InterSystems SQL

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

  • DDLImport() is a general-purpose SQL import method. This method runs as a background (non-interactive) process. To import InterSystems SQL you specify “IRIS” as the first parameter.

  • IRIS() is an InterSystems 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.

Note:

This import and execution of SQL DDL code should not be confused with the Import Statements Action from the Management Portal SQL interface. That operation imports SQL Statements in XML format.

The following example imports the InterSystems IRIS SQL code file pathname mysqlcode.txt, executing the SQL commands listed in that file in the current namespace:

  DO $SYSTEM.SQL.DDLImport("IRIS",$USERNAME,"c:\temp\mysqlcode.txt",,1)
Copy code to clipboard

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 SQL commands. 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
Copy code to clipboard

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)
Copy code to clipboard

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 command must begin on its own line. An SQL command may be broken into multiple lines and indentation is permitted. By default, each SQL command must be followed by a GO statement on its own line.

The following is an example of a valid InterSystems 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("IRIS") 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 command. The default is to not support an end-of-statement delimiter. The “GO” statement on the line following an SQL command is always supported, but is not required if deos specifies an end-of-statement delimiter.

Supported SQL Commands

Not all valid InterSystems SQL commands can be imported. The following is a list of supported InterSystems 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

Code Migration: Importing non-InterSystems SQL

You can import SQL code that is in the SQL format used by other vendors. Code from other vendors is converted to InterSystems SQL code and executed. The following methods are provided:

  • DDLImport() is a general-purpose SQL import method. This method runs as a background (non-interactive) process. Refer to Importing InterSystems SQL for general information on using this method.

    To import SQL in a specific format you specify the name of that format as the first parameter: FDBMS, Informix, InterBase, MSSQLServer (or MSSQL), MySQL, Oracle, or Sybase.

    The following example imports the MSSQL code file mssqlcode.txt, executing the SQL commands listed in that file in the current namespace:

      DO $SYSTEM.SQL.DDLImport("MSSQL",$USERNAME,$lb("C:\temp\somesql.sql","UTF8"),,1)
    Copy code to clipboard

    Note that if the first parameter is MSSQL, Sybase, Informix, or MySQL, the third parameter can be either an SQL code file pathname or a two-element %List with the first element the SQL code file pathname and the second element is the I/O translation table to use.

  • Individual interactive methods are provided to import the following types of SQL: FDBMS(), Informix(), InterBase(), MSSQLServer(), Oracle(), 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.

  • DDLImportDir() 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.

  • ImportDir() 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.