Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DB2 Stored Procedure Output Value Command Prompt

Posted on 2007-11-16
9
Medium Priority
?
6,757 Views
Last Modified: 2008-06-16
Hi experts,

This should be the simple question.

I am calling db2 stored procedure having output parameter from the command prompt.

How to display (ECHO) the output parameter value of the stored procedure at the command prompt?

Thanks
0
Comment
Question by:ajexpert
  • 4
  • 4
9 Comments
 
LVL 5

Expert Comment

by:ocgstyles
ID: 20302537
Hi ajexpert,

Here's an example (using Linux):

$ db2 -td@ "create procedure test(
in p_in1 int,
in p_in2 int,
out p_out int
)
language sql
begin
 set p_out = p_in1 + p_in2;
end@"

DB20000I  The SQL command completed successfully.

$ db2 "call test(5,6,?)"

  Value of output parameters
  --------------------------
  Parameter Name  : P_OUT
  Parameter Value : 11

  Return Status = 0

Every output parameter when you call the procedure should have a "?" as a place holder.

As far as putting a line in the procedure, using the above as an example:

echo p_out;

... won't work.

Hope that helps.

Keith
0
 
LVL 14

Author Comment

by:ajexpert
ID: 20304767
Hi Keith,
Thanks for example but I have tried the same.
I dont need 'Value of out put parameters' as its there by default
I am executing on Windows and thru command prompt I need something like
ECHO %P_OUT%
This should give me only the value and not other information.
I hope my requirement is clear now.
Let me know If I need to elborate more.

I am ready to increase the points if the solution is great :)
Thanks,

ajexpert
0
 
LVL 5

Expert Comment

by:ocgstyles
ID: 20305044
Hi ajexpert,

I figured that's wechat you were looking to do, but unfortunately DB2 doesn't support it.  The only way I can think to get the value is to pipe the output of the stored procedure into a script/executable to extract the value you want.

Keith
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 14

Author Comment

by:ajexpert
ID: 20307785
Hi Keith,
It would be good if you can explain the same with code example.

Thanks
0
 
LVL 5

Accepted Solution

by:
ocgstyles earned 150 total points
ID: 20308910
Hi ajexpert,

I can give an example, but I'm not sure it will help, since you are working in a Windows environment.  But in case you are familiar with both DOS batch scripting and Unix shell scripting, maybe you can use my shell script as an example to create a DOS batch.

Create a executable file named "get_param_value" that contains:

/usr/bin/awk -v P=$1 '
        /Parameter Name/ { PARAM=$4 }
        /Parameter Value/ { VAL=$4 }
        /^$/ { if( PARAM == P ) print VAL }'

What this awk script does is look for a parameter that you pass into this script.  When the awk script finds the variable, it finds the value on the next line, then when it encounters the next blank line, it outputs the value if we found the param name.

So, if you use the stored procedure I put in my first post, you can do this:

$ db2 "call test(5,6,?) | ./get_param_value P_OUT

In this case, it would print just the value 11.

Sorry again that this is Unix, but I'm not sure how to do a Windows equivalent.

Keith
0
 
LVL 5

Expert Comment

by:ocgstyles
ID: 20309404
Hi,

There's a slight oversight in the above example, it doesn't account for string parameters (values with spaces), so here's a modified version... if anyone else find's value with it...

/usr/bin/awk -F":" -v P=$1 '
        function ltrim(s){
                sub(/^ */, "", s);
                return s;
        }

        function rtrim(s){
                sub(/ *$/, "", s);
                return s;
        }

        function trim(s){
                return ltrim(rtrim(s));
        }

        /Parameter Name/ { PARAM=$2 }
        /Parameter Value/ { VAL=$2 }
        /^$/ { if( trim(PARAM) == P ) print trim(VAL) }'


0
 
LVL 14

Author Comment

by:ajexpert
ID: 20312996
Thanks for your efforts but I need to execute on Windows Environment as of now.

Is there any way to accomplish this in Windows Environment?
0
 

Expert Comment

by:nagarjuna009
ID: 20675351
Hi ,

 thank you for providing above script. Actually i have more than on OUT parameters. when i tried to change code accordingly it is giving an error. can u provide me the code for getting multiple OUT parameters. as  i am new to shell script pls do this help.. thanks in advance..
0
 
LVL 14

Author Comment

by:ajexpert
ID: 20905346
Hi,
I am giving points to ocgstyles for his efforts, though this doesn't quite resolve the query.

Thanks anyways ocgstyles
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month9 days, 10 hours left to enroll

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question