Written by

Senior Software Engineer

Question Ashok Kumar T · 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

Add reply

Comments

Vitaliy Serdtsev · Aug 11, 2023

Maybe you should take a look at the class %ScrollableResultSet?

0 0

Ashok Kumar T Aug 11, 2023 to Vitaliy Serdtsev

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 to Ashok Kumar T

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:

<FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">tt() [ </FONT><FONT COLOR="#000080">Language </FONT><FONT COLOR="#000000">= tsql, </FONT><FONT COLOR="#000080">ReturnResultsets</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">SqlName </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008000">mycls</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#000080">SqlProc </FONT><FONT COLOR="#000000">]
{
  </FONT><FONT COLOR="#0000ff">drop table </FONT><FONT COLOR="#000080">if exists </FONT><FONT COLOR="#008000">mytemp1</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">mytemp2
  </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008000">name</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">dob</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">spouse </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">mytemp1 </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">Sample</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Person </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">name </FONT><FONT COLOR="#000000">like </FONT><FONT COLOR="#008080">'A%'
  </FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008000">name</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">age</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">home_city</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">home_state </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">mytemp2 </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">Sample</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">Person </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">home_state </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#008080">'MA'
</FONT><FONT COLOR="#000000">}</FONT>
<FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Test()
{
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"call dc.mycls()"</FONT><FONT COLOR="#000000">)
  
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"select  from mytemp1"</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%Display</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"select  from mytemp2"</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%Display</FONT><FONT COLOR="#000000">()
  </FONT><FONT COLOR="#008000">; and even so
  </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"select * from mytemp1 cross join mytemp2"</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%Display</FONT><FONT COLOR="#000000">()
}</FONT>

0 0