Boosting Queries with Custom Class Queries

Who has never been through in the following situation:

I have an application/global configuration that is not and cannot be mapped to a class, but it is necessary to provide a stored procedure so that a report tool using ODBC/JDBC or even using the resultset of IRIS itself, can access the data and generate the report.

In IRIS we have a functionality that allows us to create a query that can be accessed internally and also be exposed as a stored procedure, with our own logic. That functionality is Custom Class Query.

To define a Custom Class Query we must follow these steps (which we can make an analogy to implementing an interface):

1. Create the signature of the Custom Class  Query with the definition of the parameters and the specification of the columns to be returned;
2. Implement the nameQueryExecute class method;
3. Implement the nameQueryFetch class method;
4. Implement the nameQueryClose method of the class.

For our example let's create a hypothetical global with a node and 4 pieces. The node will be auto-incremented and the pieces will be alphanumeric. Below is the code to generate our example global with 1000 lines.

PopularGlobal() Public
{
	#Dim contadorLinha As %Integer = 0
	For contadorLinha = 1 : 1 : 1000
	{
		#Dim linha			As %String	= ""
		#Dim contatorColuna	As %Integer	= 0
		For contatorColuna = 1 : 1 : 4
		{
			Set linha = $Get(linha) _ "^" _  $Replace($Replace("coluna Y linha X", "Y", contatorColuna), "X", contadorLinha)
		}
		Set ^CustomQueryTest(contadorLinha) = $Piece(linha, "^", 2, *)
	}
}
ObjectScript
ObjectScript

Custom Class Query Signature

Query ListarDados() As %Query(ROWSPEC = "ID:%Integer,Coluna1:%String,Coluna2:%String,Coluna3:%String,Coluna4:%String") [ SqlProc ]
{
}
ObjectScript
ObjectScript

The body must be left blank. The return type must be %Query and not %SQLQuery which is used with SQL commands. The ROWSPEC parameter contains the metadata of the row that will be returned in ColumnName:DataType format.

ListarDadosExecute Method

ClassMethod ListarDadosExecute(ByRef qHandle As %Binary) As %Status
{
  // Set the global data
  Set qHandle		= "^CustomQueryTest"
  // Set last accessed node
  Set qHandle(1)	= ""
  //
  Return $System.Status.OK()
}
ObjectScript
ObjectScript

In this method, we must execute the initialization of our Custom Class Query, we can make calls to the internal code, open external connections, etc. The qHandle parameter is the pointer to the "cursor".

ListarDadosExecute Method

ClassMethod ListarDadosFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = ListarDadosExecute ]
{
  #Dim statusCode	As %Status	= $System.Status.OK()
  // Get next node
  #Dim indice		As %Integer	= $Order(@qHandle@(qHandle(1)))
  // If there is no more data, set a flag indicating that the data is over and return
  If (indice = "")
  {
    Set AtEnd	= 1
    Set Row		= ""
    //
    Return statusCode
  }
  // Get the data
  #Dim linha As %String = $Get(@qHandle@(indice))
  // Set return line
  Set Row			= $ListBuild(indice, $Piece(linha, "^", 1), $Piece(linha, "^", 2), $Piece(linha, "^", 2), $Piece(linha, "^", 2))
  // Last node accessed
  Set qHandle(1)	= indice
  //
  Return statusCode
}
ObjectScript
ObjectScript

This is the main method which will contain all the logic for assembling, transforming, adapting the data to be returned. When using ResultSet for example, this method is called every time the %Next method is executed.

• The qHandle parameter is the pointer to the "cursor" that was initialized in the ListarDadosExecute method;
• The Row parameter is the reference to the line containing the data;
• The AtEnd parameter indicates whether or not there is more data to be returned.

ListarDadosClose Method

ClassMethod ListarDadosClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = ListarDadosExecute ]
{
  Kill qHandle
  //
  Return $System.Status.OK()
}
ObjectScript
ObjectScript

In this method, we release all resources used, such as: close connections, clear temporary globals, release open files, etc.

Below is the complete definition of the class with all methods

Class cjs.concurso.CustomQuery
{

Query ListarDados() As %Query(ROWSPEC = "ID:%Integer,Coluna1:%String,Coluna2:%String,Coluna3:%String,Coluna4:%String") [ SqlProc ]
{
}

ClassMethod ListarDadosExecute(ByRef qHandle As %Binary) As %Status
{
	// Define a global que contém os dados
	Set qHandle		= "^CustomQueryTest"
	// Define o último nó acessado
	Set qHandle(1)	= ""
	//
	Return $System.Status.OK()
}

ClassMethod ListarDadosClose(ByRef qHandle As %Binary) As %Status [ PlaceAfter = ListarDadosExecute ]
{
	// Limpa o handle
	Kill qHandle
	//
	Return $System.Status.OK()
}

ClassMethod ListarDadosFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ PlaceAfter = ListarDadosExecute ]
{
	#Dim statusCode	As %Status	= $System.Status.OK()
	// Recupera o próximo nó a ser acessado
	#Dim indice		As %Integer	= $Order(@qHandle@(qHandle(1)))
	// Caso não tenha mais dados set aflag indicando que os dados acabaram e retorna
	If (indice = "")
	{
		Set AtEnd	= 1
		Set Row		= ""
		//
		Return statusCode
	}
	// Recupera os dados da global
	#Dim linha As %String = $Get(@qHandle@(indice))
	// Define a linha a ser retorna
	Set Row			= $ListBuild(indice, $Piece(linha, "^", 1), $Piece(linha, "^", 2), $Piece(linha, "^", 2), $Piece(linha, "^", 2))
	// Definie último nó acessado
	Set qHandle(1)	= indice
	//
	Return statusCode
}

}
ObjectScript
ObjectScript

Example of execution using JDBC connection with DBeaver

Custom Class Query is an extremely powerful tool, we can use it in several other situations such as:

• Queries with extremely complex logic;
• Access to external data sources such as files, calls to REST services, SOAP, etc.;
• Specific security requirements;
• Data mushup;
• Between others.
• Any questions or suggestions please do not hesitate to interact.

For more information, see the Defining Custom Class Queries documentation

Don't forget to vote in the original post. :)