#sqlcompile path

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. It has the form:

#sqlcompile path=schema1[,schema2[,...]]

where schema is a schema name used to look up an unqualified SQL table name, view name, or procedure name in the current namespace. You can specify one schema name or a comma-separated list of schema names. Schemas are searched in the order specified. Searching ends and the DML operation is performed when the first match occurs. If none of the schemas contain a match, the system-wide default schema is searched.

Because schemas are searched in the specified order, there is no detection of ambiguous table names. The #import preprocessor directive also supplies a schema name to an unqualified SQL table, view, or procedure name from a list of schema names; #import does detect ambiguous names.

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

#sqlcompile path 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. #sqlcompile path can also be used to resolve unqualified procedure names in SQL CALL statements.
#sqlcompile path 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 #sqlcompile path values and use the system-wide default schema.

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

The following example resolves the unqualified table name Person to the Sample.Person table. It first searches the Cinema schema (which does not contain a table named Person), then searches the Sample schema:

#sqlcompile path=Cinema,Sample
  &sql(SELECT Name,Age
       INTO :a,:b
       FROM Person)
  WRITE "Name is: ",a,!
  WRITE "Age is: ",b

In addition to specifying schema names as search path items, you can specify the following keywords:

CURRENT_PATH: specifies the current schema search path, as defined in a prior #sqlcompile path preprocessor directive. This is commonly used to add schemas to the beginning or end of an existing schema search path, as shown in the following example:
```
#sqlcompile path=schema_A,schema_B,schema_C
#sqlcompile path=CURRENT_PATH,schema_D
```
CURRENT_SCHEMA: specifies the current schema container class name. If #sqlcompile path is defined in a class method, the CURRENT_SCHEMA is the schema mapped to the current class package. If #sqlcompile path is defined in a .MAC routine, the CURRENT_SCHEMA is the configuration default schema.
For example, if you define a class method in the class User.MyClass that specifies #sqlcompile path=CURRENT_SCHEMA, the CURRENT_SCHEMA will (by default) resolve to SQLUser, since SQLUser is the default schema name for the User package. This is useful when you have a superclass and subclass in different packages, and you define a method in the superclass that has an SQL query with an unqualified table name. Using CURRENT_SCHEMA, you can have the table name resolve to the superclass schema in the superclass and to the subclass schema in the subclass. Without the CURRENT_SCHEMA search path setting, the table name would resolve to the superclass schema in both classes.
If #sqlcompile path=CURRENT_SCHEMA is used in a trigger, the schema container class name is used. For example, if class pkg1.myclass has a trigger than specifies #sqlcompile path=CURRENT_SCHEMA, and class pkg2.myclass extends pkg1.myclass, InterSystems IRIS resolves the non-qualified table names in the SQL statements in the trigger to the schema for package pkg2 when the pkg2.myclass class is compiled.
DEFAULT_SCHEMA specifies the system-wide default schema. This keyword enables you to search the system-wide default schema as a item within the schema search path, before searching other listed schemas. The system-wide default schema is always searched after searching the schema search path if all the schemas specified in the path have been searched without a match.

If you specify a schema search path, the SQL query processor uses the schema search path first when attempting to resolve an unqualified name. If it does not find the specified table or procedure, it then looks in the schema(s) that are provided via #import (if specified), or the configured system-wide default schema. If it does not find the specified table in any of these places, it generates an SQLCODE -30 error.

The scope of the schema search path is the routine or method it is defined in. If a schema path is specified in a class method, it only applies to that class method, and not to other methods in the class. If it is specified in a .MAC routine, it applies from that point forward in the routine until another #sqlcompile path directive is found, or the end of the routine is reached.

Schemas are defined for the current namespace.

Compare with the #import preprocessor directive.

#sqlcompile select

#sqlcompile mode