Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2003
    Posts
    10

    Unanswered: calling aprocedure

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  3. #3
    Join Date
    Nov 2003
    Posts
    10
    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

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try:
    CALL USR_ACT_COMM('Read','2003','I'',''E')

    Note the '' are two single quotes.

    HTH

    Andy

    Originally posted by satya_adk
    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

  5. #5
    Join Date
    Nov 2003
    Posts
    10
    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

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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



    Originally posted by satya_adk
    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

  7. #7
    Join Date
    Nov 2003
    Posts
    10
    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'.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Satyendra,

    What DB2 version and OS are you using?

    Andy

  9. #9
    Join Date
    Nov 2003
    Posts
    10
    Hi, its DB2 7.1 and OS is sun solaris.

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Satyendra,

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

    Andy

  11. #11
    Join Date
    Nov 2003
    Posts
    10
    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.

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

    Originally posted by satya_adk
    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.

  13. #13
    Join Date
    Nov 2003
    Posts
    10
    Hi,

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

    all ' and , are part of value.


    Thanks
    Satyendra

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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


    Originally posted by satya_adk
    Hi,

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

    all ' and , are part of value.


    Thanks
    Satyendra

  15. #15
    Join Date
    Nov 2003
    Posts
    10
    Andy,

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

    Thank you very much
    Satyendra

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •