Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    28

    Unanswered: Stored Procedures OUT argument

    I have a procedure that looks like this:

    CREATE PROCEDURE DAY0_PROC (OUT v_ts TIMESTAMP)
    LANGUAGE SQL
    P1: BEGIN
    ..
    --v_ts receives some value here
    END P1

    I have a unix shell script that calls it like this:
    db2 "CALL DAY0_PROC(?)";

    After this call, in the unix script, I want to capture the OUT value in a variable and echo it (and later write it to a file..). The code as it is, displays a long show of:
    Value of output parameters
    --------------------------
    Parameter Name : V_TS
    Parameter Value : 2004-07-14-16.21.26.715864
    Return Status = 0

    I have tried passing '$variable' and 'variable' in the call - doesnt work. What should I be doing?? Somehow, IBM documentation describes at length the use with C/Java etc. but not with Unix....

    Thanks for any help.

  2. #2
    Join Date
    Mar 2004
    Posts
    448
    you can do some thing like that

    db2 call <proc_name>\(?\)|grep "Parameter Value"|cut -d ' ' -f 6 > test_in
    read test < test_in
    echo $test


    put that in an script

    regards,

    mujeeb

  3. #3
    Join Date
    Jun 2004
    Posts
    28
    Thanks, Mujeeb.

  4. #4
    Join Date
    Jun 2004
    Posts
    28
    On the same note, I am having trouble doing INOUT with Timestamps from Unix. I have a stored proc like this:
    CREATE PROCEDURE CONT_PROC (INOUT v_ts TIMESTAMP)

    If I make a call like this from a unix script:
    db2 "CALL CONT_PROC('2004-05-06 09:02:21.163751')";
    the call works fine (apparently, I still cant actually use OUT in its capacity, I am doing the grep way suggested in this thread). But the argument in the CALL needs to be a variable. So the script looks something like this:

    read date1 < prop.txt;
    db2 "CALL CONT_PROC($date1)";

    Surely, this is wrong and I get this error:
    SQL0104N An unexpected token "09" was found following "_PROC(2004-05-06". Expected tokens may include: "+". SQLSTATE=42601
    SQL3022N An SQL error "-204" occurred while processing the SELECT string in
    the Action String parameter.

    How do I send a Timestamp type variable into the CALL from the UNIX script??

    Thanks for any help.

  5. #5
    Join Date
    Jun 2004
    Posts
    28
    Never mind, I got it. I still have to grep to get the OUT part of it, though.
    Last edited by saltbits; 07-15-04 at 16:42.

Posting Permissions

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