Stored Procedure

[suziequeue]

It seems odd that there is no mechanism to return values from output parameters in stored procedures. What are folks using in place of this as a design?

 

[janhes]

It's odd you should think this. I use storred procedures a lot to return data either in input cards as part of a query or with DBLOOKUP

 

[eyetry]

We use stored procedures to return data as well. How are you configuring the rule, mdq etc... ?

 

[suziequeue]

Am quoting from the product's Help section on output cards: " The types of the parameters may be IN, OUT, or IN/OUT. However, there is no mechanism to return values from output parameters. Any values passed to an OUT parameter will be ignored." It should instead be used as an input card for it to work(?)

 

[eyetry]

Janhes could probably speak more effectively on this but I'll give it a try anyway....

We can return a single value within an element/field of an output card. We cannot return an array of data to an output card unless the card is a textblob. (We haven't gone beyond that when working with SPs as outpu.)

We do, however, execute a runmap that uses the SP response as an input. The parent map passes the required variables. The response populates the input card. The run map then builds output accordingly. We've used the same process for other maps but echoed the output back to the main map where its is used to process other items.

Hope that helps.

What version are you using?

 

[suziequeue]

We are using version 7.5.1. Your post helps. I'll try it in that manner. Thanks.

 

[Mel162]

Is there anyone that would be willing to post a good example of a call in an output card to a SP using multiple inputs and returning a value? We have been struggling with this for a while now.

 

[eyetry]

Keep in mind that there are all sorts of ways to do this. The sample below is based on something we did with Mercator 6.0 and PL/SQL. It's still in use today.

map1: output card-1 rule...

run("map2" " -ID1 '-VAR F_ID=''" + FILE_ID_NO + "'' -VAR F_CONFIG=''" + CONFIG_NO + "'' -VAR M_SUB=''" + MEMBER_ID + "'''")

map2: input card-1....

source: DB
DBQueryFile: path\file.mdg
Database: DB Name defined in MDQ file
Query: Query/Call in the MDQ file

DBIDB MDQ File
Define DB Name
Point to SID
Enter proper ID/PW
Define call as a query...

call procedure_name(#f_id#,#f_config#,#m_sub#,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

Each "?" represents data returned by SP. We generated the tree from the procedure and then had to modify it. Below is an example of what the returned data looed like...
[field1]|[field2a|field2b|field2c]|[field3a|field3b|field3c]|[field4a|field4b|field4c]|[...]....

 

[Mel162]

The last post was of great assistance. You mentioned having to modiify the tree that was generated from the SP. In what way did you need to modify it?

 

[OllyC]

I would use the GET function to call the stored proc as eyetry describes it, then use the WORD function to pull out the returned fields I need.

 

[eyetry]

We had problems with the MDQ tree generator. Don't recall the details but the data returned didn't sync with the generated tree. Had to do with the occurs I think. Been years now.

That said the trees always seem to generate fine for standard SQL and I recently wrote a query invoking a ScalarType and that tree generated fine too.

Let us know if you have problems...