Defining the Methods
For basic demonstration purposes, this section shows a simple example that could also be implemented as a basic class query. These methods implement the code for the following query:
Query AllPersons() As %Query(ROWSPEC = "ID:%String,Name:%String,DOB:%String,SSN:%String")
{
}
The next section shows a more complex example. Also see Uses of Custom Queries, for information on other use cases.
Defining the querynameExecute() Method
The querynameExecute() method must provide all the setup logic needed. The name of the method must be querynameExecute, where queryname is the name of the query. This method must have the following signature:
ClassMethod queryNameExecute(ByRef qHandle As %Binary,
additional_arguments) As %Status
Where:
-
qHandle is used to communicate with the other methods that implement this query.
This method should set qHandle as needed by the querynameFetch method.
Although qHandle is formally of type %BinaryOpens in a new tab, it can hold any value, including an OREF or a multidimensional array.
-
additional_arguments is any runtime parameters that the query can use.
Within this implementation of method, use the following general logic:
-
Perform any one-time setup steps.
For queries using SQL code, this method typically includes declaring and opening a cursor.
-
Set qHandle as needed by the querynameFetch method.
-
Return a status value.
The following shows a simple example, the AllPersonsExecute() method for the AllPersons query introduced earlier:
ClassMethod AllPersonsExecute(ByRef qHandle As %Binary) As %Status
{
set statement=##class(%SQL.Statement).%New()
set status=statement.%PrepareClassQuery("Sample.Person","ByName")
if $$$ISERR(status) { quit status }
set resultset=statement.%Execute()
set qHandle=resultset
Quit $$$OK
}
In this scenario, the method sets qHandle equal to an OREF, specifically an instance of %SQL.StatementResultOpens in a new tab, which is the value returned by the %Execute() method.
As noted earlier, this class query could also be implemented as a basic class query rather than a custom class query. Some custom class queries do, however, use dynamic SQL as a starting point.
Defining the querynameFetch() Method
The querynameFetch() method must return a single row of data in $List format. The name of the method must be querynameFetch, where queryname is the name of the query. This method must have the following signature:
ClassMethod queryNameFetch(ByRef qHandle As %Binary,
ByRef Row As %List,
ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = querynameExecute ]
Where:
-
qHandle is used to communicate with the other methods that implement this query.
When InterSystems IRIS® data platform starts executing this method, qHandle has the value established by the querynameExecute method or by the previous invocation (if any) of this method. This method should set qHandle as needed by subsequent logic.
Although qHandle is formally of type %BinaryOpens in a new tab, it can hold any value, including an OREF or a multidimensional array.
-
Row must be either a %List of values representing a row of data being returned or a null string if no data is returned.
-
AtEnd must be 1 when the last row of data has been reached.
-
The PlaceAfter method keyword controls the position of this method in the generated routine code. For querynameExecute, substitute the name of the specific querynameExecute() method. Be sure to include this if your query uses SQL cursors. (The ability to control this order is an advanced feature that should be used with caution. InterSystems does not recommend general use of this keyword.)
Within this implementation of method, use the following general logic:
-
Check to determine if it should return any more results.
-
If appropriate, retrieve a row of data and create a %List object and place that in the Row variable.
-
Set qHandle as needed by subsequent invocations (if any) of this method or needed by the querynameClose() method.
-
If no more rows exist, set Row to a null string and set AtEnd to 1.
-
Return a status value.
For the AllPersons example, the AllPersonsFetch() method could be as follows:
ClassMethod AllPersonsFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status
[ PlaceAfter = AllPersonsExecute ]
{
set rset = $get(qHandle)
if rset = "" quit $$$OK
if rset.%Next() {
set Row=$lb(rset.%GetData(1),rset.%GetData(2),rset.%GetData(3),rset.%GetData(4))
set AtEnd=0
} else {
if (rset.%SQLCODE < 0) {write "%Next failed:", !, "SQLCODE ", rset.%SQLCODE, ": ", rset.%Message quit}
set Row=""
set AtEnd=1
}
quit $$$OK
}
Notice that this method uses the qHandle argument, which provides a %SQL.StatementResultOpens in a new tab object. The method then uses methods of that class to retrieve data. The method builds a $List and places that in the Row variable, which is returned as a single row of data. Also notice that the method contains logic to set the AtEnd variable when no more data can be retrieved.
As noted earlier, this class query could also be implemented as a basic class query rather than a custom class query. The purpose of this example is to demonstrate setting the Row and AtEnd variables.
The querynameClose() Method
The querynameClose() method must perform any needed clean up, after data retrieval has finished. The name of the method must be querynameClose, where queryname is the name of the query. This method must have the following signature:
ClassMethod queryNameClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = querynameFetch ]
Where:
-
qHandle is used to communicate with the other methods that implement this query.
When InterSystems IRIS starts executing this method, qHandle has the value established by the last invocation of the querynameFetch method.
-
The PlaceAfter method keyword controls the position of this method in the generated routine code. For querynameFetch, substitute the name of the specific querynameFetch() method. Be sure to include this if your query uses SQL cursors. (The ability to control this order is an advanced feature that should be used with caution. InterSystems does not recommend general use of this keyword.)
Within this implementation of method, remove variables from memory, close any SQL cursors, or perform any other cleanup as needed. The method must return a status value.
For the AllPersons example, the AllPersonsClose() method could be as follows:
For example, the signature of a ByNameClose() method might be:
ClassMethod AllPersonsClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = AllPersonsFetch ]
{
Set qHandle=""
Quit $$$OK
}
Generated Methods for Custom Queries
The system automatically generates the querynameGetInfo() and querynameFetchRows(). Your application does not call any of these methods directly.
Parameters of the Class Query
This section provides more details on the parameters you specify within a custom class query.
About ROWSPEC
The ROWSPEC parameter for a query provides information on the names, data types, headings, and order of the fields in each row. It is a quoted and comma-separated list of variable names and data types of the form:
ROWSPEC = "Var1:%Type1,Var2:%Type2[:OptionalDescription],Var3"
The ROWSPEC specifies the order of fields as a comma-separated list. The information for each field consists of a colon-separated list of its name, its data type (if it is different than the data type of the corresponding property), and an optional heading.
The number of elements in the ROWSPEC parameter must match the number of fields in the query. Otherwise, InterSystems IRIS® data platform returns a Cardinality Mismatch error.
For an example, the ByName query of the Sample.Person class is as follows:
Query ByName(name As %String = "")
As %SQLQuery(CONTAINID = 1, ROWSPEC = "ID:%Integer,Name,DOB,SSN", SELECTMODE = "RUNTIME")
[ SqlName = SP_Sample_By_Name, SqlProc ]
{
SELECT ID, Name, DOB, SSN
FROM Sample.Person
WHERE (Name %STARTSWITH :name)
ORDER BY Name
}
Here, the CONTAINID parameter specifies that the row ID is the first field (the default); note that the first field specified in the SELECT statement is ID. The ROWSPEC parameter specifies that the fields are ID (treated as an integer), Name, DOB, and SSN; similarly, the SELECT statement contains the fields ID, Name, DOB, and SSN, in that order.
About CONTAINID
CONTAINID should be set to the number of the column returning the ID (1, by default) or to 0 if no column returns the ID.
Note:
The system does not validate the value of CONTAINID. If you specify a non-valid value for this parameter, there is no error message. This means that if your query processing logic depends on this information, you may experience inconsistencies if the CONTAINID parameter is set improperly.
