Linked Stored Procedure Call - Need Results to populate further logic

Question

Question

Scott Roth · Apr 22

#ObjectScript #SQL #HealthShare #InterSystems IRIS for Health #Health Connect #InterSystems IRIS

I was wondering if someone could help me. In the past I have been able to call external Stored Procedures through a SQL Outbound Connection and have them return me the EnsLib.SQL.Snapshot to use within a BPL to extract data.

But this time instead of using a SQL Outbound BO to make the Stored Procedure call, I decided to create a Linked Stored Procedure through the %JDBC_Server to point to the Stored Procedure out on MS SQL.

However, I am struggling to get the code just right to return the Column value from the Linked Stored Procedure.

 set result = ##class(EnsLib.SQL.Snapshot).%New()
 set result = $SYSTEM.SQL.Execute("CALL osuwmc_Utils_EnterpriseDirDb.InterfaceCheckConnectMedCtrID('$Get(MedCtrID)')")
 while result.%Next(){
  set Loc = result.Get("OSUguestRoleDTL")
 }

Does not return me the value I am looking for because the rest of my code fails.

However, if execute CALL osuwmc_Utils_EnterpriseDirDb.InterfaceCheckConnectMedCtrID('<value>') from the SQL Viewer it will return a Result. If I do a do result.%Display() it shows me the result, so what am I missing? This is the first time I have tried to call a Linked Stored Procedure from within Object Script.

I need to get the value that is being returned within "OSUguestRoleDTL" to help me determine how to route the message and populate addresses within the HL7 message.

Product version: IRIS 2024.1

$ZV: IRIS for UNIX (Red Hat Enterprise Linux 8 for x86-64) 2024.1 (Build 267_2U) Tue Apr 30 2024 16:06:39 EDT [HealthConnect:7.2.0-1.r1]

Discussion (2)1

Log in or sign up to continue

Scott Roth · Apr 22

I was able to get it to work. I found that in Cache I need to call %Get("<column>") name vs what I am doing in a BPL.

 s Loc = ""
 s tStatement = ##class(%SQL.Statement).%New()
 s execall= "CALL osuwmc_Utils_EnterpriseDirDb.InterfaceCheckConnectMedCtrID(?)"
 s qStatus = tStatement.%Prepare(execall)
 if $$$ISERR(qStatus) {write "%Prepare failed:" do $SYSTEM.Status.DisplayError(qStatus) quit}
 set rset = tStatement.%Execute(pInput)
 while rset.%Next() {
 set Loc = rset.%Get("OSUguestRoleDTL")
 }
 if $Length(Loc) = 0 {
    set Loc = "OSUWMC"
 }

0 0

score 0 · Answer 1 · 2025-04-22T10:41:13-04:00

Try:

 set result = $SYSTEM.SQL.Execute("CALL osuwmc_Utils_EnterpriseDirDb.InterfaceCheckConnectMedCtrID('$Get(MedCtrID)')")
 set rs = result.%NextResult()
 while rs.%Next(){
  set Loc = rs.Get("OSUguestRoleDTL")
 }

Maybe you'll need to call %NextResult() twice.