Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2009
    Posts
    3

    Unanswered: error calling Stored procedure

    Hello!
    Excuse me for my English!

    I create a stored procedure on a DB2 database.

    Code:
    CREATE PROCEDURE SCHEMAPROVA."prova"
      (OUT SQLCODE_OUT INTEGER,
        OUT SQLSTATE_OUT CHARACTER(5))
      SPECIFIC SCHEMAPROVA."prova"
      READS SQL DATA
      NOT DETERMINISTIC
      CALLED ON NULL INPUT
      LANGUAGE SQL
    
      P1: BEGIN
            DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
            DECLARE SQLCODE INT DEFAULT 0;
    
            DECLARE CAMPO1(16);
            DECLARE CAMPO2(40);
    
            DECLARE cursor1 CURSOR WITH RETURN FOR
                SELECT 
                SCHEMAPROVA.TABELLACAMPO2,
                SCHEMAPROVA. TABELLA.CAMPO2, 
                FROM SCHEMAPROVA.TABELLA
    
              DECLARE EXIT HANDLER FOR SQLEXCEPTION
    
              SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
              FROM SYSIBM.SYSDUMMY1;
    
              OPEN cursor1;
              FETCH cursor1 INTO CAMPO1, CAMPO2;
              CLOSE cursor1;
    
              SET SQLCODE_OUT = SQLCODE;
              SET SQLSTATE_OUT = SQLSTATE;
    
    END P1;

    but if I execute CALL SCHEMAPROVA."prova" ()
    I get
    Code:
    An exception occurred
    Message: [IBM][CLI Driver][DB2/NT] SQL0444N  La routine "EQNRS.prova" (nome specifico "SQL091022182100200")  stata implementata con codice nella libreria o percorso "\EQNRS.prova", funzione "EQNRS.prova" ai quali non  possibile accedere.  Codice di errore: "4".  SQLSTATE=42724
    SQL: CALL EQNRS."prova"()
    Method: TDB2DirQuery.Execute
    If I wrote a procedure with IN parameters or without parameters, it's works.
    With OUT parameters doesn't work!!!

    Please, can you help me?

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Try: CALL SCHEMAPROVA."prova" (?,?)

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Also, I don't think it is a good idea to make the procedure name case sensitive.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by n_i
    Also, I don't think it is a good idea to make the procedure name case sensitive.
    How many lashes do you get for doing that?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Oct 2009
    Posts
    3
    case sensitive is not a problem: the same procedure works without parameters or with only IN parameters.

    with CALL SCHEMAPROVA."prova" (?,?) I get an other error of IN, OUT, INOUT parameters...

    Code:
    An exception occurred
    Message: [IBM][CLI Driver] CLI0100E  Numero errato di parametri. SQLSTATE=07001
    SQL: CALL "SCHEMAPROVA"."prova"( ?,?)
    Method: TDB2DirQuery.Execute
    Last edited by Microchip; 10-27-09 at 06:21.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How are you trying to call the Stored Procedure? If you are using the CLP then the method I gave you should work. If you are doing it from an application, then you need to see how to implement OUT parameters in whatever you are using.

    Andy

  7. #7
    Join Date
    Oct 2009
    Posts
    3
    I tried using DB2 Maestro.

    I tried to test stored procedure using IBM RSA and... it works!!!

Posting Permissions

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