Defining Custom Class Queries

Although simple %SQLQueryOpens in a new tab queries perform all result set management for you, this is not sufficient for certain applications. For such situations, Caché allows you to write custom queries, which are defined in methods (which by default are written in ObjectScript). To define a custom query, use the instructions given earlier in this chapter, with the following changes:

Specify %QueryOpens in a new tab for the query type.

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 following subsections provide details on them.

For basic demonstration purposes, the first three subsections show a simple example that could also be implemented as a basic class query; you can use this sample in the SAMPLES namespace. 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. This is only one possibility; see “Additional Custom Class Example” for another approach.

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 Caché 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 rs=$get(qHandle)
    if rs="" quit $$$OK

    if rs.%Next() {
        set Row=$lb(rs.%GetData(1),rs.%GetData(2),rs.%GetData(3),rs.%GetData(4))
        set AtEnd=0
    } else {
        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 Caché 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 — the %Library.ResultSetOpens in a new tab object uses them to process query requests.