Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2010
    Posts
    17

    Question Unanswered: Passing parameters to SP in 'immediate mode'

    Hi
    this must be easy but it is giving me grief - I am trying to test an SP I have just written using Datastudio 2.2.1.0 :

    CREATE OR REPLACE PROCEDURE SP_1
    (
    IN P_GUID VARCHAR(37),
    OUT P_FILE_UID VARCHAR(37),
    OUT P_SERVICE_ID VARCHAR(10),
    OUT P_ERROR VARCHAR(4000)
    )
    ...

    This is supposed to:
    delete the row from the table identified by 'NAME.1', but return the file uid and service id values from the row before it does.
    If there is an error then P_ERROR should be populated.

    I have written the SP and it compiles. Now I want to test it with something like:
    BEGIN
    DECLARE P_FILE_UID VARCHAR(37);
    DECLARE P_SERVICE_ID VARCHAR(10);
    DECLARE P_ERROR VARCHAR(4000);
    CALL SP_1('NAME.1', P_FILE_UID, P_SERVICE_ID, P_ERROR);
    P1: BEGIN ATOMIC
    SELECT P_FILE_UID, P_SERVICE_ID, P_ERROR FROM STATUS_MSG;
    END P1;
    END<<


    ( where << is our statement termination tag)

    this fails to compile with the follwoing error msg:
    An unexpected token "SELECT P_FILE_UID, P_SERVICE_ID, P_ERROR " was found following ")". Expected tokens may include: "
    P1: BEGIN ATOMIC
    ".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.61.65

    I have tried BEGIN ATOMIC instead of begin - but to no avail

    HELP!!!

    Many thanks

    10Pints


    DETAILS of SP:

    CREATE OR REPLACE PROCEDURE SP_1
    (
    IN P_GUID VARCHAR(37),
    OUT P_FILE_UID VARCHAR(37),
    OUT P_SERVICE_ID VARCHAR(10),
    OUT P_ERROR VARCHAR(4000)
    )
    SPECIFIC SP_1
    LANGUAGE SQL
    BEGIN
    DECLARE SQLCODE INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    GET DIAGNOSTICS EXCEPTION 1 P_ERROR = MESSAGE_TEXT;
    SET P_ERROR = SQLCODE;
    END;

    SET P_ERROR = 0;
    SET P_FILE_UID = 0; -- NOT FOUND

    SELECT FILE_UID, SERVICE_ID INTO P_FILE_UID, P_SERVICE_ID FROM TBL_STATUS_MSG WHERE R_GUID = P_GUID;

    IF( P_FILE_UID <> 0)
    THEN
    DELETE FROM STATUS_MSG WHERE GUID = P_GUID;
    ELSE
    SET P_SERVICE_ID = 0;
    END IF;
    END<<

    INSERT INTO STATUS_MSG (FILE_UID, R_GUID, SERVICE_ID) VALUES('FILE_UID.1', 'R.1', 'SERVICE.1')<<
    INSERT INTO IMS_RTCA_STATUS_MSG (FILE_UID, R_GUID, SERVICE_ID) VALUES('FILE_UID.2', 'R.2', 'SERVICE.2')<<
    SELECT * FROM STATUS_MSG<<
    Last edited by 10Pints; 11-14-11 at 11:21. Reason: CORRECTION

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is wrong with using Data Studio to test the Stored Procedure?

    Andy

  3. #3
    Join Date
    Nov 2010
    Posts
    17
    Hi
    right
    I need a way in datastudio to do the call and then display the parameters
    I have just (in desperation) tried this from the DB2CMD window - and that gives me the ouT paramas for free.
    If I do (in a DB CMS window)
    DB2 CALL SP_1('NAME.1', ?,?,?);

    I get
    Value of output parameters
    --------------------------
    Parameter Name : P_FILE_UID
    Parameter Value :

    Parameter Name : P_SERVICE_ID
    Parameter Value :

    Parameter Name : P_ERROR
    Parameter Value :

    Return Status = 0

    But I had been labouring to Get Data studio to do same - must be a way
    any tips?

    T

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I no longer use that version, but it should be just right-click and select "Run". A dialog should come up for the input parameters. When it completes, it should give you the out parameters.

    Andy

  5. #5
    Join Date
    Nov 2010
    Posts
    17
    Hi Andy
    thanks for your help
    I have tried that but still get problem; when I do that I get the initial parameter dialog pop up
    I then populate the one in parameter
    and datastudio goes of and reports the operation succeeded in the output window (as below)...
    and no ouptut params anywhere to be seen.

    (the name in the actual outut is diferent from my example - as I wanted to keep the example as simple as possible)
    e.g.:
    WATTS_T.IMS_SP_DELETE_RTCA_STATUS_MSG - Run started.
    Data returned in result sets is limited to the first 500 rows.
    Data returned in result set columns is limited to the first 500 bytes or characters.
    WATTS_T.IMS_SP_DELETE_RTCA_STATUS_MSG - Calling the stored procedure.
    WATTS_T.IMS_SP_DELETE_RTCA_STATUS_MSG - Run completed.

    WATTS_T.IMS_SP_DELETE_RTCA_STATUS_MSG - Execution Time => 0 ms


    Strange - this ought to be simple especially since the command window does automatically.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There should be a parameter tab right behind where the status is.

    Andy

  7. #7
    Join Date
    Nov 2010
    Posts
    17
    mm attached scrnshot-params.JPG is the dialog i get when I right click the SP in the data soruce explorer window.

    there is a tabbed dialog if I right click as above and chose run settings
    see scrnshot-params 2.JPG
    but that one only has an editor for the post run - so have same problem:
    I guess i need to bae able to do something like
    VALUES(P_FILE_UID, P_SERVICE_ID)
    Attached Thumbnails Attached Thumbnails scrnshot-params.JPG  

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by 10Pints View Post
    WATTS_T.IMS_SP_DELETE_RTCA_STATUS_MSG - Run started.
    Data returned in result sets is limited to the first 500 rows.
    Data returned in result set columns is limited to the first 500 bytes or characters.
    WATTS_T.IMS_SP_DELETE_RTCA_STATUS_MSG - Calling the stored procedure.
    WATTS_T.IMS_SP_DELETE_RTCA_STATUS_MSG - Run completed.

    WATTS_T.IMS_SP_DELETE_RTCA_STATUS_MSG - Execution Time => 0 ms
    Right behind where this comes out, should be a tab with the parameters.

    Andy

  9. #9
    Join Date
    Nov 2010
    Posts
    17
    thanks for help andy - thi s is much appreciated -
    Supposed to have uploaded 2 images then sorry - try the second one now
    This is when i right click SP and select run settings:
    Attached Thumbnails Attached Thumbnails scrnshot-params 2.JPG  

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Forget the dialog. After the SP executes, it gives you the status of what it did and how long. There should be a tab behind that with the parameters used and returned.

    Andy

  11. #11
    Join Date
    Nov 2010
    Posts
    17
    AHHH!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    the light ... it has dawned ... I had "Display results in a single tab" set - so no parameter tab

    I now at last get a parameters tab:
    Name Type Data type Value Value (OUT)
    ------------ ------ --------- ----- -----------
    P_RTCA_GUID INPUT VARCHAR x
    P_FILE_UID OUTPUT VARCHAR *NULL*
    P_SERVICE_ID OUTPUT VARCHAR *NULL*
    P_ERROR OUTPUT VARCHAR *NULL*


    thanks Andy you have most patient - I think I owe you a beer
    are you near stevenage?
    10Pints

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not drink. Give the money to your favorite charity instead.

    Andy

  13. #13
    Join Date
    Nov 2010
    Posts
    17
    You don't drink?
    You must have some vice ...

    thanks again

Posting Permissions

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