Solved

DB2 Stored Procedure Output Value Command Prompt

Posted on 2007-11-16
9
6,029 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 14

Author Comment

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

Thanks
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 5

Accepted Solution

by:
ocgstyles earned 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Hi,
I am giving points to ocgstyles for his efforts, though this doesn't quite resolve the query.

Thanks anyways ocgstyles
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

772 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now