The Importance of ‘set Row = “”’ in Class Query “Fetch” Method

One of our apps uses a class query to support a ZEN Report and works just fine in that report, producing the expected results every time. We’ve since migrated to InterSystems Reports and noticed that, for a report using the same class query, 100s of extra rows with the same column values appear at its bottom.

We eliminated InterSystems Reports as the source of the problem by recreating the same “extra rows” issue with an Excel spreadsheet calling the same class query as a stored procedure.

What was the issue? When we called the stored procedure from the old ZEN Report or from the SQL Query feature in the Management Portal, we did not see these extra rows.

We compared our class query code with the information provided in @Eduard Lebedyuk's article and found the issue. Class Queries in InterSystems IRIS

Although it properly set the “AtEnd” ByRef argument properly when there were no results left, our class query’s “Fetch” method did not also set the "Row" argument to the empty string.

It looks like “AtEnd” being set is satisfactory for ending the query’s results in certain cases (MP SQL), but ‘set Row = “”’ is needed for all cases, like for requests from JDBC (InterSystems Reports) or ODBC (Excel).

Here is our updated Fetch method:

ClassMethod QueryNameFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) As %Status [ Internal, PlaceAfter = QueryNameExecute ]
{
    If 'qHandle.ResultSet.Next() {
        Set AtEnd = 1
        set Row = "" // <=========== Previously missing line
        Quit $$$OK
    } // Stuff all the result columns into one big $LIST in Row
    set Row = ""
    set ncols = qHandle.ResultSet.%ResultColumnCount
    for i=1:1:ncols {
        set Row = Row _ $lb(qHandle.ResultSet.GetData(i))
    }
    Quit $$$OK
}