#import

Specifies the schema search path for any subsequent Embedded SQL DML statements.

Description

This macro preprocessor directive specifies the schema search path for any subsequent Embedded SQL DML statements.

#import specifies one or more schema names to search to supply the schema name for an unqualified table, view, or stored procedure name. You can specify a single schema name, or a comma-separated list of schema names. Schemas are searched in the current namespace. This is shown in the following example, which locates the Employees.Person table:

#import Customers,Employees,Sales
  &sql(SELECT Name,DOB INTO :n,:date FROM Person)
  WRITE "name: ",n," birthdate: ",date,!
  WRITE "SQLCODE=",SQLCODE

All of the schemas specified in the #import directive are searched. The Person table must be found in exactly one of the schemas listed in #import. Because #import requires a match within the schema search path, the system-wide default schema is not used.

Dynamic SQL uses the %SchemaPath property to supply a schema search path to resolve unqualified names.

Both #import and #sqlcompile path specify one or more schema names used to resolve an unqualified table name. Some of the differences between these two directives are as follows:

#import detects ambiguous table names. #import searches all specified schemas, detecting all matches. #sqlcompile path searches the specified list of schemas in left-to-right order until it finds the first match. Therefore, #import can detect ambiguous table names; #sqlcompile path cannot. For example, #import Customers,Employees,Sales must find exactly one occurrence of Person in the Customers, Employees, and Sales schemas; if it finds more than one occurrence of this table name an SQLCODE -43 error occurs: “Table 'PERSON' is ambiguous within schemas”.
#import cannot take the system-wide default. If #import cannot find the Person table in any of its listed schemas, an SQLCODE -30 error occurs. If #sqlcompile path cannot find the Person table in any of its listed schemas, it checks the system-wide default schema.
#import directives are additive. If there are multiple #import directives, the schemas in all of the directives must resolve to exactly one match. Specifying a second #import does not inactivate the list of schema names specified in a prior #import. Specifying an #sqlcompile path directive overwrites the path specified in a prior #sqlcompile path directive; #sqlcompile path does not overwrite schema names specified in prior #import directives.

InterSystems IRIS ignores non-existent schema names in #import directives. InterSystems IRIS ignores duplicate schema names in #import directives.

If the table name is already qualified, the #import directives do not apply. For example:

  #import Voters
  #import Bloggers
  &sql(SELECT Name,DOB INTO :n,:date FROM Sample.Person)
  WRITE "name: ",n," birthdate: ",date,!
  WRITE "SQLCODE=",SQLCODE

In this case, InterSystems IRIS searches the Sample schema for the Person table. It does not search the Voters or Bloggers schemas.

#import is applied to SQL DML statements. It can be used to resolve unqualified table names and view names for SQL SELECT queries, and for INSERT, UPDATE, and DELETE operations. #import can also be used to resolve unqualified procedure names in SQL CALL statements.
#import is not applied to SQL DDL statements. It cannot be used to resolve unqualified table, view, and procedure names in data definition statements such as CREATE TABLE and the other CREATE, ALTER, and DROP statements. If you specify an unqualified name for a table, view, or stored procedure when creating, modifying, or deleting the definition of this item, InterSystems IRIS will ignore #import values and use the system-wide default schema.

Compare with the #sqlcompile path preprocessor directive.

#include

#ifNDef