Class Queries in InterSystems IRIS (and Cache, Ensemble, HealthShare) is a useful tool that separates SQL queries from 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. This approach is also convenient for custom queries, in which the task of obtaining the next row is defined by a developer. Sounds interesting? Then read on!
Basic class queries
Simply put, basic class queries allow you to represent SQL SELECT queries. SQL optimizer and compiler handle them just as they would standard SQL queries, but they are more convenient when it comes to executing them from
context. 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 Sample.Person class with the ByName query which returns all user names that begin with a specified letter
Class Sample.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_Sample_By_Name, SqlProc ] { SELECT ID, Name, DOB, SSN FROM Sample.Person WHERE (Name %STARTSWITH :name) ORDER BY Name } }
You can call this query from
in the following way: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") }
Alternatively, you can obtain a resultset using the automatically generated method queryNameFunc:
Set resultset = ##class(Sample.Person).ByNameFunc("A") While resultset.%Next() { Write !, resultset.%Get("Name") }
This query can also be called from SQLcontext in these 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 default Caché namespace And that's all about simple queries. Now let's proceed to custom ones.
Custom class queries
Though basic class queries work fine in most cases, sometimes it is necessary to execute full control over the query behavior in applications, e.g.:
- Sophisticated selection criteria. Since in custom queries you implement a method that returns the next row on your own, these criteria can be as sophisticated as you require.
- If data is accessible only via API in a format that you don't want to use
- If data is stored in globals (without classes)
- If you need to escalate rights in order to access data
- If you need to call an external API in order to access data
- If you need to gain access to the file system in order to access data
- You need to perform additional operations before running the query (e.g. establish a connection, check permissions, etc.)
So, how do you create custom class queries? First of all, you should define 4 methods which implement the entire workflow for your query, from initialization to destruction:
- queryName — provides information about a query (similar to basic class queries)
- queryNameExecute — constructs a query
- queryNameFetch — obtains the next row result of a query
- queryNameClose — destructs a query
Now let's analyze these methods in more detail.
The queryName method
The queryName method represents information about a query.
- Type: %Query
- Leave body blank
- Define the ROWSPEC parameter which contains the information about names and data types of the output results along with the field order
- (Optional) Define the CONTAINID parameter which corresponds to the numeric order if the field containing ID. If you don't return ID, don't assign any value to CONTAINID
For example, let's create the AllRecords query (queryName = AllRecords, and the method is simply called AllRecords) which will be outputting all instances of the new persistent class Utils.CustomQuery, one by one. First, let's create a new persistent class Utils.CustomQuery:
Class Utils.CustomQuery Extends (%Persistent, %Populate){ Property Prop1 As %String; Property Prop2 As %Integer; }
Now let's write the AllRecords query:
Query AllRecords() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllRecords, SqlProc ] { }
The queryNameExecute method
The queryNameExecute method fully initializes a query. The signature of this method is as follows:
ClassMethod queryNameExecute(ByRef qHandle As %Binary, args) As %Status
where:
- qHandle is used for communication with other methods of the query implementation
- This method must set qHandle into the state which will then be passed to the queryNameFetch method
- qHandle can be set to OREF, variable or a multi-dimensional variable
- args are additional parameters passed to the query. You can add as many args as you need (or don't use them at all)
- The method must return query initialization status
But let's get back to our example. You are free to iterate through the extent in multiple ways (I will describe the basic working approaches for custom queries below), but as for this example let's iterate through the global using the $Order function. In this case, qHandle will be storing the current ID, and since we don't need any additional arguments, the arg argument is not required. The result looks like this:
ClassMethod AllRecordsExecute(ByRef qHandle As %Binary) As %Status { Set qHandle = "" Quit $$$OK }
The queryNameFetch method
The queryNameFetch method returns a single result in $List form. The signature of this method is as follows:
ClassMethod queryNameFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = queryNameExecute ]
where:
- qHandle is used for communication with other methods of the query implementation
- When the query is executed, qHandle is being assigned values specified by queryNameExecute or by previous call of queryNameFetch.
- Row will be set either to a value of %List or to an empty string, if all data has been processed
- AtEnd must be set to 1, once the end of data is reached.
- The PlaceAfter keyword identifies the method's position in the int code . The "Fetch" method must be positioned after the "Execute" method, but this is important only for static SQL, i.e. cursors inside queries.
In general, the following operations are performed within this method:
- Check whether we've reached the end of data
- If there is still some data left: Create a new %List and assign a value to the Row variable
- Otherwise, set AtEnd to 1
- Prepare qHandle for the next result fetch
- Return the status
This is how it will look like in our example:
ClassMethod AllRecordsFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status { #; Iterating through ^Utils.CustomQueryD #; Writing the next id to qHandle and writing the global's value with the new id into val Set qHandle = $Order(^Utils.CustomQueryD(qHandle),1,val) #; Checking whether there is any more data left If qHandle = "" { Set AtEnd = 1 Set Row = "" Quit $$$OK } #; If not, create %List #; val = $Lb("", Prop1, Prop2) see Storage definition #; Row =$lb(Id,Prop1, Prop2) see ROWSPEC for the AllRecords request Set Row = $Lb(qHandle, $Lg(val,2), $Lg(val,3)) Quit $$$OK }
The queryNameClose method
The queryNameClose method terminates the query, once all the data is obtained. The signature of this method is as follows:
ClassMethod queryNameClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = queryNameFetch ]
where:
- Caché executes this method after the final call to the queryNameFetch method
- In other words, this is a query destructor
- Therefore you should dispose all SQL cursors, queries and local variables in its implementation
- The methods return the current status
In our example, we have to delete the local variable qHandle:
ClassMethod AllRecordsClose(ByRef qHandle As %Binary) As %Status { Kill qHandle Quit $$$OK }
And here we are! Once you compile the class, you will be able to use the AllRecords query from %SQL.Statement – just as the basic class queries.
Iteration logic approaches for custom queries
So, what approaches can be used for custom queries? In general, there exist 3 basic approaches:
Iteration through a global
The approach is based on using $Order and similar functions for iteration through a global. It can be used in the following cases:
- Data is stored in globals (without classes)
- You want to reduce the number of glorefs is the code
- The results must be/can be sorted by the global's subscript
Static SQL
The approach is based on cursors and static SQL. This is used for:
- Making the int code more readable
- Making the work with cursors easier
- Speeding up the compilation process (static SQL is included into the class query and is therefore compiled only once).
Note:
- Cursors generated from queries of the %SQLQuery type are named automatically, e.g. Q14.
- All cursors used within a class must have different names.
- Error messages are related to the internal names of cursors which have an additional characters at the end of their names. For example, an error in cursor Q140 is actually caused by cursor Q14.
- Use PlaceAfter and make sure that cursors are used in the same int routinewhere they have been declared.
- INTO must be used in conjunction with FETCH, but not DECLARE.
Example of static SQL for Utils.CustomQuery:
Query AllStatic() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllStatic, SqlProc ] { } ClassMethod AllStaticExecute(ByRef qHandle As %Binary) As %Status { &sql(DECLARE C CURSOR FOR SELECT Id, Prop1, Prop2 FROM Utils.CustomQuery ) &sql(OPEN C) Quit $$$OK } ClassMethod AllStaticFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = AllStaticExecute ] { #; INTO must be with FETCH &sql(FETCH C INTO :Id, :Prop1, :Prop2) #; Check if we reached end of data If (SQLCODE'=0) { Set AtEnd = 1 Set Row = "" Quit $$$OK } Set Row = $Lb(Id, Prop1, Prop2) Quit $$$OK } ClassMethod AllStaticClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = AllStaticFetch ] { &sql(CLOSE C) Quit $$$OK }
Dynamic SQL
The approach is based on other class queries and dynamic SQL. This is reasonable when in addition to an SQL query itself, you also need to perform some additional operations, e.g. execute an SQL query in several namespaces or escalate permissions before running the query.
Example of dynamic SQL for Utils.CustomQuery:
Query AllDynamic() As %Query(CONTAINID = 1, ROWSPEC = "Id:%String,Prop1:%String,Prop2:%Integer") [ SqlName = AllDynamic, SqlProc ] { } ClassMethod AllDynamicExecute(ByRef qHandle As %Binary) As %Status { Set qHandle = ##class(%SQL.Statement).%ExecDirect(,"SELECT * FROM Utils.CustomQuery") Quit $$$OK } ClassMethod AllDynamicFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status { If qHandle.%Next()=0 { Set AtEnd = 1 Set Row = "" Quit $$$OK } Set Row = $Lb(qHandle.%Get("Id"), qHandle.%Get("Prop1"), qHandle.%Get("Prop2")) Quit $$$OK } ClassMethod AllDynamicClose(ByRef qHandle As %Binary) As %Status { Kill qHandle Quit $$$OK }
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:
- Define the properties corresponding to the resulting fields
- Define the private properties where the query context will be stored
- Override the %OpenCursor method (similar to queryNameExecute) which initiates the context. In case of any errors, set %SQLCODE and %Message as well
- 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
- 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
code in the following way:Set resultset= ##class(Utils.CustomQueryRS).%New() While resultset.%Next() { Write resultset.Id,! }
Another example is available in the SAMPLES namespace – it's the Sample.CustomResultSet class which implements a query for Samples.Person.
Summary
Custom queries will help you to separate SQL expressions from
code and implement sophisticated behavior which can be too difficult for pure SQL.References
The author would like to thank Alexander Koblov for his assistance in writing this article.
In regards to the first part of your write-up, as a developer, if you want a quick and easy way to test the query from your terminal window (i.e. Cache Terminal, PowerTerm, Putty, Reflections, etc...), then you can run the query as follows:
Do ##class(%ResultSet).RunQuery({className},{queryName}) - if you have input parameters, then you would pass those values as subscripts 3 - n;
Example of running query with two input parameters:
Do ##class(%ResultSet).RunQuery({className},{queryName},{inputValue1},{inputValue2})
So an example of running the first query in your write-up from a terminal window might be:
Do ##class(%ResultSet).RunQuery("Sample.Person","ByName","A")
The output from the query might be:
ID:Name:DOB:SSN:
53:Adam,Ralph O.:41730:657-43-6149:
33:Adam,Zoe X.:56117:982-36-6928:
80:Ahmed,Edgar Q.:33719:546-61-2688:
110:Alton,Umberto B.:30116:877-79-1057:
146:Avery,Valery P.:39515:310-11-8847:
Hope this Helps and Have a Great Day!!! Happy Coding/Testing those queries!
Quick followup note - so folks don't freak out about the output from the query example - the names and birthdates and SSN's displayed in the example were from a SAMPLES testing environment where data was auto-generated at random - so the data is fake and not for real people :)
Thanks!
I often need to run queries from a terminal, so I extended Caché ObjectScript with zsql command to run queries and display the results. Here's how it works:
zsql "SELECT TOP 2 Name FROM Sample.Person"
Would output:
To achieve it I created %ZLANGC00 mac routine with the following code:
; %ZLANGC00 ; custom commands for ObjectScript ; http://docs.intersystems.com/cache20141/csp/docbook/DocBook.UI.Page.cls?KEY=GSTU_customize Quit /// Execute Query and display the results /// Call like this: /// zsql "SELECT TOP 10 Name FROM Sample.Person" ZSQL(Query) #Dim ResultSet As %SQL.StatementResult Set ResultSet = ##class(%SQL.Statement).%ExecDirect(, Query) Do ResultSet.%Display() Quit
Save and compile it and then you can execute sql (class queries too) in a terminal with zsql command:
That said I myself prefer executing SQL queries in SMP because you don't need to type them there (drag&drop from the left panel or copy&paste from the code) - it's very convenient.
I typically use the SQL shell in the terminal when doing this. It is very powerful.
SAMPLES>do $system.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
The command prefix is currently set to: <<nothing>>.
Enter q to quit, ? for help.
SAMPLES>>?
Interactive SQL Shell
(c) InterSystems Corporation 1999-2009 All Rights Reserved
----------------------------------------------------
To execute an SQL statement, type it in and press ENTER.
To execute a multiline SQL statement, press <enter> to enter
multiline statement mode, enter the each statement line and
enter GO to exit multiline statement mode and execute the statement.
etc...
This uses %SQL.Statement to do all the work and has a number of options including saving of queries, etc.
I would also recommend to try Caché Web Terminal SQL mode:
This mode appeared with 4.0 version.
for me personally, the greatest benefit of custom SQL queries is that you don't need any (persistent) data, you can just generate it on the fly, either computing them or bringing from any arbitrary external system.
Dan
Fantastic post @Eduard Lebedyuk
Alternative approach: %SQL.CustomResultSet is icing on the cake which is not covered by Intersystems documentation. It really should be. This is exactly what I needed for my current project. Thanks.
For 2017.1CE and later as well as all IRIS versions, %SQL.CustomResultSet should not be used. Instead, use %SQL.CustomQuery. There are several good reasons for this. There is good class documentation available. I am happy to post examples if anyone is interested.
Hi @Dan Pasco
It would be great if you can share some examples with us.
Best Regards,
Henrique
First, keep in mind that all implementations (faithful implementations that is) of %SQL.CustomQuery are also projected as table-valued functions. That means you can include the function in the FROM clause of a SELECT statement.
The process of implementing a custom query is simple. These steps are described in the %SQL.CustomQuery class documentation so I'll just summarize here.
I won't post the version of the class that produces this output since the version of %Net.Http in current versions of CE/IRIS do not have a working GetJSON() method. The version of the class I'm posting simply passes in the raw JSON data as an argument.
The query:
SELECT top 5 stateCode,name,population
FROM example_custom.sample_custom_query('https://api.census.gov/data/2014/pep/natstprc?get=STNAME,POP&for=state:*...','Default')
ORDER BY population DESC
and the results:
5 row(s) affected
Using this version of a custom query class:
and this query
SELECT top 5 stateCode,name,population
FROM example_custom.sample_custom_json_query('[["STNAME","POP","DATE_","state"],["Alabama","4849377","7","01"],["Alaska","736732","7","02"],["Arizona","6731484","7","04"],["Arkansas","2966369","7","05"],["California","38802500","7","06"],["Colorado","5355866","7","08"],["Connecticut","3596677","7","09"],["Delaware","935614","7","10"],["District of Columbia","658893","7","11"],["Florida","19893297","7","12"],["Georgia","10097343","7","13"],["Hawaii","1419561","7","15"],["Idaho","1634464","7","16"],["Illinois","12880580","7","17"],["Indiana","6596855","7","18"],["Iowa","3107126","7","19"],["Kansas","2904021","7","20"],["Kentucky","4413457","7","21"],["Louisiana","4649676","7","22"],["Maine","1330089","7","23"],["Maryland","5976407","7","24"],["Massachusetts","6745408","7","25"],["Michigan","9909877","7","26"],["Minnesota","5457173","7","27"],["Mississippi","2994079","7","28"],["Missouri","6063589","7","29"],["Montana","1023579","7","30"],["Nebraska","1881503","7","31"],["Nevada","2839099","7","32"],["New Hampshire","1326813","7","33"],["New Jersey","8938175","7","34"],["New Mexico","2085572","7","35"],["New York","19746227","7","36"],["North Carolina","9943964","7","37"],["North Dakota","739482","7","38"],["Ohio","11594163","7","39"],["Oklahoma","3878051","7","40"],["Oregon","3970239","7","41"],["Pennsylvania","12787209","7","42"],["Rhode Island","1055173","7","44"],["South Carolina","4832482","7","45"],["South Dakota","853175","7","46"],["Tennessee","6549352","7","47"],["Texas","26956958","7","48"],["Utah","2942902","7","49"],["Vermont","626562","7","50"],["Virginia","8326289","7","51"],["Washington","7061530","7","53"],["West Virginia","1850326","7","54"],["Wisconsin","5757564","7","55"],["Wyoming","584153","7","56"],["Puerto Rico Commonwealth","3548397","7","72"]]')
ORDER BY population DESC
produces the same result:
5 row(s) affected
I am happy to post other examples if you wish.
Dan
Custom queries can also be instantiated without using SQL. Simply call %New and pass in the arguments that are defined by the %OpenCursor method. There is one difference here - the first argument of %New is the SELECTMODE and subsequent arguments correspond to the %OpenCursor arguments. Once instantiated, the interface is like any other %SQL.IResultSet.
Another unknown feature that isn't mentioned anywhere else.

I am a fan Robert...but...
https://community.intersystems.com/post/new-video-sql-%E2%80%93-things-y...
Somewhere around the 36 minute mark of this video. Buried perhaps. Still, %SQL.CustomQuery has a lot of interesting capabilities.
Incredible! I really missed something important!
@Dan Pasco Thanks for the pointer. I never stop learning.
This is a great feature Dan!
I'm using it to write SQL queries to Pandas dataframes in Python (code).
💡 The article is considered as InterSystems Data Platform Best Practice.
Some of the reasons why I focus on utilizing class queries include
Class Queries are really worth investing in IMHO.