The name of the method to be created. This name may be unqualified
(StoreName) and take the system-wide
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 name. If a method with
this name already exists, the operation fails with an SQLCODE -361
error. To avoid this error, use the optional keyword OR REPLACE to modify or replace the existing method. CREATE OR REPLACE
METHOD has the same effect as invoking DROP METHOD to delete the old version of the method and then invoking CREATE METHOD.
The name of the generated class is the package name corresponding
to the schema name, followed by a dot, followed by “meth”,
followed by the specified name. For example, if
the unqualified method name RandomLetter takes
the initial default schema SQLUser, the resulting class name would
be: User.methRandomLetter. 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 duplicate method
name that differs only in letter case. Specifying a method name that
differs only in letter case from an existing method name results in
an SQLCODE -400 error.
A list of parameters used to pass values to the method. The
parameter list is enclosed in parentheses, and parameter declarations
in the list are separated by commas. The parentheses are mandatory,
even when specifying 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
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 output value from a method is automatically converted from Logical format to Display/ODBC format.
An input value to a method is, by default, not converted from Display/ODBC format to Logical format. However, input display-to-logical conversion can be configured systemwide using the $SYSTEM.SQL.Util.SetOption("SQLFunctionArgConversion")Opens in a new tab method. You can use $SYSTEM.SQL.Util.GetOption("SQLFunctionArgConversion")Opens in a new tab to determine the current configuration of this option.
The available keywords are as follows:
||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
||Specifies that the method can only be invoked by other methods
of its own class or subclasses. By default, a method is public, and
can be invoked without restriction. This restriction is inherited
||Specifies that the method is an SQL stored procedure. Stored
procedures are inherited by subclasses. (This keyword can be abbreviated
|Specifies that the method created will contain
the ReturnResultsets keyword. All forms of this characteristics phrase
||Specifies the data type of
the value returned by a call to the method. If RETURNS is omitted,
the method cannot return a value. This specification is inherited
by subclasses, and can be modified by subclasses. This datatype can specify type parameters such as MINVAL, MAXVAL, and SCALE. For
example RETURNS DECIMAL(19,4). Note that when returning
a value, InterSystems IRIS ignores the length of datatype; for example, RETURNS VARCHAR(32) can receive
a string of any length that is returned by a call to the method.
||Only used when LANGUAGE is SQL (the default).
When specified, InterSystems IRIS adds an #SQLCOMPILE SELECT=mode statement to the corresponding class method,
thus generating the SQL statements defined in the method with the
specified SELECTMODE. The possible mode values
are LOGICAL, ODBC, RUNTIME, and DISPLAY. The default is LOGICAL.
If you specify a query keyword (such as CONTAINSID or RESULTS)
that is not valid for a method, the system generates an SQLCODE -47
error. If you specify a duplicate query keyword (such as FINAL FINAL),
the system generates an SQLCODE -44 error.
The SELECTMODE clause is used for SELECT query
operations and for INSERT and UPDATE operations. It specifies the compile-time select mode. 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.
In a SELECT query, the SELECTMODE
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 display mode can be set (to LOGICAL, ODBC,
or DISPLAY) at execution time.
In an INSERT or UPDATE operation, the SELECTMODE RUNTIME
option supports automatic conversion of input data values from a display
format (DISPLAY or ODBC) to logical storage format. This compiled
display-to-logical data conversion code is applied only if the select
mode setting when the SQL code is executed is LOGICAL (which is the
default for all InterSystems SQL execution interfaces).
When the SQL code is executed, the %SQL.StatementOpens in a new tab class %SelectModeOpens in a new tab property specifies the execution-time
select mode, as described in “Using
Dynamic SQL” chapter of Using InterSystems
SQL. For further details on SelectMode options, refer
to “Data Display Options” in the “InterSystems IRIS SQL Basics” chapter
of Using InterSystems SQL.
The program code for the method 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
InterSystems IRIS uses the code you supply to generate the actual
code of the method.
If the code you specify is SQL, InterSystems IRIS provides additional
lines of code when generating the method that embed the SQL in an
ObjectScript “wrapper,” provide a procedure context handler
(if necessary), and handle return values. The following is an example
of this InterSystems IRIS-generated wrapper code:
&sql( SELECT col FROM tbl )
If the code you specify is OBJECTSCRIPT, the ObjectScript code
must be enclosed in curly braces. All code lines must be indented
from column 1, except for labels and macro preprocessor directives.
A label or macro directive must be prefaced by a colon (:) in column
For ObjectScript code, you must explicitly define the “wrapper”
(which NEWs variable and uses QUIT exit and (optionally) to return
a value upon completion).
The method can be exposed as a stored procedure by specifying
the PROCEDURE keyword. When a stored procedure is called, an object
of the class %Library.SQLProcContextOpens in a new tab is instantiated
in the %sqlcontext variable. This procedure context handler 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.