Written by

Question just fig · Sep 27, 2019

How can i go through the full result once?

#Caché #SQL

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? Waitting!!!

Discussion (2)0

Add reply

Comments

just fig · Sep 27, 2019

I solved it. I used Quit incorrectly where i should use Continue.

0 0

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