%SQL.CustomQuery

hidden abstract class %SQL.CustomQuery extends %SQL.IResultSet

%SQL.CustomQuery is the root class for custom query procedure classes. You can extend this class to implement SQL procedures that return a single static result set. Custom query procedures are similar to queries with TYPE = %Library.Query. Subclassing %SQL.CustomQuery has a few advantages over custom class queries. The result set returned by executing, either directly or by an SQL CALL, are more efficient when interacting with the server. The metadata for a result set is constructed from the class definition so there is never a need for ROWSPEC. Also, result sets provide a more object-oriented interface.

You can make custom result sets available to dynamic SQL by implementing a class method projected as a stored procedure. An example of a custom result set is available in the SAMPLES database. There is also an example of creating a stored procedure that returns an instance of a custom result set to the caller. Such a procedure can be invoked using the embedded or dynamic CALL statement.

When subclassing %SQL.CustomQuery, there are a few steps that you must follow in order to produce a working result set.

1. Define properties that correspond to each column in the result row. If the property type is swizzleable then any direct access to the property will trigger swizzling. %Get, %GetData and the various %Send methods will not swizzle the object.

Note: Properties inherited from a system superclass are not considered to be part of the row.

2. Define any private properties needed to maintain the current state of the result set.

3. Override and implement %OpenCursor. Code in this method initializes the result iterator and prepares the data for return. It also reports any errors encountered during execution by setting %SQLCODE and %Message.

4. Override and implement %FetchCursor. Code in this method retrieves the next row and sets the properties corresponding to columns in the row to the appropriate value. If no row is found this method returns 0, otherwise it returns 1. This method must also set value of the %ROWCOUNT property.

5. Override and implement %CloseCursor. This is only necessary if you need to perform some clean up. %CloseCursor is called when the object is destructed.

Method Inventory

• %FetchCursor()
• %Get()
• %GetData()
• %GetSerializedMetadata()
• %Next()
• %OnNew()
• %SendODBC()

Parameters

parameter SQLNAME = {..#SQLNAME};

Override this parameter to define the SQLNAME of the query procedure. If this value is not specified then a default procedure name will be generated using the standard procedure naming convention.

Methods

method %FetchCursor(ByRef sc As %Library.Status = $$$OK) as %Library.Integer

Advance to the next row in the result referenced by %ProcCursor. Returns 0 if the cursor is at the end of the result set. An optional argument contains a %Library.Status value on return. This %Status value indicates success or failure of the %Next call. %SQLCODE is also set by %Next. This implementation is overridden by classes that implement the result set interface.

method %Get(colname As %String = "") as %Library.String

Returns the value of the column with the name colname in the current row of the result set.

If colname is not a valid column name, this method throws a error.

method %GetData(colnbr As %Integer = 0) as %Library.String

%GetData() Returns the value of the column referenced by colnbr. Object values are not swizzled automatically.

classmethod %GetSerializedMetadata(ByRef pMetadata As %CacheString = "") as %Status

Get the serialized %Metadata property value

method %Next(ByRef sc As %Library.Status = $$$OK) as %Library.Integer

Advance to the next row in the result referenced by %ProcCursor. Returns 0 if the cursor is at the end of the result set. An optional argument contains a %Library.Status value on return. This %Status value indicates success or failure of the %Next call. %SQLCODE is also set by %Next. This implementation is overridden by classes that implement the result set interface.

final method %OnNew(pSelectMode As %Integer = {$zu(115, 5)}) as %Library.Status

%OnNew is called by the constructor. It supports a variable number of arguments passed by value. The first argument is the runtime SELECTMODE value and it defaults to $system.SQL.GetSelectMode(). This method is generated as final. It invokes the user implemented %OpenCursor method. Any formal arguments defined by the %OpenCursor method will be added to the formal spec of %OnNew. Actual values for these arguments can be specified when calling %New(). Errors are reported by setting ..%SQLCODE, ..%Message.

method %SendODBC() as %Library.Integer

Fetch and send a series of rows for the ODBC/JDBC server. For internal use only.

Queries

query CQ()

Selects {..#statementmetadata}