Defining Custom Class Queries
Although basic class queries perform all result set management for you, this is not sufficient for certain applications. For such situations, you can create custom class queries, which are defined by a set of methods that work together. These methods by default are written in ObjectScript.
Custom class queries are not supported for sharded classes.
Defining Custom Class Queries
To define a custom query, use the instructions for defining basic class queries, with the following changes:
-
Specify %QueryOpens in a new tab for the query type.
-
Specify the ROWSPEC parameter of the query (in parentheses, after the query type). This parameter provides information on the names, data types, headings, and order of the fields in each row of the result set of the query. See About ROWSPEC.
-
Optionally specify the CONTAINID parameter of the query (in parentheses, after the query type). This parameter specifies the column number of the field, if any, that contains the ID for a particular row; the default is 1. See About CONTAINID.
Together, the ROWSPEC and CONTAINID parameters are known as the query specification.
-
Leave the body of the query definition empty. For example:
Query All() As %Query(CONTAINID = 1, ROWSPEC = "Title:%String,Author:%String") { }
-
Define the following class methods in the same class:
-
querynameExecute — This method must perform any one-time setup.
-
querynameFetch — This method must return a row of the result set; each subsequent call returns the next row.
-
querynameClose — This method must perform any cleanup operations.
Where queryname is the name of the query.
Each of these methods accepts an argument (qHandle), which is passed by reference. You can use this argument to pass information among these methods.
These methods define the query. The next section provides details.
-
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.
Defining Parameters for Custom Queries
If the custom query should accept parameters, do the following:
-
Include them in the argument list of the query class member. The following example uses a parameter named MyParam:
Query All(MyParam As %String) As %Query(CONTAINID = 1, ROWSPEC = "Title:%String,Author:%String") { }
-
Include the same parameters in the argument list for querynameExecute method, in the same order as in the query class member.
-
In the implementation of the querynameExecute method, use the parameters as appropriate for your needs.
If you call a class query using ADO.NET, ODBC, or JDBC, any string parameters will be truncated to 50 characters by default. To increase the maximum string length for a parameter, specify a MAXLEN in the signature, as in the following example:
Query MyQuery(MyParam As %String(MAXLEN = 200)) As %Query [SqlProc]
This truncation does not occur if you call the query from the Management Portal or from ObjectScript.
When to Use Custom Queries
The following list suggests some scenarios when custom queries are appropriate:
-
If it is necessary to use very complex logic to determine whether to include a specific row in the returned data. The querynameFetch() method can contain arbitrarily complex logic.
-
If you have an API that returns data in format that is inconvenient for your current use case. In such a scenario, you would define the querynameFetch() method so that converts data from that format into a $List, as needed by the Row variable.
-
If the data is stored in a global that does not have a class interface.
-
If access to the data requires role escalation. In this scenario, you can perform the role escalation within the querynameExecute() method.
-
If access to the data requires calling out to the file system (for example, when building a list of files). In this scenario, you can perform the callout within the querynameExecute() method and then stash the results either in qHandle or in a global.
-
If it is necessary to perform a security check, check connections, or perform some other special setup work before retrieving data. You would do such work within the querynameExecute() method.
SQL Cursors and Class Queries
If a class query uses an SQL cursor, note the following points:
-
Cursors generated from queries of type %SQLQueryOpens in a new tab automatically have names such as Q14.
You must ensure that your cursors are given distinct names.
-
Error messages refer to the internal cursor name, which typically has an extra digit. Therefore an error message for cursor Q140 probably refers to Q14.
-
The class compiler must find a cursor declaration before making any attempt to use the cursor. This means that you must take extra care when defining a custom query that uses cursors.
The DECLARE statement (usually in querynameExecute() method) must be in the same MAC routine as the Close and Fetch and must come before either of them. As shown earlier in this topic, use the method keyword PlaceAfter in both the querynameFetch() and querynameClose() method definitions to make sure this happens.