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) Quitwhile 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?
Comments
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 = 1Set: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 descI 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.