New post
Question
· Nov 5, 2021

Iterate over an SQL result set

#SQL #InterSystems IRIS

I running a query and get the results inside a result set. Now I have to iterate through the result set many times. From the doc I've seen only the Next() method. Is there a way to reset the cursor? Otherwise what is a good data structure to save multiple rows of a table?

My code in this case is something like this:

   set sql = "SELECT * FROM MY_TABLE WHERE X= '"_Y_"'"
   set status = ..Adapter.ExecuteQuery(.rs, sql)
    // somehow iterate the rs more than one time

Product version: IRIS 2021.1
Discussion (6)1
Log in or sign up to continue
David Hockenbroch · Nov 5, 2021

Your result set should be an EnsLib.SQL.GatewayResultSet, which has a method called GetSnapshot(). That method has you pass a EnsLib.SQL.Snapshot by reference. You're probably going to want to set the FetchAll parameter on the GetSnapshot() method to 1 so it gets all the results, but you can also create your EnsLib.SQL.Snapshot before using GetSnapshot() and set it's starting row and max rows if you'd like. Then you can iterate over the snapshot instead of the result set. Once you've gone through it once, you could either create a new snapshot by calling the GetSnapshot() method again, or you can use the snapshot's Rewind() method.

1 0
Julian Matthews · Nov 5, 2021

Hey Nicola.

As an alternative to Davids response, you can pass ..Adapter.ExecuteQuery() a snapshot object (EnsLib.SQL.Snapshot) which will then get populated rather than a getting a result set returned. With this populated snapshot object, you can then iterate through it using its Next() method just like the result set, but then you can use its Rewind() method. 

For example:

Set Snapshot = ##Class(EnsLib.SQL.Snapshot).%New()
Set sql = "SELECT * FROM MY_TABLE WHERE X= '"_Y_"'"
Set status = ..Adapter.ExecuteQuery(Snapshot, sql)
While Snapshot.Next(){
  //First run through of snapshot
}
Do Snapshot.Rewind()
While Snapshot.Next(){
  //Second run through of snapshot
}

Something to be mindful of - as I'm passing ..Adapter.ExecuteQuery() an object rather than trying to get it to output something, the period before the variable is intentionally missing in my example.

3 1
Yuji Ohata · Nov 7, 2021

Hi!

I use ScrollableResultSet class in this case.
This class is enable to use Previous() method, and it's able to reset Cursor by using repeatedly.
Like this.

Set query = "YOUR SQL"
Set result = ##class(%ScrollableResultSet).%New("%DynamicQuery:SQL")
Set sts = result.Prepare(query)
If ($$$ISERR(sts)) {
    // ERROR
}

Do result.Execute()

If (result.%SQLCODE < 0) {
    // ERROR
}

// Show get column data.
While (result.%Next()) {
  Wrtie result.%GetData(1)
}

// Reset cursor index.
While (result.%Previous() > 0) {
  // Do nothing.
}

// Show get column data again.
While (result.%Next()) {
  Wrtie result.%GetData(1)
}
1 0