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

Question

Question

Andy Stobirski · May 28, 2022

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

Discussion (5)2

Log in or sign up to continue

Robert Cemper · May 28, 2022

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()

0 0

Chris Eslinger · May 31, 2022

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)

0 0

Dan Pasco · Jun 1, 2022

%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.

0 0

score 0 · Answer 1 · 2022-05-29T06:02:07-04:00

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.

score 0 · Answer 2 · 2022-06-01T02:22:44-04:00

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

}