New post
Question
· Aug 10, 2023

Construct clone of %execute result of %SQL.Statement class

#InterSystems IRIS #InterSystems IRIS for Health

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

I can loop result.%Next() multiple times if clone was taken from the object

Product version: IRIS 2023.1
Discussion (3)1
Log in or sign up to continue
Ashok Kumar · Aug 11, 2023

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'1
0 0
Vitaliy Serdtsev · Aug 14, 2023

In 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()
}
0 0