Linked Stored Procedure Call - Need Results to populate further logic
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.
Comments
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.
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"
}