How could we get columns' data inside %Library.ProcedureContext and %SQL.ClassQueryResultSet ?‽?

Primary tabs

SQL, Caché, Ensemble

Hello,

 

I have created my first table in Ensemble:

 

 

And I have added a row using the Production:

 

 

We would like to query it, and we would expect to get a row wih all the previous' image data.

 

I have tried, inside a code block in a Bussiness Process:

  set statement=##class(%SQL.Statement).%New()
 
  set status = statement.%PrepareClassQuery("TablasBBDD.NotificacionesPUSH.RelacionAplicacionNotificacion","seleccionarTodo")
 
  set result = statement.%Execute()
  do result.%Display()
 
  $$$LOGINFO("filas result: "_result.%ROWCOUNT)
 
  $$$LOGINFO("result: "_result)
 
  //$$$LOGINFO("clave: "_result.%Get("clave"))
 
  $$$LOGINFO("resultado actual: "_result.%CurrentResult)
 
  $$$LOGINFO("resultado siguiente: "_result.%Next())

   

So then we observe the following in a trace:

We have one row:

The class name is:

How could we get the current result? Because of as loginfo from the following instruction: $$$LOGINFO("resultado actual: "_result.%CurrentResult)
 we get:

 

And there is not next:

 

The question is:

 

Considering we have just executed the following query:

Query seleccionarTodo() As %SQLQuery [ SqlProc ]
{
SELECT FROM TablasBBDD_NotificacionesPUSH.RelacionAplicacionNotificacion
}

 

How could be get the result's columns' data???

I tried to use:

  //$$$LOGINFO("clave: "_result.%Get("clave"))
 

However it does not work, because it makes bussiness process crash.

I have read the following:

 

 

Replies

What happens if you remove "do result.%Display()"?

I suspect that %Display() is iterating through the result set, so by the time it reaches "$$$LOGINFO("resultado siguiente: "_result.%Next())" it is already on the last row.

Hello Marc Mundt, you are right:

I have tried to comment the sentence: "do result.%Display()", so then the code would be:

However $$$LOGINFO("resultado actual: "_result.%CurrentResult) prints an empty result:

And as you said, do result.%Display(), was iterating over the result:


We now have the following code:

set statement=##class(%SQL.Statement).%New()
 
  set status = statement.%PrepareClassQuery("TablasBBDD.NotificacionesPUSH.RelacionAplicacionNotificacion","seleccionarTodo")
 
  set result = statement.%Execute()
  //do result.%Display()
 
  $$$LOGINFO("filas result: "_result.%ROWCOUNT)
 
  $$$LOGINFO("result: "_result)
 
  //$$$LOGINFO("clave: "_result.%Get("clave"))
 
  $$$LOGINFO("resultado actual: "_result.%CurrentResult)
 
  $$$LOGINFO("result number of columns: "_result.%ResultColumnCount)
 
  //$$$LOGINFO("result dumped: "_result.DumpResults())
 
  //$$$LOGINFO("resultado siguiente: "_result.%Next())
 
  set i = 0
  while (result.%Next() '= 0){
   set i=i+1
   $$$LOGINFO("i: "_i)
   $$$LOGINFO("Inside loop, current result: "_result.%CurrentResult)
   set printed = result.%CurrentResult.%Print()
   $$$LOGINFO("current result print: "_printed)
   //$$$LOGINFO("current result getData(0): "_result.%CurrentResult.%GetData(0))
   //$$$LOGINFO("current result column(0): "_result.%CurrentResult.Column(0))
  }

And when we check the trace, we see, there are 4 columns:

And we iterate once, so there is one row:

Being current result a SQL.ClassQueryResultSet:

However, why when we call:

set printed = result.%CurrentResult.%Print()

$$$LOGINFO("current result print: "_printed)

It shows:

How could we get, from result.%CurrentResult which is a %SQL.ClassQueryResultSet, the row's data? I mean, how could we print result's row's data?‽???

 

 

I have read:

- https://cedocs.intersystems.com/latest/csp/documatic/%25CSP.Documatic.cl...

You can get columns like this:

 $$$LOGINFO("current result print: " _ result.clave)

Instead of

set printed = result.%CurrentResult.%Print()

$$$LOGINFO("current result print: "_printed)

printed value is 1 because %Print outputs data into the current device and returns %Status.