The name of the query to be created in a stored procedure class. The queryname must be a valid identifier and must be followed by parentheses, even if no parameters are specified. The procedure name may be unqualified (StoreName) and take the default schema name, or qualified by specifying the schema name (Patient.StoreName). You can use the $SYSTEM.SQL.Schema.Default()Opens in a new tab method to determine the current system-wide default schema name. The initial system-wide default schema name is SQLUser which corresponds to the class package name User.
Note that the FOR characteristic (described below) overrides the class name specified in queryname. If a method with this name already exists, the operation fails with an SQLCODE -361 error.
The name of the generated class is the package name corresponding to the schema name, followed by a dot, followed by “query”, followed by the specified queryname. For example, if the unqualified query name RandomLetter takes the initial default schema SQLUser, the resulting class name would be: User.queryRandomLetter. For further details, see SQL to Class Name Transformations in the “Defining and Using Stored Procedures” chapter of Using InterSystems SQL.
InterSystems SQL does not allow you to specify a queryname that differs only in letter case. Specifying a queryname that differs only in letter case from an existing query name results in an SQLCODE -400 error.
If the specified queryname already exists in the current namespace, the system generates an SQLCODE -361 error.
Include the optional keyword OR REPLACE to modify or replace an existing query without generating an error. CREATE OR REPLACE QUERY has the same effect as invoking DROP QUERY to delete the old version of the query and then invoking CREATE QUERY.
A list of parameter declarations for parameters used to pass values to the query. The parameter list is enclosed in parentheses, and parameter declarations in the list are separated by commas. The parentheses are mandatory, even if you specify no parameters.
Each parameter declaration in the list consists of (in order):
An optional keyword specifying whether the parameter mode is IN (input value), OUT (output value), or INOUT (modify value). If omitted, the default parameter mode is IN.
The parameter name. Parameter names are case-sensitive.
The data type of the parameter.
Optional: A default value for the parameter. You can specify the DEFAULT keyword followed by a default value; the DEFAULT keyword is optional. If no default is specified, the assumed default is NULL.
The following example creates a query exposed as a stored procedure with two input parameters, both of which have default values. The topnum input parameter specifies the optional DEFAULT keyword; the minage input parameter omits this keyword:
CREATE QUERY AgeQuery(IN topnum INT DEFAULT 10,IN minage INT 20)
SELECT TOP :topnum Name,Age FROM Sample.Person
WHERE Age > :minage ;
The following are all valid CALL statements for this query: CALL AgeQuery(6,65); CALL AgeQuery(6); CALL AgeQuery(,65); CALL AgeQuery().
An optional argument denoting one or more keywords that specify the characteristics of a query. Characteristics can be specified in any order. The available characteristics keywords are as follows:
||Specifies which field, if any, returns the ID. Set CONTAINID to the number of the column that returns the ID, or 0 if no column returns the ID. InterSystems IRIS does not validate that the named field actually contains the ID, so a user error here results in inconsistent data.
||Specifies the name of the class in which to create the method. If the class does not exist, it will be created. You can also specify a class name by qualifying the method name. The class name specified in the FOR clause overrides a class name specified by qualifying the method name.
||Specifies that subclasses cannot override the method. By default, methods are not final. The FINAL keyword is inherited by subclasses.
||Specifies that the query is an SQL stored procedure. Stored procedures are inherited by subclasses. (This keyword can be abbreviated as PROC.)
Specifies the data fields in the order that they are returned by the query. If you specify a RESULTS clause, you must list all fields returned by the query as a comma-separated list enclosed in parentheses. Specifying fewer or more fields than are returned by the query results in a SQLCODE -76 cardinality mismatch error.
For each field you specify a column name (which will be used as the column header) and a data type.
If LANGUAGE SQL, you can omit the RESULTS clause. If you omit the RESULTS clause, the ROWSPEC is automatically generated during class compilation.
||Specifies the mode used to compile the query. The possible values are LOGICAL, ODBC, RUNTIME, and DISPLAY. The default is RUNTIME.
If you specify a method keyword (such as PRIVATE or RETURNS) that is not valid for a query, the system generates an SQLCODE -47 error. Specifying duplicate characteristics results in an SQLCODE -44 error.
The SELECTMODE clause specifies the mode in which data is returned. If the mode value is LOGICAL, then logical (internal storage) values are returned. For example, dates are returned in $HOROLOG format. If the mode value is ODBC, logical-to-ODBC conversion is applied, and ODBC format values are returned. If the mode value is DISPLAY, logical-to-display conversion is applied, and display format values are returned. If the mode value is RUNTIME, the mode can be set (to LOGICAL, ODBC, or DISPLAY) at execution time by setting the %SQL.StatementOpens in a new tab class %SelectModeOpens in a new tab property, as described in “Using Dynamic SQL” chapter of Using InterSystems SQL. The RUNTIME mode default is LOGICAL. For further details on SelectMode options, refer to “Data Display Options” in the “InterSystems IRIS SQL Basics” chapter of Using InterSystems SQL. The value that you specify for SELECTMODE is added at the beginning of the ObjectScript class method code as: #SQLCompile SELECT=mode. For further details, see #sqlcompile select in the “ObjectScript Macros and the Macro Preprocessor” chapter of Using ObjectScript.
The RESULTS clause specifies the results of a query. The SQL data type parameters in the RESULTS clause are translated into corresponding InterSystems IRIS data type parameters in the query’s ROWSPEC. For example, the RESULTS clause RESULTS ( Code VARCHAR(15) ) generates a ROWSPEC specification of ROWSPEC = “Code:%Library.String(MAXLEN=15)”.
The program code for the query to be created. You specify this code in either SQL or ObjectScript. The language used must match the LANGUAGE clause. However, code specified in ObjectScript can contain embedded SQL.
If the code you specify is SQL, it must consist of a single SELECT statement. The program code for a query in SQL is prefaced with a BEGIN keyword, followed by the program code (a SELECT statement). At the end of the program code, specify a semicolon (;) then an END keyword.
If the code you specify is OBJECTSCRIPT, it must contain calls to the Execute()Opens in a new tab and Fetch()Opens in a new tab class methods of the %Library.QueryOpens in a new tab class provided by InterSystems IRIS, and may contain Close()Opens in a new tab, FetchRows()Opens in a new tab, and GetInfo()Opens in a new tab method calls. ObjectScript code is enclosed in curly braces. If Execute() or Fetch() are missing, an SQLCODE -46 error is generated upon compilation.
If the ObjectScript code block fetches data into a local variable (for example, Row), you must conclude the code block with the line SET Row="" to indicate an end-of-data condition.
If the query is exposed as a stored procedure (by specifying the PROCEDURE keyword in characteristics), it uses a procedure context handler to pass the procedure context back and forth between the procedure and its caller.
When a stored procedure is called, an object of the class %Library.SQLProcContextOpens in a new tab is instantiated in the %sqlcontext variable. This is used to pass the procedure context back and forth between the procedure and its caller (for example, the ODBC server).
%sqlcontext consists of several properties, including an Error object, the SQLCODE error status, the SQL row count, and an error message. The following example shows the values used to set several of these:
The values of SQLCODE and %ROWCOUNT are automatically set by the execution of an SQL statement. The %sqlcontext object is reset before each execution.
Alternatively, an error context can be established by instantiating a %SYSTEM.Error object and setting it as %sqlcontext.Error.
InterSystems IRIS uses the code you supply to generate the actual code of the query.