This is the 8th day of my participation in the September Challenge.
SQL > CREATE METHOD CREATE METHOD
characteristics
The keywords available are as follows:
FOR className– Specifies the name of the class in which the method will be created.
If the class does not exist, it will be created. You can also specify the class name by qualifying the method name. The class name specified in the FOR clause overrides the specified class name by qualifying the method name.
FINAL– Specifies that subclasses cannot override this method.
By default, methods are not final. The FINAL keyword is inherited by subclasses.
PRIVATE– Specifies that the method can only be called by other methods of its own class or subclass.
By default, methods are public and can be invoked without restriction. This restriction is inherited by subclasses.
PROCEDURE– Specifies that the method is aSQLStored procedures.
Stored procedures are inherited by subclasses. (This keyword can be shortened to PROC.)
RESULT SETS,DYNAMIC RESULT SETS [n]– Specifies that the created method will containReturnResultsetsThe keyword.
All forms of this characteristic phrase are synonyms.
RETURNS datatype– Specifies the data type of the value returned by calling this method.
If RETURNS is omitted, the method cannot return a value. This specification is inherited by subclasses and can be modified by subclasses. This data type can specify type parameters such as MINVAL, MAXVAL, and SCALE. For example, RETURNS DECIMAL(19,4). Note that when returning a value, IRIS ignores the length of the data type; For example, RETURNS VARCHAR(32) can receive strings of any length returned by the calling method.
SELECTMODE mode– only whenLANGUAGEforSQL(Default).
When specified, IRIS adds the #SQLCOMPILE SELECT=mode statement to the corresponding class method to generate the SQL statement defined in the method using the specified SELECTMODE. Possible mode values are LOGICAL, ODBC, RUNTIME, and DISPLAY. The default is LOGICAL.
If you specify a query key that is invalid for a method (such as CONTAINSID or RESULTS), the system generates a SQLCODE -47 error. If a duplicate query key (for example, FINAL FINAL) is specified, the system generates a SQLCODE -44 error.
The SELECTMODE clause is used for SELECT query operations as well as INSERT and UPDATE operations. It specifies the compile-time selection mode. The value specified for SELECTMODE is added at the beginning of the ObjectScript class method code, such as :#SQLCompile Select=mode.
- in
SELECTIn the query,SELECTMODESpecifies the mode for returning data.
If the schema value is LOGICAL, the LOGICAL (internal storage) value is returned. For example, the date is returned in $HOROLOG format. If the mode value is ODBC, the logic-to-ODBC conversion is applied and the ODBC format value is returned. If the mode value is DISPLAY, the logical-to-display conversion is applied and the DISPLAY format value is returned. If the mode value is RUNTIME, the display mode (LOGICAL, ODBC, or display) can be set at execution time.
- in
INSERTorUPDATEIn the operation,SELECTMODE RUNTIMEOption to change input data values from display format (displayorODBC) automatically converts to logical storage format.
This compiled display-to-logic data conversion code is applied only when the selection mode for SQL code execution is set to LOGICAL, which is the default setting for all SQL execution interfaces.
When SQL code is executed, the %SQL.Statement class %SelectMode property specifies the mode to be selected at execution time.
LANGUAGE
Specifies the keyword clause of the language used by CODE_BODY. The allowed clauses are Language OBJECTSCRIPT(for OBJECTSCRIPT) or Language SQL. If the LANGUAGE clause is omitted, the default is SQL.
code_body
The program code for the method to be created. You can specify this code in SQL or ObjectScript. The LANGUAGE used must match the LANGUAGE clause. However, the code specified in ObjectScript can contain embedded SQL.
IRIS uses the provided code to generate the actual code for the method.
If the specified code is SQL, IRIS provides additional lines of code when generating methods that embed SQL into ObjectScript “wrappers”, provides procedure context handlers (if necessary), and processes the return values. Here is an example of the wrapping code generated by this IRIS:
NEW SQLCODE,%ROWID,%ROWCOUNT,title
&sql( SELECT col FROM tbl )
QUIT $GET(title)
Copy the codeIf the specified code is OBJECTSCRIPT, the OBJECTSCRIPT code must be enclosed in braces. All lines of code must be indented from column 1, except for labels and macro preprocessor instructions. Labels or macros must begin with a colon (:) in column 1.
For ObjectScript code, you must explicitly define a “wrapper” (the NEWs variable and exit with QUIT, optionally returning a value on completion).
You can expose the method as a stored PROCEDURE by specifying the PROCEDURE keyword. When a stored procedure is called, objects of the %Library.SQLProcContext class are 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 the error object, SQLCODE error status, NUMBER of SQL lines, and error message. The following example shows the values used to set several of these values:
SET %sqlcontext.%SQLCODE=SQLCODE
SET %sqlcontext.%ROWCOUNT=%ROWCOUNT
SET %sqlcontext.%Message=%msg
Copy the codeSQLCODE and %ROWCOUNT values are automatically set by the execution of the SQL statement. The % SQLContext object is reset before each execution.
Alternatively, the Error context can be established by instantiating the % System.error object and setting it to % SQLContext.error.
The sample
The following example uses the Create method with SQL code to generate the UpdateSalary method in the sample. Employee class:
CREATE METHOD UpdateSalary ( IN SSN VARCHAR(11), IN Salary INTEGER )
FOR Sample.Employee
BEGIN
UPDATE Sample.Employee SET Salary = :Salary WHERE SSN = :SSN;
END
Copy the codeAdd keyword [DdlAllowed] to table
The following example creates a RandomLetter() method that stores the process for generating random uppercase letters. This method can then be called as a function in a SELECT statement. A Drop method is provided to Drop the RandomLetter() method.
CREATE METHOD RandomLetter()
RETURNS INTEGER
PROCEDURE
LANGUAGE OBJECTSCRIPT
{
:Top
SET x=$RANDOM(91)
IF x<65 {GOTO Top}
ELSE {QUIT $CHAR(x)}
}
Copy the codeClass User.methRandomLetter Extends %Library.RegisteredObject [ ClassType = "", DdlAllowed, Owner = {yx}, Not ProcedureBlock ]
{
ClassMethod RandomLetter(a) As %Library.Integer(MAXVAL=2147483647,MINVAL=-2147483648) [ SqlName = RandomLetter, SqlProc ]
{
Top
SET x=$RANDOM(91)
IF x<65 {GOTO Top}
ELSE {QUIT $CHAR(x)}
}
}
Copy the codeSELECT Name FROM Sample.Person
WHERE Name %STARTSWITH RandomLetter()
Copy the code
DROP METHOD RandomLetter
Copy the codeThe following embedded SQL example uses the Create method with ObjectScript code to generate the method TraineeTitle in the sqlUser.myStudents class and return a Title value:
ClassMethod CreateMethod(a)
{
&sql(
CREATE METHOD TraineeTitle
(
IN SSN VARCHAR(11),
INOUT Title VARCHAR(50)
)
RETURNS VARCHAR(30)
FOR SQLUser.MyStudents
LANGUAGE OBJECTSCRIPT
{
n SQLCODE,%ROWCOUNT
&sql(
SELECT Title INTO :Title FROM Sample.Employee
WHERE SSN = :SSN
)
if $g(%sqlcontext)'= "" { s %sqlcontext.%SQLCODE=SQLCODE s %sqlcontext.%ROWCOUNT=%ROWCOUNT } q } ) if SQLCODE=0 { w ! ," create method "QUIT} elseif SQLCODE=-361 {w! SQLCODE: ",SQLCODE & SQL (DROP METHOD TraineeTitle FROM sqluser.myStudents) if SQLCODE=0 {w! ," Delete method "QUIT}} else {w! ,"SQL error: ",SQLCODE } }Copy the codeIt uses the % SQLContext object and sets its %SQLCODE and %ROWCOUNT properties with the corresponding SQL variables. Notice that the ObjectScript code is enclosed in curly braces after the method’s LANGUAGE ObjectScript keyword. There is embedded SQL code in the ObjectScript code, marked with &SQL and enclosed in parentheses.