The %SQLQuery class is used for defining class queries based on SQL definitions.
parameter COMPILEMODE = "IMMEDIATE";
COMPILEMODE is a parameter that similar to the embedded SQL COMPILE directive. COMPILEMODE
defaults to 'immediate' which means that the SQL Query will be processed as embedded SQL
and the statement is compiled at the same time as the query methods are compiled. If the value
is specified as 'dynamic' then the query will be generated at compile time but the statement
is processed as a dynamic statement, meaning it will be dynamically prepared. A dynamic prepare
checks at runtime to see if the statement has been compiled, if so it just runs the statement. If
the statement has not been compiled then it is compiled and then run. The advantage of 'dynamic'
COMPILEMODE is that the cost of preparing the statement is deferred until first call and the
query can respond to changes to the referenced tables, including any changes made to the table
parameter SELECTMODE = "RUNTIME";
The SELECTMODE parameter allows the query author to declare the format of the values returned by the query.
It is the responsibility of the query implementation to properly format the values according to the SELECTMODE
value. If SELECTMODE is RUNTIME then the query implementation has to examine the current runtime setting and react
accordingly. The current runtime SELECTMODE value can be retrieved by calling $system.SQL.GetSelectMode(). The default value
of SELECTMODE is LOGICAL to preserve compatibility with older implementations.
REMEMBER - it is only a declaration and the implementation of the query is responsible for properly formatting the column
values in a manner consistent with the SELECTMODE setting.
classmethod Close(%qHandle As %Binary)
Close the query associated with the handle
classmethod Execute(ByRef %qHandle As %Binary)
Execute is passed parameters for Query
Returns handle for subsequent operations
classmethod Fetch(ByRef %qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0)
Fetch the next row of data from the query.
classmethod FetchRows(ByRef %qHandle As %Binary, FetchCount As %Integer = 0, ByRef RowSet As %List, ByRef ReturnCount As %Integer, ByRef AtEnd As %Integer)
qHandle is query-defined data and is typically updated by Fetch.
The Fetch method should set Row to be a $List of
values corresponding to the columns of the query. If we are past the end of the data
and no row is returned, Row should be set to null string ("").
If the non-empty row being returned is the last one, AtEnd could be
optionally set to 1, indicating that an additional call to Fetch may be avoided.
Row may also be subscripted. If any of the columns in the row are stream orefs
then the oref type information is lost in the Row $list encoded value. To retain
the oref type, place the oref into Row(columnNumber). %Library.ResultSet recognizes
this form and will extract the column value as the subscripted value.
FetchRows returns the next FetchCount rows in the query
Updates handle for subsequent operations
Returns $list for row or "" if no more rows
Func is a method that is used to invoke the query as a function, returning an instance of %SQL.StatementResult.
classmethod GetInfo(ByRef colinfo As %List, ByRef parminfo As %List, ByRef idinfo As %List, ByRef %qHandle As %Binary, extoption As %Integer = 0, extinfo As %List)
Returns information about the query. It is used by the various ResultSet objects.
classmethod SendODBC(ByRef %qHandle As %Binary, ByRef array As %String, qacn As %Integer, ByRef %qrc As %Integer, ByRef piece As %Boolean, ColumnCount As %Integer)
Fetch and send a series of rows for the ODBC/JDBC server. For internal use only.