Announcement
Neerav Adam Verma · Feb 13, 2020

Class Queries

Class Queries in InterSystems IRIS (and Cache, Ensemble, HealthShare) is a useful tool that separates SQL queries from Object Script code. Basically, it works like this: suppose that you want to use the same SQL query with different arguments in several different places.In this case you can avoid code duplication by declaring the query body as a class query and then calling this query by name. 

They are declared as Query items in class definitions (similar to Method or Property) in the following way:

  • Type: %SQLQuery
  • All arguments of your SQL query must be listed in the list of arguments
  • Query type: SELECT
  • Use the colon to access each argument (similar to static SQL)
  • Define the ROWSPEC parameter which contains information about names and data types of the output results along with the order of fields
  • (Optional) Define the CONTAINID parameter which corresponds to the numeric order if the field containing ID. If you don't need to return ID, don't assign any values to CONTAINID
  • (Optional) Define the COMPILEMODE parameter which corresponds to the similar parameter in static SQL and specifies when the SQL expression must be compiled. When this parameter is set to IMMEDIATE (by default), the query will be compiled simultaneously with the class. When this parameter is set to DYNAMIC, the query will be compiled before its first execution (similar to dynamic SQL)
  • (Optional) Define the SELECTMODE parameter which specifies 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. The default name of a query in SQL context is as follows: PackageName.ClassName_QueryName
  • Caché Studio provides the built-in wizard for creating class queries

Sample definition of the Person class with the ByName query which returns all user names that begin with a specified letter

Class NV.Person Extends %Persistent
{
Property Name As %String;
Property DOB As %Date;
Property SSN As %String;
Query ByName(name As %String = "") As %SQLQuery
    (ROWSPEC="ID:%Integer,Name:%String,DOB:%Date,SSN:%String", 
     CONTAINID = 1, SELECTMODE = "RUNTIME", 
     COMPILEMODE = "IMMEDIATE") [ SqlName = SP_NV_By_Name, SqlProc ]
{
SELECT ID, Name, DOB, SSN
FROM NV.Person
WHERE (Name %STARTSWITH :name)
ORDER BY Name
}
}

You can call this query from Caché Object Script in the following way: 

Set statement=##class(%SQL.Statement).%New()   
Set status=statement.%PrepareClassQuery("NV.Person","ByName")   
If $$$ISERR(status) {
    Do $system.OBJ.DisplayError(status) 
}   
Set resultset=statement.%Execute("A")   
While resultset.%Next() {
    Write !, resultset.%Get("Name")   
}

Alternatively, you can obtain a resultset using the automatically generated method queryNameFunc:

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

Alternative approach: %SQL.CustomResultSet

Alternatively, you can create a query by subclassing from the %SQL.CustomResultSet class. Benefits of this approach are as follows:

  • Slight increase in speed
  • ROWSPEC is unnecessary, since all metadata is obtained from the class definition
  • Compliance with the object-oriented design principles

To create query from the subclass of %SQL.CustomResultSet class, make sure to perform the following steps:

  1. Define the properties corresponding to the resulting fields
  2. Define the private properties where the query context will be stored
  3. Override the %OpenCursor method (similar to queryNameExecute) which initiates the context. In case of any errors, set %SQLCODE and %Message as well
  4. Override the %Next method (similar to queryNameFetch) which obtains the next result. Fill in the properties. The method returns 0 if all the data has been processed and 1 if some data is still remaining
  5. Override the %CloseCursor method (similar to queryNameClose) if necessary

Example of %SQL.CustomResultSet for Utils.CustomQuery:

Class Utils.CustomQueryRS Extends %SQL.CustomResultSet
{
Property Id As %String;
Property Prop1 As %String;
Property Prop2 As %Integer;
Method %OpenCursor() As %Library.Status
{
    Set ..Id = ""
    Quit $$$OK
}

Method %Next(ByRef sc As %Library.Status) As %Library.Integer [ PlaceAfter = %Execute ]
{
    Set sc = $$$OK
    Set ..Id = $Order(^Utils.CustomQueryD(..Id),1,val)
    Quit:..Id="" 0
    Set ..Prop1 = $Lg(val,2)
    Set ..Prop2 = $Lg(val,3)
    Quit $$$OK
}
}

You can call it from Caché Object Script code in the following way:

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

Replies

Tasks contain session reference so you can simplify your query to:

SELECT
TaskStatus_Request As ID
FROM EnsLib_Workflow.TaskResponse
WHERE TaskStatus_SessionId = ?

For debugging: add hang 1 before executing SQL. What happens?

What do you want to do with ID?

Hi Eduard,

The query is not a problem as the same query runs fine when we run on sql browser via smp. 

There is one flow with two threads

1. Goes and calls a workflow operation , thus creating the workflow task response etc.

2. Hang (Delay in BPL) for few seconds and then calls the query with session. And this query gives null result. But same query with same parameters runs fine in sql browser.

We are going to open the task request object with the id and persist its values for audit trail / historical data / reporting (it will be a mix of task request / response values + lot of others). As long as we get the TaskRequest ID we are good

May be is there a simple example with just one flow, 2 sequences as explained above? 

Neerav, have you tried logging the session ID that you're passing to the query in the BPL? I only suggest this in order to rule out the possibility that the BPL code is failing to get the correct session ID and that's why the query is not returning any results.

Yes Marc.  It logs fine as well and can see it in the visual trace too in the message header values.

There could be two issues:

  • Race condition
  • SQL error

For race condition you need to compare the time when the query is executed to the time when Workflow objects are created.

For sql construct query as a string and trace that.