SQL iQuery
Preprocessor Directives
In designing the SQL iQuery Scripting language, we focused on compatibility
with existing language preprocessors. For example the C/C++ preprocessor was
very influential to the iQuery preprocessor design.
Most iQuery directives or control statements begin with the pound sign (#) or
"hash tag" but contemporary standards. Some however, do not and we'll identify
and discuss those later.
Directives and controls statement appear on one line, however they may be
continued onto the next line by including the back slash as the last character
on the line.
All embedded SQL statements and iQuery Script Control statements do not being
with a pound sign, and may span as many lines as necessary. They must be
terminated with a semi-colon. There is one caveat, the final/last SQL statement
in the source member does not require a terminating semi-colon as the end of
file condition implies the end of line.
Control Statements
- #ifdef - continues if the substitution variable is defined
- #ifndef - continues if the substitution variable is not defined or is
empty
- #ifExists - continues if the IBM i object exists
- #ifNotExists - continues if the IBM i object does not exist
- #if - continues if the condition is true
- #else - continues if the corresponding #ifxxxx is not true.
- #elseif - continues if the corresponding #ifxxx is not true and its
condition is true
- #endif - closes a #ifxxxx...#elsexx condition.
- #exit - Terminates the iQuery script and returns after clearing and SQL
statement(s).
- #return - Terminates the iQuery script and returns but leaves any SQL
statement loaded up to that point in the buffer for processing by the
RUNiQRY command.
iQuery Session Variable Directives
- #define - declare an iQuery session variable and assign it the value
- synonyms: #declare #dcl #defn
- #eval - similar to #define but evaluates the rValue (right-side of the
equals sign) for any mathematical expressions
- #undef - deletes a previously declared iQuery Session variable
- synonyms: #undcl #undefine
- #default - if the specified iQuery Session variable is not defined or has no value, it
declares the variable and assigns the value, otherwise no-op.
Process Directives
- #dumpvars - Writes the current value of all variables to the joblog.
- #sleep - Stops processing (goes to sleep) for the given period of time (in seconds)
Message Directives
- #msg - Write an *INFO message to the joblog
- #status - Write a *STATUS message TOMSGQ(*EXT)
- #statusmsg - Write an *INFO message to the joblog, and send the same
text as a *STATUS message to *EXT
- #diag - Write a *DIAG message to the joblog.
- #error - Write an *ESCAPE message and cancel the script
- #prtenv - Write the value of the specified Environment Variable to the
joblog
CL Program Directives
- #cmd - Run a CL command.
- synonyms: #cl #clp #runcmd #qcmdexc
- #call - Calls the specified program
- #rtvdtaara - Retrieves the contents of a data area and assigns it to the
iQuery session variable.
- syntax: #rtvdtaara &myvar = *LDA
Environment Directives
- #env - Write the value of the specified Environment Variable to the
joblog
- synonyms: #prtenv, #logenv
- #getenv - Store the value of the Environment Variable in the iQuery
session variable.
- syntax: #getenv &title = CORPORATE_IDENTITY
- #putenv - Change the value of the Environment Variable
- syntax: #putenv CORPORATE_IDENTITY = anything you want goes here
SQL Directives
- IF [ NOT ] EXISTS SELECT ...
- (note: no # prefix is specified) Conditions the following statements based on the
results of the IF EXISTS statement.
- #path - Run the SQL SET PATH statement
Include Directive
- #include - Include another source member or IFS text file
- synonyms: #copy #copybook
- syntax - Any of the following syntax styles are supported.
- #include mbr
- #include file(mbr)
- #include file,mbr
- #include lib/file(mbr)
- #include lib/file,mbr
- #include lib/file/mbr
- #include /qsys.lib/mylib.lib/qsqlsrc.file/mymbr.mbr
- #include /home/myfolder/sales.sql
<<Overview -
Directive Examples>>