Question
Andy Stobirski · May 28

Why doesn't this %Library.ResultSet ClassName/QueryName doesn't work?

Hi Guys,

So I've been following this guide in using a %Library.ResultSet with a  ClassName / QueryName as described in the first example.

https://docs.intersystems.com/irislatest/csp/documatic/%25CSP.Documatic....

The code I've got so far doesn't work and is as follows:

set rs=##class(%ResultSet).%New()
set rs.ClassName="GMECC.DocmanConnect.Tables.vwNewGPs"
set rs.QueryName="GetRows"
set sc=rs.Execute("a")  If $$$ISERR(sc) Do DisplayError^%apiOBJ(sc) Quit
while rs.%Next() { do rs.%Print() }		

And the referenced class is

Class GMECC.DocmanConnect.Tables.vwNewGPs [ ClassType = view, CompileAfter = (GMECC.DocmanConnect.Tables.Endpoints, GMECC.DocmanConnect.Tables.PARIS.AUDUAGUMECCPRAC), DdlAllowed,  Not ProcedureBlock, SqlTableName = vwNewGPs, ViewQuery = { 

	// Bunch of SQLCode
   	
 	} ]
{

Query GetRows() As %SQLQuery
{
	select *
	from GMECC_DocmanConnect_Tables.vwNewGPs
}

}

When code when run, produces the follow error:

ERROR #5002: ObjectScript error: <PARAMETER>zGetRowsExecute^GMECC.DocmanConnect.Tables.vwNewGPs.1

What am I doing wrong, can anyone offer any insight?

Product version: IRIS 2021.2
$ZV: IRIS for Windows (x86-64) 2021.2.1 (Build 654U) Fri Mar 18 2022 06:09:35 EDT
0
0 212
Discussion (5)2
Log in or sign up to continue
set sc=rs.Execute("a")

You pass a paramter,
but there is no need for a parameter visible.  NO ? in query

try

set sc=rs.Execute()

You can also call it like this:
 

set rs = ##class(GMECC.DocmanConnect.Tables.vwNewGPs).GetRowsFunc()
while rs.%Next() { do rs.%Print() }

More info about implicit methods.

Set QueryClass = "GMECC.DocmanConnect.Tables.vwNewGPs"
Set QueryName = "GetRows"
Set tResult = ##class(%Library.ResultSet).%New(QueryClass_":"_QueryName)
Set tResult.RuntimeMode = 1
Set:Params="" status = tResult.Execute()
Set:Params'="" status = tResult.Execute(Params)

Also look at the %SQL way of running queries which has some advantages, there are a few ways of doing it, one of which is:

s stmnt=##class(%SQL.Statement).%New()

s sc=stmnt.%PrepareClassQuery("GMECC.DocmanConnect.Tables.vwNewGPs","GetRows")

s rs=stmnt.%Execute()

w rs.%SQLCODE,!

while rs.%Next() {

// s var=rs.%Get("ColumnName")  - for known column names

s var1=rs.%GetData(columnNumber)

w var,!

w var1

}

%Library.ResultSet remains in the product for backward compatibility reasons but there are better ways to execute class queries. Any class query can be projected as a table valued function (TVF). TVF's can be executed if the class query also declares SQLPROC. A TVF can be included in the FROM item list, it can be joined with other FROM items, it can be ordered, restricted and a subset of available columns made available. Here is a simple example from the Sample.Person class:

select id,name,dob from sample.SP_Sample_By_Name('Ad') order by dob desc

I populated Sample.Person with some generated data and ran the above statement:

 
ID Name DOB
855 Adams,Elvira X. 03/16/2021
1378 Adams,Ed L. 01/15/2018
477 Adams,Debra S. 10/01/2015
1341 Adam,Chad U. 10/20/2013
32 Adam,Dmitry N. 10/28/2010
1099 Adams,Pam Z. 10/20/1993
897 Adam,Joe Y. 02/23/1984
1469 Adam,Phyllis N. 04/20/1982
358 Adam,Liza H. 12/13/1980
1096 Adam,Belinda Z. 08/02/1975
1269 Adam,Charlotte P. 03/03/1974
1396 Adams,Robert E. 03/14/1973
1109 Adams,Quigley H. 01/01/1968
454 Adam,Amanda A. 01/22/1964
856 Adams,Lawrence A. 03/23/1961
1104 Adam,Stavros O. 02/24/1948
1179 Adam,Pam A. 05/16/1941
426 Adams,Brian M. 01/15/1928

18 row(s) affected

And you can also execute this using a dynamic statement:

USER>set result = $system.SQL.Execute("select id,name,dob from sample.SP_Sample_By_Name('Ad') order by dob desc")

USER>write result.%Next()
1
USER>write result.Name
Adams,Elvira X.