New post
Question
· Sep 27, 2019

How can i go through the full result once?

#SQL #Caché

When i use  ##class(%SQL.Statement).%New() -> .%Execute() and then  .%Next() to go through the result set, i could only go through part of the result. And then, i have to call  .%NextResult() to go through the next part of the result. BUT, the  .%Display() could show all of the result.

How can i go through the full result once,like .%Display()? Or how should i do to go through the full result? Is there anyone can help me? Waittingcrying!!!

Discussion (2)0
Log in or sign up to continue
Nigel Salm · Sep 29, 2019

I'm glad you resolved your issue. For the record there is another approach you could use and that is the %Library.ResultSet (or %ResultSet) class.

You can use this class to run Class Queries or Dynamic SQL

Here is an example from the Class Documentation with some liberal editing from myself

	Set result=##class(%ResultSet).%New("%DynamicQuery:SQL")
	Set sc=result.Prepare("SELECT %ID, Name, Salary FROM Sample.Employee WHERE Salary > ?")
	If $$$ISERR(sc) Do $system.Status.DisplayError(sc) Quit
        // Get the first 10000 rows
	Set sc=result.Execute(10000) If $$$ISERR(sc) Do $system.Status.DisplayError(sc) Quit
        // To get all rows call the Execute Method with no parameter
	While result.Next(.sc) {
		If $$$ISERR(sc) Quit
		Write result.Data("Name"),result.Data("Salary"),!
	}
	If $$$ISERR(sc) Do $system.Status.DisplayError(sc) Quit
0 0