INvoking a store procedure from a routine

[gpbarsky]

Hi forumers......

Is there a way to invoke a store procedure from within a routine ?

Actually, I'm invoking a server job, which executes the store procedure and returns me a file with the found values.

What I wish, is to invoke the store procedure from within the routine, and receive the values from the store procedure in the routine.

Any suggestion will be appreciated.

Thanks.

[Sainath.Srinivasan]

Why from routine?

You can call it from a job using stored procedure stage.

[gpbarsky]

I'll try to explain my case.

I have a tabla with two columns:
PARAMETER, CHAR(50)
VALUE, CHAR(50)

Some rows of this table may be:
PARAMETER VALUE
-----------------
CONN01 SQL01
USER1 US01
PWD1 PWD01

So within a job I need to access this table to get some connection parameters to be used in the logic of the job.

My solution is to have a routine (GETPARAMETERVALUE) accessing this table. But, for security reasons, I have not
privilege to read the table, and the user running the DS jobs also has not the privileges to read the table.

These privileges are embedded in the store procedure I have to invoke. The store procedure receives a parameter
(by example CONN01), and must return the value SQL01.

The only way I found to do this is to invoke a server job with a ODBC stage to invoke the store procedure (SQL). The
store procedure stage does not has the SQL database. This job creates a seq file, with the found value. Then, in the routine,
I read the only record the file has, and then I delete the file. The result is returned in the Ans field of the routine.

Was I clear ?

[ray.wurlod]

So it seems you have a working solution.

The only way to call a stored procedure from a server routine is to use BCI functions, and to execute an appropriate SQL statement with them (for example CALL or EXEC).

[gpbarsky]

What is a BCI function ?? I couldn't see the complete answwer.

[chulett]

Basic Call Interface. Search for it here, there's been a ton of discussions on and examples of its syntax posted. Only "problem" might be the fact that the reigning champion on the subject is Ray.