0
198

Written by

Kris Roberts
Follow
Question Kris Roberts · Apr 1, 2024

InterSystems Reports - Logi Report (23.2) - How to code a stored procedure that will return a result set to be used in a report

#InterSystems IRIS #InterSystems Reports (Logi)

A report that I need to create - I need to write a class method that will execute a few queries and save the data to a class and then return the data in a result set that can be used in the Logi report as a Stored procedure.

I can create the class method that is a Stored procedure and do the queries and manipulate the data, however I am having an issue at the end of the method to return the fields back as a result set.

I have looked at the Intersystems' documentation for the stored procedures but not finding a good example for a class method that is a stored procedure which returns a result set (multiple rows and 8 columns)

If you have any examples or suggestions please let me know.

Thanks.

Product version: IRIS 2023.1
$ZV: IRIS for Windows (x86-64) 2023.1.2 (Build 450_0_22960U) Mon Dec 18 2023 18:47:09 EST
Discussion (0)0
Log in or sign up to continue

Comments

Scott Roth · Apr 3, 2024

I have many integrations using JDBC stored procedure calls against MS SQL. 

  • Define Stored Procedure Class Structure that extends Extends (%Library.Persistent, %XML.Adaptor) [ Not ProcedureBlock, SqlRowIdPrivate ] for any Parameters (Properties) that need to be passed to the stored procedure
Class osuwmc.CPD.DataStructures.CheckProviderSpecialty Extends (%Library.Persistent, %XML.Adaptor) [ Not ProcedureBlock, SqlRowIdPrivate ]

{



Property DoctorNumber As%String(MAXLEN = 6);



Storage Default

{

<Data name="CheckProviderSpecialtyDefaultData">

<Value name="1">

<Value>%%CLASSNAME</Value>

</Value>

<Value name="2">

<Value>DoctorNumber</Value>

</Value>

</Data>

<DataLocation>^osuwmc.CPD59D.CheckProvideAF3D</DataLocation>

<DefaultData>CheckProviderSpecialtyDefaultData</DefaultData>

<IdLocation>^osuwmc.CPD59D.CheckProvideAF3D</IdLocation>

<IndexLocation>^osuwmc.CPD59D.CheckProvideAF3I</IndexLocation>

<StreamLocation>^osuwmc.CPD59D.CheckProvideAF3S</StreamLocation>

<Type>%Storage.Persistent</Type>

}
  • Using a Custom Operation that uses the EnsLib.SQL.Outbound adapter, and a XData Message Map, I create Methods that use the Stored Procedure class structure defined and return EnsLib.SQL.Snapshot.
Include (EnsSQLTypes, %occODBC)



Class osuwmc.Epic.MFN.EpicMFNToCPDDBWriteDEV Extends Ens.BusinessOperation [ ClassType = "", ProcedureBlock ]

{



Parameter ADAPTER = "EnsLib.SQL.OutboundAdapter";Parameter INVOCATION = "Queue";Property InitDSN As%String;



Method OnInit() As%Status

{

    Set..InitDSN = ..Adapter.DSN

    Kill$$$EnsRuntimeAppData(..%ConfigName)

    //Set ..Adapter.ConnectAttrs = "QueryTimeout:45" ; try this too just in case...

    Quit$$$OK

}
  • I call the execution of the store procedure using ..Adapter.ExecuteProcedureParmArray
Method CheckDoesProviderExists(pRequest As osuwmc.CPD.DataStructures.CheckDoesDoctorNumberExist, Output pResponse As EnsLib.SQL.Snapshot) As%Status

{

  set SPQuery = "{ ?= call InterfaceCheckDoctorNumber(?) }"

  set parm = 2

  set parm(1,"SqlType")=$$$SQLVARCHAR

  set parm(1,"IOTypes")=$$$SQLPARAMOUTPUT



  set parm(2)=pRequest.DoctorNumber

  set parm(2,"SqlType")=$$$SQLVARCHAR

  set parm(2,"IOTypes")=$$$SQLPARAMINPUT



  set tSC = ..Adapter.ExecuteProcedureParmArray(.CheckDoctor,.outputs,SPQuery,"oi",.parm)

  if tSC = 1

  {

    set pResponse = CheckDoctor.GetAt(1)

  }

  quit tSC

}

Let me know if you need additional help, but this should give you a good start.

0