Calling a SQL Server stored procedure via ODBC

Question

Question

Ewan Whyte · Jul 8, 2020

#HL7 #ODBC #SQL #Ensemble #InterSystems IRIS

Hi,

I'm trying to ultimately create a function that I can use with HL7 messages that calls a stored procedure held on a SQL Server.

Initially I tried extending the function class to use the sql inbound adapter and/or EnsLib.SQL.Common, but this wouldn't work from the class method for the function.

Then I attempted to use the linked stored procedure using the wizard where it connected fine and I could find the sp but but it wouldn't return any data when ran. The test sp should just return an int.

The class document produced is this.

I suppose my question is, is it even possible to call a sp on a SQL Server to use in a function? If so, then any pointers or examples would be really appreciated.

Thanks.

Discussion (5)1

Log in or sign up to continue

Halim Chouchane · Jul 8, 2020

Hi Ewan,

Yes, it's possible to call a SP from an sql server, we use it a lot and it works fine.
Yes, it returns an Integer but usually that's not the result returned by the SP

the result is in %sqlcontext, I use it like this :

ClassMethod GetApptsSP(URNO As %String = "3123456") As %Status
{

// calling the SP
set status = ##class(Hospital.SP.tpGetAppts).tpGetAppts(URNO)

s rs=%sqlcontext.NextResult() // getting the result
While rs.%Next() {

s dateTime = rs.%Get("DueTimestamp")
s ApptDate = $P(rs.%Get("DueTimestamp")," ",1)
....

}

q $$$OK

}

hope this help

0 0

score 0 · Answer 1 · 2020-07-08T09:10:05-04:00

This is absolutely possible. First, you'll want to use a SQL Business Operation rather than a Business Service. Your function will send a simple request message to the Business Operation containing any parameters you want to pass to the SP (if you only have one parameter you can use Ens.StringContainer). The Operation will execute the SP on SQL Server and return a response message to your function.

You didn't mention where you plan to call this function from (a DTL? a Business Process?). If it's a Business Process then you don't need a custom function, you can just use a "call" action to send the request to the Operation.

If you need to do this from a DTL, then in order to send a message to the Operation you'll need to call a method of the business process or router. You can get a reference to the process/router this way:

set bp=%Ensemble("%Process")

And then you can send your request message like this:

set tSC=bp.SendRequestSync("My.SQLServer.Business.Operation",req,.resp)

score 0 · Answer 2 · 2020-07-09T09:56:46-04:00

Thank you for your help, I'm now getting results back from my stored procedure. I did have one problem though, when the wizard created the ClassMethod it wrapped the stored procedure name in double quotes and as a result HS couldn't find it when connecting to the server. Once removed the method, worked like a treat.

Thanks again.

score 0 · Answer 3 · 2020-07-09T18:54:04-04:00

Yes, I had the same issue.

Some sql ODBC adapters accept the double quotes, some not, it's safer to remove them.

All the best.

score 0 · Answer 4 · 2020-10-07T04:46:59-04:00

Hi Halim,

I've got a custom function that calls a SP and it appears to work now and again.
It appears that the function appears to be caching previous results? The only way I've been able to get it to work with the correct result is either by sending the same message multiple times or restarting the router which isn't ideal. Have you come across this before?

Thanks