Class Requests at InterSystems Caché

Introduction

Class Queries InterSystems Caché is a useful tool used to abstract directly SQL queries in COS code. In the simplest case, it looks like this: let's say you use the same SQL query in several places, but with different arguments.

In order not to write it every time, you can designate the request text as a class request and in the future refer to this request by its name. And there are also custom queries in which you write the logic for getting the next row of results yourself. Under the cut, I’ll talk about how you can use it all.

Basic class queries

So, basic class queries are a method for representing SELECT SQL queries. They are handled by the SQL optimizer and compiler, just like regular SQL queries, but they are easier to call from within the COS context. In the class definition, these are elements of the Query type (similar, for example, to Method or Property). They are defined as follows:

• Type - % SQLQuery
• In the list of arguments you need to list the list of SQL query arguments
• Type of request - SELECT
• The argument is accessed through a colon (similar to static SQL)
• Define the ROWSPEC parameter - it contains information about the names and data types of the returned results, as well as the order of the fields
• (Optional) Define the CONTAINID parameter; it is equal to the serial number of the field containing Id. If Id is not returned, no CONTAINID is required
• (Optional) Define the COMPILEMODE parameter. It is similar to the same parameter in static SQL and determines when the SQL expression is compiled. If IMMEDIATE is equal (by default), then compilation occurs during class compilation. If equal to DYNAMIC, then compilation occurs before the first execution of the query, similar to dynamic SQL
• (Optional) Define the SELECTMODE parameter - declaration of the format of the query results
• Add the SqlProc property if you want to call this query as an SQL procedure
• Set the SqlName property if you want to rename the query. By default, the query name in the SQL context: PackageName.ClassName_QueryName
• Caché Studio provides a class request wizard

You can use this request from the COS context as follows:

   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 ("A")
   While resultset.% Next () {
         Write!, Resultset.% Get ("Name")
   }

Or immediately get the resultset using the generated queryNameFunc method:

   Set resultset = ## class (Sample.Person) .ByNameFunc ("A") 
   While resultset.% Next () {
         Write!, Resultset.% Get ("Name")
   }

In addition, this query can be called from the SQL context in two ways:

Call Sample.SP_Sample_By_Name('A')

Select * from Sample.SP_Sample_By_Name('A')

This class can be found in the SAMPLES area that comes with Caché. That's all about simple queries. Now let's move on to custom queries.

Custom class requests

Basic class queries are sufficient for most situations. However, there are cases in which your application needs full control over the behavior of the request, in particular:

• The complex logic of determining which records should fall into the result. Since in a custom request you write the method that produces the following query result yourself in COS, this logic can be arbitrarily complex
• If you access data through an API whose format does not suit you
• If data is stored in globals, no classes
• If access to data requires an escalation of rights
• If you need to request an external API to access the data
• If you need access to the file system to access data
• Some additional operations are required before executing the request itself (establishing a connection, checking rights, etc.)

So, how are custom class queries written? To create a queryName query, you define 4 methods that implement the entire logic of the query, from creation to destruction:

• queryName - similar to the basic query of a class, provides information about the query
• queryName Execute - initializes the query
• queryName Fetch - obtains the following result
• queryName Close - query destructor

Now about these methods in more detail.

QueryName method

The queryName method provides information about the query.

• Type -% Query
• Leave the definition blank.
• Define the ROWSPEC parameter - it contains information about the names and data types of the returned results, as well as the order of the fields
• (Optional) Define the CONTAINID parameter; it is equal to the serial number of the field containing Id. If Id is not returned, no CONTAINID is required

As an example, we will create an AllRecords request (those. QueryName = AllRecords, and the method will be called simply AllRecords), which will in turn return all the records of the stored class.

First, create a new stored class Utils.CustomQuery:

Class Utils.CustomQuery Extends (% Persistent,% Populate)
{
Property Prop1 As% String;
Property Prop2 As% Integer;
}

Now write a description of the AllRecords request:

Query AllRecords () As% Query (CONTAINID = 1, ROWSPEC = "Id:% String, Prop1:% String, Prop2:% Integer") [SqlName = AllRecords, SqlProc]
{
}

QueryNameExecute method

The queryNameExecute method performs all the necessary query initialization. It should have the following signature:

ClassMethod queryNameExecute (ByRef qHandle As% Binary, args) As% Status

Where:

• qHandle is used to communicate with other query implementation methods.
• This method should bring qHandle to the state that the queryNameFetch method receives as input
• qHandle can be OREF, variable, or multidimensional
• args are additional parameters passed to the request. They can be arbitrarily many or not at all
• Request initialization status is returned

Let us return to our example. There are many options for circumventing the extent (the main approaches to organizing custom queries will be described below), I suggest using the global circumvention using the $ Order function . qHandle will accordingly store the current Id, in this case an empty string. arg is not used, as any additional arguments are not needed. The result is:

ClassMethod AllRecordsExecute (ByRef qHandle As% Binary) As% Status
{
    Set qHandle = ""
    Quit $$$ OK
}

QueryNameFetch Method

The queryNameFetch method returns a single result in the format of $ List . It should 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 other query implementation methods.
• When executing a query, qHandle accepts the values ​​set by queryNameExecute or a previous call to queryNameFetch
• Row must accept either a value in the format % List , or it must be equal to an empty string if there is no more data
• AtEnd must be 1 when reaching the end of the data
• The PlaceAfter keyword determines the position of the method in int code (there is an article on compiling and generating int code on the hub ), the Fetch method should be located after the Execute method, this is important only when using static SQL , or rather cursors inside the query.

Inside this method, in general, the following operations are performed:

1. Determine if the end of the data has been reached.
2. If there is still data: Create a% List and set the value of the Row variable
3. Otherwise, set AtEnd to 1
4. Set qHandle for subsequent calls
5. Return status

In our example, it will look like this:

ClassMethod AllRecordsFetch (ByRef qHandle As% Binary, ByRef Row As% List, ByRef AtEnd As% Integer = 0) As% Status
{
    #; We go around the global ^ Utils.CustomQueryD
    #; We write the next id in qHandle, and the global value with the new id in val
    Set qHandle = $ Order (^ Utils.CustomQueryD (qHandle), 1, val)
    #; Check if the data has reached the end.   
    If qHandle = "" {
        Set AtEnd = 1
        Set Row = ""
        Quit $$$ OK
    }
    #; If not, form% List
    #; val = $ Lb ("", Prop1, Prop2) - see Storage Definition
    #; Row = $ Lb (Id, Prop1, Prop2) - see the ROWSPEC request AllRecords
    Set Row = $ Lb (qHandle, $ Lg (val, 2), $ Lg (val, 3))
    Quit $$$ OK
}

QueryNameClose method

The queryNameClose method terminates the query after receiving all the data. It should have the following signature:

ClassMethod queryNameClose (ByRef qHandle As% Binary) As% Status [PlaceAfter = queryNameFetch]

Where:

• Caché executes this method after the last call to the queryNameFetch method
• This method is a request destructor
• In the implementation of this method, close the used SQL cursors, queries, delete local variables
• Method returns status

In our example, we need to delete the local qHandle variable:

ClassMethod AllRecordsClose (ByRef qHandle As% Binary) As% Status
{
    Kill qHandle
    Quit $$$ OK
}

That's all. After compiling the class, the AllRecords query can be used similarly to the basic class queries - using% SQL.Statement.

Custom query logic

So, how can you organize custom query logic? There are 3 main approaches:

• Global Bypass
• Static SQL
• Dynamic SQL

Global Bypass

The approach is to use the $ Order function and the like to bypass the global. It should be used in cases if:

• Data is stored in globals, without classes
• We need to reduce the number of gloref - calls to globals
• Results should / can be sorted by global key.

Static SQL

The approach is to use cursors and static SQL. This can be done in order to:

• Simplify reading int code
• Simplify Cursors
• Reduced compilation time (static SQL is placed in the class request and compiles only once)

Features:

• Cursors generated from queries such as% SQLQuery are automatically named, for example, Q14
• All cursors used within the class must have different names.
• Error messages refer to the internal names of cursors that have an extra character at the end of the name. For example, an error in the Q140 cursor most likely relates to the Q14 cursor
• Use PlaceAfter and make sure that the declaration and use of the cursor occur in one int program
• INTO should be located with FETCH, not DECLARE

Dynamic SQL

The approach is to use other class queries and dynamic SQL. It is relevant for cases when, in addition to the actual query, which can be represented in the form of SQL, you need to perform some additional actions, for example, you must execute the SQL query, but in several areas one at a time. Or before executing the request, an escalation of rights is needed.

Alternative approach -% SQL.CustomResultSet

Alternatively, you can define the query as a descendant of the % SQL.CustomResultSet class . There is an article on the hub about the use of % SQL.CustomResultSet . The advantages of this approach:

• Slightly higher speed
• All meta information is taken from the class definition, ROWSPEC is not needed
• Compliance with OOP principles

When creating the inheritor of the% SQL.CustomResultSet class, you must perform the following steps:

1. Define the properties that will match the result fields.
2. Define private properties that will contain the request context and not be part of the result
3. Переопределите метод %OpenCursor — аналог метода queryNameExecute, отвечающий за первоначальное создание контекста. В случае возникновения ошибок установите %SQLCODE и %Message
4. Переопределите метод %Next — аналог метода queryNameFetch отвечающий за получение следующего результата. Заполните свойства. Метод возвращает 0, если данных больше нет, если есть, то 1
5. Переопределите метод %CloseCursor — аналог метода queryNameClose, если это необходимо

You can call it from COS code as follows:

    Set resultset = ## class (Utils.CustomQueryRS).% New ()
    While resultset.% Next () {
        Write resultset.Id ,!
    }

And in the SAMPLES area, there is an example - the Sample.CustomResultSet class that implements the request for the Samples.Person class.

conclusions

Custom queries allow you to solve problems such as abstraction of SQL code in COS and the implementation of behavior that is difficult to implement only SQL.

References

Class Requests
Global workaround
Static SQL
Dynamic SQL
% SQL.CustomResultSet Utils.CustomQuery
class Utils.CustomQueryRS
class The

author thanks the adaptun habruiser for helping with this article.