Previous section   Next section

Defining and Using Class Queries

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.
Important:
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:
  • (For simple class queries) The type should be %SQLQuery.
  • In the argument list, specify any arguments that the query should accept.
  • In the body of the definition, write an SQL SELECT statement.
    In this statement, to refer to an argument, precede the argument name with a colon (
    :
    ).
    This SELECT statement should not include an INTO clause.
  • 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. The second subsection provides the details.
  • 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. The third subsection provides the details.
    Together, the ROWSPEC and CONTAINID parameters are known as the query specification.
  • Include the SqlProc keyword in the query definition.
    You can omit this step if you plan to use %ResultSet to invoke the query and if you do not need to invoke the query as a stored procedure. If you plan to use %SQL.Statement to invoke the query, you must specify the SqlProc keyword.
  • Optionally specify the SqlName keyword in the query definition, if you want the name of the stored procedure to be other than the default name.
    These are compiler keywords, so include them in square brackets after any parameters, after the query type (%SQLQuery).
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.

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)
 ORDER BY Name
}
Note:
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(MyParm As %String(MAXLEN = 200)) As %SQLQuery [SqlProc]

This truncation does not occur if you call the query from the Management Portal or from ObjectScript.

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. To edit ROWSPEC, the options are:
  • Edit the code directly.
  • For an already existing query, display the query in the Studio Inspector window, expand its list of parameters, and use the available dialog box.
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 = "") 
    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. 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.
Note:
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:
  • SELECTMODE
  • COMPILEMODE
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:
  • Specify %Query 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 %Binary, 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:
  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 ]
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 %Binary, 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:
  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 ]
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.ResultSet object uses them to process query requests.

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
    MyParm
    :
    Query All(MyParm 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.
Note:
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(MyParm As %String(MAXLEN = 200)) As %Query [SqlProc]

This truncation does not occur if you call the query from the Management Portal or from ObjectScript.

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.
Important:
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 {
            Quit:'tRS.Next()
            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"),
                   2:$$$Text("Unresponsive"),3:$$$Text("Troubled"),4:$$$Text("Error"),
                   5:$$$Text("Mismatch"),:"")
        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:
  • 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 %SQLQuery 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 chapter, use the method keyword PlaceAfter in both the querynameFetch() and querynameClose() method definitions to make sure this happens.
Previous section   Next section