Construct clone of %execute result of %SQL.Statement class
Hi Team,
Is there any way to take a clone object of executed query result of %SQL.Statement
Set statement= ##Class(%SQL.Statement).%New()
Set pre = statement.%Prepare("Select * From TableName")
Set result = = SQl.%Execute()
Write result.%ConstructClone(,.cc) ; ConstrcutClone was overridden in the class %SQL.StatementResultI can loop result.%Next() multiple times if clone was taken from the object
Product version: IRIS 2023.1
Discussion (3)1
Comments
Maybe you should take a look at the class %ScrollableResultSet?
Thanks Vitaliy for the suggestion, %ScrollableResultSet is works for the Cache SQL. We are actually fetching multiple Resultsets and not working as expected for language=tsql.
ClassMethod tt() [ Language = tsql, SqlName = mycls, SqlProc ]
{
SELECT Name FROM sample_SQL.NewClass4
SELECT Name, Age FROM sample_SQL.NewClass5
}
set results=##class(%ScrollableResultSet).%New()
set tsc = results.Prepare("call sample_SQL.MYCLS()")
if $$$ISERR(tsc) W $SYSTEM.OBJ.DisplayError(tsc)
do results.Execute()
ERROR #6048: Invalid Statement Type: 'CALL'1In this case, it will be easier to temporarily dump the data into globals. Then you can access this data at any time, including through %ScrollableResultSet.
Here is a small example:
ClassMethod tt() [ Language = tsql, ReturnResultsets, SqlName = mycls, SqlProc ]
{
drop table if exists mytemp1,mytemp2
select name,dob,spouse into mytemp1 from Sample.Person where name like 'A%'
select name,age,home_city,home_state into mytemp2 from Sample.Person where home_state = 'MA'
}
ClassMethod Test()
{
d ##class(%SQL.Statement).%ExecDirect(,"call dc.mycls()")
d ##class(%SQL.Statement).%ExecDirect(,"select * from mytemp1").%Display()
d ##class(%SQL.Statement).%ExecDirect(,"select * from mytemp2").%Display()
; and even so
d ##class(%SQL.Statement).%ExecDirect(,"select * from mytemp1 cross join mytemp2").%Display()
}