Class Requests at InterSystems Caché

Introduction

InterSystems Caché Class Requests is a useful tool used to abstract from SQL queries directly in COS code. In the simplest case, it looks like this: suppose 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 text of the query as a query of the class and subsequently refer to this query by its name. And then there are custom queries in which you write the logic for obtaining the next line of results yourself. Under the cut, I will talk about how this all can be used.

Basic Class Requests

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

• Type - % SQLQuery
• In the argument list, list the SQL query argument list.
• Request Type - SELECT
• The argument is accessed with 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) Determine the parameter CONTAINID; it is equal to the sequence number of the field containing Id. If Id is not returned, it is not necessary to specify CONTAINID
• (Optional) Define the COMPILEMODE parameter. It is similar to the same parameter in static SQL and determines when the SQL expression is compiled. If equal to IMMEDIATE (the 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 parameter SELECTMODE - 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. Default query name in SQL context: PackageName.ClassName_QueryName
• Caché Studio provides a class query wizard

You can use this query 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 the delivery of Caché. That's all about simple queries. We now turn to custom queries.

Custom Class Requests

Basic class queries are sufficient for most situations. However, there are cases in which your application needs complete 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 query method that gives the following query result, you write yourself on COS, then this logic can be arbitrarily complex
• If you access data through an API whose format does not suit you
• If the data is stored in globala, without classes
• If access to data requires escalation of rights
• If you need to request an external API to access the data
• If access to the data requires access to the file system
• Any additional operations are required before executing the request itself (establishing a connection, checking rights, etc.)

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

• queryName - similar to the base class query, provides information about the query
• queryName Execute - initial request instantiation
• queryName Fetch - gets the following result
• queryName Close - query destructor

Now about these methods in more detail.

QueryName method

The queryName method provides information about the request.

• Type -% Query
• Leave definition empty
• 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) Determine the parameter CONTAINID; it is equal to the sequence number of the field containing Id. If Id is not returned, it is not necessary to specify CONTAINID

As an example, we will create an AllRecords query (those. QueryName = AllRecords, and the method will be called simply AllRecords), which will in turn produce 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 we’ll 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 methods of query implementation
• This method should bring qHandle to the state that the queryNameFetch method takes as input.
• qHandle can take the values ​​of OREF, variable or multidimensional variable
• args are optional parameters passed to the query. They can be arbitrarily many or not at all
• Return the request initialization status

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

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

QueryNameFetch method

The queryNameFetch method returns one result in $ List format. 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 methods of query implementation
• When executing a query, qHandle accepts the values ​​set by queryNameExecute or by previous calling queryNameFetch
• Row must accept either a value in the % List format, or it must be equal to an empty string if there is no more data
• AtEnd must be 1 when the end of the data is reached.
• The PlaceAfter keyword determines the position of the method in the int code (there is an article about compiling and generating int code), 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 whether the end of the data is reached.
2. If the data is still there: Create% List and set the value of the Row variable
3. Otherwise, set AtEnd to 1
4. Install qHandle for subsequent calls
5. We return the 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
{
#; Go around the global ^ Utils.CustomQueryD
#; 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 whether 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 AllRecords ROWSPEC request
Set Row = $ Lb ( qHandle , $ Lg ( val , 2), $ Lg ( val , 3))
Quit $$$ OK
}

QueryNameClose method

The queryNameClose method terminates with a query after all data has been received. It should have the following signature:

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

Where:

• Caché performs 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, you need to delete the local variable qHandle:

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 in the same way as the basic class queries — using% SQL.Statement.

Custom query logic

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

• Global traversal
• Static SQL
• Dynamic SQL

Global traversal

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

• The data is stored in globala, without classes
• It is necessary to reduce the number of gloref - calls to globals
• Results must / 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 working with cursors
• Reduce compile time (static SQL is rendered in the class query and is compiled only once)

Features:

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

Dynamic SQL

The approach is to use other class queries and dynamic SQL. This is relevant for cases when, in addition to the actual query, which we represent in the form of SQL, you need to perform some additional actions, for example, you need to execute a SQL query, but in several areas in turn. Or you need an escalation of rights before executing the request.

Alternative approach -% SQL.CustomResultSet

Alternatively, you can define the query as an inheritor of the % SQL.CustomResultSet class. On Habré there is an article about using % 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 the principles of OOP

When creating an inheritor of the% SQL.CustomResultSet class, the following steps must be performed:

1. Determine the properties that match the result fields.
2. Define private properties that will contain the context of the query and not be part of the result.
3. Redefine the% OpenCursor method — the equivalent of the queryNameExecute method, which is responsible for the initial creation of the context. In case of errors, set% SQLCODE and% Message
4. Redefine the% Next method - an analogue of the queryNameFetch method responsible for obtaining the following result. Fill in the properties. The method returns 0, if there is no more data, if there is, then 1
5. Redefine the% CloseCursor method - an analogue of the queryNameClose method, if necessary

Call it from the 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 query for the Samples.Person class.

findings

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

Links

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

The author is grateful to Habrayuser adaptun for help in writing the article.