%SQL.CustomQuery
abstract class %SQL.CustomQuery extends %SQL.IResultSet
%SQL.CustomQuery is the root class for custom query function classes. You can extend this class to implement SQL Table-Valued Functions that can be used in SQL statements. Custom query functions 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 as a function in an SQL statement, is 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 to explicitly define a ROWSPEC.
When subclassing %SQL.CustomQuery, there are a few steps that you must follow in order to produce a working table-valued function.
1. Override the SQLNAME parameter to assign a name to the custom function;
2. Define properties that correspond to each column in the result row. These properties correspond to the columns that form the ROWTYPE of the
table function. Column numbers are assigned in the order in which the properties appear in the class definition. 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.
3. Define any private properties needed to maintain the current state of the cursor. Any property defined as PRIVATE is automatically excluded from the ROWTYPE.
4. Override and implement %OpenCursor(). Formal arguments defined by %OpenCursor become the formal arguments for the projected table-value function. Code in this method initializes the cursor and prepares the data to be fetched.
5. 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 maintain the 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.
The following class definition is an example of a simple custom query. This example accepts a JSON array and presents the name and city properties from each element of the array as a row.
Class User.TVF Extends %SQL.CustomQuery { Parameter SQLNAME = "custom_tvf"; Property name As %String; Property city As %String; Property sourceData As %Library.DynamicArray [ Private ]; Property iterator As %Iterator.Array [ Private ]; Method %OpenCursor(sourceData As %String(MAXLEN="")) [ Private ] { if ('$isObject(sourceData)) || ('sourceData.%IsA("%Library.DynamicAbstractObject")) { set ..sourceData = ##class(%Library.DynamicAbstractObject).%FromJSON(sourceData) } else { set ..sourceData = sourceData } set ..iterator = ..sourceData.%GetIterator() } Method %CloseCursor() [ PlaceAfter = %Next, Private ] { set ..iterator = "" set ..sourceData = "" } Method %FetchCursor(ByRef sc As %Library.Status = {$$$OK}) As %Library.Integer { if ($isObject(..iterator)) && (..iterator.%GetNext(.key,.value)) { set ..name = value.name set ..city = value.city } else { set ..iterator = "" return 0 } return 1 } }
The above example can be referenced in the from clause of a SELECT statement.
select * from custom_tvf('[{"name":"Hermann, George","city":"New York, NY"},{"name":"Orr, Bobby","city":"Boston, MA"}]') order by city
name | city |
---|---|
Orr, Bobby | Boston, MA |
Hermann, George | New York, NY |
2 Rows(s) Affected |
A custom query function can also be instantiated directly and iterated over using the result set interface. The first argument passed to %New() is SELECTMODE, as is defined by the %SQL.IResultSet interface. Subsequent arguments are as defined by the %OpenCursor().
USER>set cursor = ##class(TVF).%New(,[{"name":"Hermann, George","city":"New York, NY"},{"name":"Orr, Bobby","city":"Boston, MA"}]) USER>write cursor.%Next() 1 USER>write cursor.name Hermann, George USER>write cursor.city New York, NY USER>write cursor.%SQLCODE 0 USER>write cursor.%Next() 1 USER>write cursor.name Orr, Bobby USER>write cursor.%Next() 0 USER>write cursor.%SQLCODE 100
Method Inventory
Parameters
Methods
Advance the cursor to the next row. If the cursor is found to be at the end of the data then return 0, otherwise populate the row properties and return 1.
To report an error set %SQLCODE and %Message to appropriate values. It is also acceptable to simply throw an exception and the system will assign appropriate values to %SQLCODE and %Message.
If colname is not a valid column name, this method throws a error.
Inherited Members
Inherited Properties
- %CurrentResult
- %Message
- %OutputColumnCount
- %PrivateTables
- %ROWCOUNT
- %ROWID
- %ResultColumnCount
- %SQLCODE
- %StatementType
- %StatementTypeName
Inherited Methods
- %ClassIsLatestVersion()
- %ClassName()
- %ConstructClone()
- %DispatchClassMethod()
- %DispatchGetModified()
- %DispatchMethod()
- %DispatchSetModified()
- %DispatchSetMultidimProperty()
- %Display()
- %DisplayFormatted()
- %DisplayMarkDown()
- %Extends()
- %GetClientMetadata()
- %GetMetadata()
- %GetParameter()
- %GetRow()
- %GetRows()
- %GetStatement()
- %IsA()
- %IsModified()
- %MoreResults()
- %New()
- %NextOutputValue()
- %NextResult()
- %NormalizeObject()
- %ObjectModified()
- %OriginalNamespace()
- %PackageName()
- %Print()
- %SerializeObject()
- %SetModified()
- %ValidateObject()
- Close()
- Fetch()
- FetchRows()
- GetInfo()
- GetODBCInfo()
- getSelectMode()