Hi, can someone please clearify how to call a procedure from command window. I am facing few problem--

firstly one of my parameter itself contain comma (') as part of value, is there something like excape characted here. for example a value to be passed is satya's.

secondly if i call a procedure i get some output like here-

db2 => call ABCX.USER_ACT_COMM('READ','2003','I')
SQL0206N "I" is not valid in the context where it is used. SQLSTATE=42703

i dont understand what is the error here..


Thanks for your help
Satyendra

Satyendra,
Can you post the definition of the Stored Procedure (name and parameters is all that is necessary)? THis will help me to give you a valid answer.

Andy

Andy this is my stored procedure

USR_ACT_COMM (IN busCode VARCHAR(20),
IN inpYear VARCHAR(20),
IN userCode VARCHAR(10)

I want to call this stored procedure where in want to pass 'I','E' (including all quotes and comma ) as value for third parameter userCode, basically i am usiing that value in a IN clause in a select query so i have to pas aformatted string for that.

hope i put things clear.

Thanks
Satyendra

Try:
CALL USR_ACT_COMM('Read','2003','I'',''E')

Note the '' are two single quotes.

HTH

Andy

Hi Andy,

The procs is getting called but the value does not seem to be passed correctly.

this is what i get--

db2 => CALL ABCX.USER_ACT_COMM('Read','2003','I'',''E')
SQL0104N An unexpected token "','" was found following "a.USAGE_TYPE IN (I". E
xpected tokens may include: ",". SQLSTATE=42601


basically the third parameter i am passing is supposed to just sit a IN clause.

example-

select...

where..
.
.
and a.USAGE_TYPE IN ( *** here**)
.
.


And this is working fine when beeing called from java code.I need to run that from command window for testing purpose.


thanks
Satyendra

Satyendra,
I am confused. What is the select statement you just presented have to do with calling a stored procedure. Could you be totally explicit what you want?

Andy

Hi Andy, sorry for delay, i was on off for few days.

You need not to considere that select statement, that i used just to clearify that i am using that value in one of select statement within the procedure body...so you know within the body of procedure there is a select statement where there is a IN clause which values are supplied as third parameter to the procedure, so typically the value to be passed should be like 'I','E'.

Satyendra,

What DB2 version and OS are you using?

Andy

Hi, its DB2 7.1 and OS is sun solaris.

Satyendra,

Have you tried using the Stored Procedure Builder (SPB) to debug the SP to see if the parameters are being passed properly?

Andy

Andy,

This procedure is running fine, both through java code and also running fine when i call it through stored procedure builder where i write my parameters in the prompt window. My problem is i want to call it from command window, the reason being i dont have stored porocedure builder istalled on clients's production environment.

so i need the way how i can call it through command window on db2 prompt.

I hope you understand what is my need.

doesn't that look strange we do not have any mean to pass parameters that itself contain comma (,) and single quote (') as part of parameter value from command prompt when it runs fine from procedure builder.

Thanks for your efforts and time in discussing this problem.

Satyendra,
What is the exact value of the parameter you want to pass. I want to see what it should look like inside the SP.

Andy

Hi,

The value that should reach within SP is 'I','E','N'

all ' and , are part of value.


Thanks
Satyendra

Satyendra,

Sorry I did not reply sooner, I was out of the office for a few days.

Try:
CALL USR_ACT_COMM('Read','2003','''I'',''E'',''N''')

The ''' are 3 single quotes and the '' are 2.

HTH

Andy

Andy,

Yeah, it works now.
I can see the SP returning results as expected on comand window.

Thank you very much
Satyendra