execute stored procedure SQL

[Doug Stelley]

I am in the beginning of a project wher I have to do quite a few SQL lookups per message. I would like to cut my teeth on a simple one and grow from there but I’m having a tough time finding the right documentation.

In my transaltion configurator I have

0(0).PV1(0).#50(0) (Alternate Visit ID) for the source.

That would be my  $xlateInVals.

I need to use that to call a stored procedure.

The specifics are:

called SQL server: snhd_app

user: query

pword: read_only

stored procedure: Find_MRN_from_VisId

in VBS I would call it with: “exec Find_MRN_from_VisId 380720”

with 380720 being the variable.

Will somebody right me a tcl to do that? I believe I can then use that to grow from.

In this case there will only be one value returned. I will need to learn how to handle multiple values soon. arrays?

Thanks in advance!

Topic

[Jim Kosloskey]

Doug,

I will describe the way we would do that.

First we utilize the Data Manager (ODBC Drivers) add-on for Cloverleaf.

Then we would invoke a Stored Procedure as you are doing. Our intent would be to have one reusable Tcl Procedure to do be used wih all invocations of query/rply Stored Procedurs. So the specifics of each invocation would be externalized from the Tcl proc. In our case that would be in a lookup table.

We would eploy parameters for the exchange. That is there would be parameter INPUT to the Stored Procedure and Parameters (probably only two) OUTPUT from the Store Procedure.

One of the OUPUT parameters would be a subfielded parameter containing a return code to indoicate success/failure of the Stored Procedure – a non-zero return code would be the actual error code detected by The Stored Procedure – the second subfield would be the literal associated with the code returned in the first sunfield. This allows us to detect an error has occurred; take whaever actin is warranted; and externalize the fact that a error occurred via email, pages, Global Monitor, etc.

The other OUPUT Parameter would be the response to the query. Our thoughts are that this could be virtually any structure of data (XML. HL7, VRL, FRL, HRL) but it would be the responsibility of the Stored Proicedur to construct the format. We then would use Cloverleaf parsing capabilities to manage that returned message.

One note about Sql Server when invoking a Stored Procedure and using parameters. The OUTPUT Parameters are stuck behind a recordset even if the Stored Procedure did not create a recordset. So your Tcl code will need to exhaust the recordset (won’t take but one action with our model because our model does not have the Stored Procedure return a recordset). Then the OUTPUT Parameters are populated. This is not true with Oracle DBs.

Our model attempts to fit the Services Architecture model that is why would exchange mesage structures as parameters rather than recordsets which have no known standard.

Anyway that is our way of looking at it…

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Doug Stelley]

I must be way overthinking this. In this particuler interface the volume will be low so I dont think I’ll need to worry about a performance hit.

If I understand you. You suggest creating a generic TCL Proc and invoke it in the thread?

I would like need at least 2, one for expected single value returns  and one for multiples.

I am not understanding the table part though. Can you email me with a bit more guidance?

Even though I went to Level 1 and 2 a few years ago I have been out of the loop and am too rusty. I got some relearning to do! I purchased 30 hours of technical support from lawson for this type of thing but this should be pretty strait forward. I understand your line of thinking and I am very willing to follow the model as it will give more stability but looking at examples I have found, I dont have enough yet to go on!

[Author]

Replies