SQL Preprocessing: #define and #include

Go Up to Valid Content in the SQL Editor

SQL preprocessing provides similar functionality to that provided by C language compiler directives. The ISQL Editor supports a simplified version of the following directives.

• #include provides a means to include the contents of a file in a script at the location of the directive
• #define provides a simple, global search and replace function within a script

The following figure illustrates the result of basic preprocessing of a script, nesting of #define directives/references, and the notations required by the ISQL editor. The original script includes two #define directives and a #include reference to a one-line file named fileForInclude.sql. The referenced file includes two identifiers to be replaced with #define processing.

The key steps in working with SQL preprocessing are:

• Preparing the ISQL editor for #include and #define preprocessing - Prior to using SQL preprocessing features, you should set the paths that will be searched in processing #include directives. For details, see Setting up Rapid SQL to Preprocess #include directives.
• Using #define and #include directives in scripts - While the supported directives approximate typical C language #define and #include functionality, there are differences in functionality and required syntax. For example, identifiers in the #define directive and in all instances to be replaced, must be prefixed with two ampersand characters (#define &&PI 3.14159). For detailed information, see #include Functionality and Syntax and #define Functionality and Syntax.
• Preprocessing and executing scripts containing #define and #include directives - The ISQL Editor offers two preprocessing options. You can have a script preprocessed without being executed, opening the processed script in a new editor window with all #define and #include substitutions made. This lets you view the processed script before execution or continue working with the processed SQL. Alternatively, you can have the script preprocessed and executed in a single step. For details, see Preprocessing and Executing Scripts Containing #define and #include Directives.

Setting up Rapid SQL to Preprocess #include directives

In ISQL editor processing of a #include directive, the following locations are searched, in the following order, for the specified file:

1. The location specified on the Datasource Properties tab of the Datasource Registration Wizard/Editor. For details, see Registering Datasources.
2. The location specified on the Directories tab of the Options editor. For details, see Directories Options.

   Note: For detailed information on setting options, see Specifying Application Preferences and Feature Options.

Before using SQL preprocessing, ensure that server side or server side and local search paths for include files are specified.

#include Functionality and Syntax

Support for the #include directive provides a means to include the contents of a file in a script at the location of the directive. For example, if a script contains the following:

#include mydeclarations.sql

then on preprocessing of the script, there are two effects:

• The line containing the #include directive is commented out before the script is sent to the database
• The text in the file mydeclarations.sql is placed in the script following the commented out line with the #include directive.

The #include directive is supported for simple file names only. Supported syntax of the #include directive for use in the ISQL editor, Procedure Object Editor, or Package Body Object Editor, is as follows:

#include <filename.ext>

where:

• filename.ext is a simple filename and extension

Note: For those familiar with C compiler functionality, angle bracket and quoted forms are supported only indirectly. While #include <filename.ext> and #include "filename.ext" forms are valid, they are functionally equivalent to the #include filename.ext. Using the angle bracket or quoted forms has no effect on locations searched for the target file.

Searches are performed in the locations specified in the setup for this feature. For details, see Setting up Rapid SQL to Preprocess #include directives.

Error processing is as follows:

• If the preprocessor fails to include the specified file, it displays an error message noting the reason for the failure (such as the file does not exist, insufficient permissions on the file, or file too large). Preprocessing or execution of the script cannot continue until the error is corrected.
• If the file is found in the first search location specified in Setting up Rapid SQL to Preprocess #include directives but cannot be opened (permission denied for example), no attempt will be made to locate the file in the second specified search location.

#define Functionality and Syntax

The #define directive provides a simple, global search and replace function within a script. For example, if a script contains the following:

#define &&PI 3.14159

then on execution of the script, there are two effects:

• All instances of &&PI in the script would be replaced by 3.14159 on execution of the script
• The line containing the #define directive is commented out before the script is sent to the database

The #define directive is supported for symbolic constants only. Supported syntax of the #define directive for use in the ISQL editor, Procedure Object Editor, or Package Body Object Editor, is as follows:

#define &&Identifier Replacement_text

where:

• Identifier is any character string appearing in the script
• Replacement_text is the string that will replace all instances of the string specified by the Identifier argument. Valid values are strings, numbers or combinations consisting of the digits 0-9, characters a-z, characters A-Z, and the underscore character.

Note: In addition to the actual #define directive appearing in a script, the ampersand notation is also required in all references that are to be replaced. References that are nor prefixed with ampersand characters are not processed.

Nested #define directives are also supported. For example if a script contains the following:

#define &&myTable Clients
#define &&companyClients MyCompany
#define &&tempTable New&&myTable
#define &&companyTempTable &&companyClients&&myTable

Select * from &&tempTable;
Select * from &&companyTempTable

then after preprocessing, the contents of the script would be as follows:

Select * from NewClients;

Select * from MyCompanyClients

Preprocessing and Executing Scripts Containing #define and #include Directives

The ISQL editor offer two preprocess/execute modes:

• The script can be simply preprocessed, opening the script in a new tab, with all #define and #include substitutions made. This lets you view your preprocessed script or continue with edits after preprocessing, before executing it in the new tab.
• The script can be preprocessed and executed in a single step.

Preprocessing or preprocessing/executing the script consists of selecting a mode and the executing the script. Preprocessing mode is controlled by the preprocessing dropdown on the ISQL editor toolbar.

To preprocess a script and have the preprocessed script opened in a new tab:

1. From the Preprocess dropdown, select Pre-Process Only. The Preprocess dropdown icon takes on a distinctive appearance to indicate Pre-Process Only mode.

   

2. On the ISQL toolbar, click the Execute button.

   

   The script with all #define and #include replacements made, opens in a new ISQL editor.

   Note: For details on error processing and specific handling of directives, see #include Functionality and Syntax and #define Functionality and Syntax.

To preprocess and execute a script in a single step:

1. From the Preprocess dropdown, select Pre-Process and Execute.
2. On the ISQL toolbar, click the Execute button.

   The script executes. While the script sent to the server for execution includes all #define and #include substitutions, the new Query tab contains the original, unprocessed script.

For related information, see the following topics:

• See Script/File Execution Facilities for other script execution options
• See SQL Preprocessing: #define and #include for an introduction to SQL preprocessing in the ISQL editor