Using Caché Objects
Defining and Using Class Queries
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   

This chapter discusses class queries, which act as named queries that are part of a class structure and that can be accessed via dynamic SQL. It discusses the following topics:

When viewing this book online, use the preface of this book to quickly find related topics.
Introduction to Class Queries
A class query is a tool — contained in a class and meant for use with dynamic SQL — to look up records that meet specified criteria. With class queries, you can create predefined lookups for your application. For example, you can look up records by name, or provide a list of records that meet a particular set of conditions, such as all the flights from Paris to Madrid.
By creating a class query, you can avoid having to look up a particular object by its internal ID. Instead, you can create a query that looks based on any class properties that you want. These can even be specified from user input at runtime.
If you define a custom class query, your lookup logic can use ObjectScript and can be arbitrarily complex.
There are two kinds of class queries:
Note that you can define class queries within any class; there is no requirement to contain them within persistent classes.
Do not define a class query that depends upon the results of another class query. Such a dependency is not supported.
Using Class Queries
Before looking at how to define class queries, it is useful to see how you can use them. In server-side code, you can use a class query as follows:
  1. Use %New() to create an instance of %SQL.Statement.
  2. Call the %PrepareClassQuery() method of that instance. As arguments, use the following, in order:
    1. Fully qualified name of the class that defines the query that you want to use.
    2. Name of the query in that class.
    This method returns a %Status value, which you should check.
  3. Call the %Execute() method of the %SQL.Statement instance. This returns an instance of %SQL.StatementResult.
  4. Use methods of %SQL.StatementResult to retrieve data from the result set. For details, see Dynamic SQL in Using Caché SQL.
Note that you can use the older dynamic SQL API (%ResultSet) in a similar manner.
The following shows a simple example that you can use in the SAMPLES namespace. This example uses the ByName query of Sample.Person:
 // classquerydemo

#include %occInclude
 set statement=##class(%SQL.Statement).%New()
 set status=statement.%PrepareClassQuery("Sample.Person","ByName")
 if $$$ISERR(status) { do $system.OBJ.DisplayError(status) }
 set resultset=statement.%Execute()
 while resultset.%Next() {
    write !, resultset.%Get("Name")
If you are using the Caché Java or ActiveX binding, you can use the result set classes that are part of that binding.
If the query is marked with SqlProc, which defines it as an ODBC or JDBC stored procedure, you can invoke it as a stored procedure from an SQL context. See “Defining and Using Stored Procedures” in Using Caché SQL.
Defining Basic Class Queries
To define a basic class query, define a query as follows:
Studio provides a wizard (the New Query Wizard) that you can use to define such a basic class query. The following subsection shows an example.
The following shows a simple example:
Query ListEmployees(City As %String = "") 
   As %SQLQuery (ROWSPEC="ID:%Integer,Name:%String,Title:%String", CONTAINID = 1) [SqlProc, SqlName=MyProcedureName]
SELECT ID,Name,Title FROM Employee
 WHERE (Home_City %STARTSWITH :City)
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. To edit ROWSPEC, the options are:
The number of elements in the ROWSPEC parameter must match the number of fields in the query. Otherwise, Caché returns a “Cardinality Mismatch” error.
For an example, in the SAMPLES database, the ByName query of the Sample.Person sample class is as follows:
Query ByName(name As %String = "") 
   [ 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.
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. If you create a query using the New Query Wizard, then Studio automatically assigns the appropriate value to CONTAINID, based on the order you specify in that wizard.
Caché does not validate the value of CONTAINID. If you specify a non-valid value for this parameter, Caché does not throw an error. This means that if your query processing logic depends on this information, you may experience inconsistencies if the CONTAINID parameter is set improperly.
Other Parameters of the Query Class
In addition to ROWSPEC and CONTAINID, you can specify the following parameters of the query. These are class parameters for %SQLQuery:
For details, see the class reference for %Library.SQLQuery and %Library.Query (its superclass).
Defining Custom Class Queries
Although simple %SQLQuery 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:
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
Within this implementation of method, use the following general logic:
  1. Perform any one-time setup steps.
    For queries using SQL code, this method typically includes declaring and opening a cursor.
  2. Set qHandle as needed by the querynameFetch method.
  3. 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.StatementResult, 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 ]
Within this implementation of method, use the following general logic:
  1. Check to determine if it should return any more results.
  2. If appropriate, retrieve a row of data and create a %List object and place that in the Row variable.
  3. Set qHandle as needed by subsequent invocations (if any) of this method or needed by the querynameClose() method.
  4. If no more rows exist, set Row to a null string and set AtEnd to 1.
  5. 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.StatementResult 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 ]
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.ResultSet object uses them to process query requests.
Defining Parameters for Custom Queries
If the custom query should accept parameters, do the following:
Additional Custom Query Example
The previous section provides a simple example of a custom class query, one that could easily be implemented instead as a basic class query. This section shows a more typical example from the Caché class library. Also see the next section for additional ideas.
This example is presented to demonstrate an approach you can use, not to document how the class library implements specific features. Thus this section does not indicate which class currently contains this code, nor will this section be updated to reflect future changes in that class.
In this example, the query builds and uses a process-private global. The query is defined as follows:
Query ByServer() As %Query(ROWSPEC = "Name,Port,PingPort,Renderer,State,StateEx") [ SqlProc ]
The querynameExecute() method is as follows:
ClassMethod ByServerExecute(ByRef qHandle As %Binary) As %Status [ Internal ]
    Set tSC = $$$OK
    Try {
        Set tRS = ##class(%ResultSet).%New("%ZEN.Report.RenderServer:ByName")
        Kill ^||%ISC.ZRS
        Set tSC = tRS.Execute()
        For {
            Set tType = tRS.Get("ServerType")
            If (tType'=0) && (tType'="") Continue // Not a Render Server
            Set name = tRS.Get("Name")
            Set ^||%ISC.ZRS(name) = $LB(name,tRS.Get("Port"),tRS.Get("PingPort"),tRS.Get("Renderer"))
    Catch (ex) {
        Set tSC = ex.AsStatus()
    Set qHandle = $LB("")
    Quit tSC
Notice that this method saves the data into a process-private global and not into the qHandle variable. Also note that this method uses the older dynamic SQL class (%ResultSet).
The querynameFetch() method is as follows:
ClassMethod ByServerFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) 
As %Status [ Internal, PlaceAfter = ByServerExecute ]
    Set index = $List(qHandle,1)
    Set index = $O(^||%ISC.ZRS(index))
    If index="" {
        Set Row = ""
        Set AtEnd = 1
    Else {
        Set Row = ^||%ISC.ZRS(index)
        Set stInt = ..GetState($List(Row,2),$List(Row,3),$List(Row,4))
        Set stExt = $Case(stInt,0:$$$Text("Inactive"),1:$$$Text("Active"),
        Set $List(Row,5) = stInt, $List(Row,6) = stExt
    Set qHandle = $LB(index)
    Quit $$$OK
Finally, the querynameClose() method is as follows:
ClassMethod ByServerClose(ByRef qHandle As %Binary) As %Status [ Internal, PlaceAfter = ByServerExecute ]
    Set qHandle = ""
    Kill ^||%ISC.ZRS
    Quit $$$OK
When to Use Custom Queries
The following list suggests some scenarios when custom queries are appropriate:
SQL Cursors and Class Queries
If a class query uses an SQL cursor, note the following points: