Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2005
    Posts
    6

    Unhappy Unanswered: Error when executing statement in DB2 Stored Procedure

    Hi All,
    I have a DB2 stored procedure like this:

    CREATE PROCEDURE TEST.SEARCH_TESTSEARCH( in m_MA VARCHAR(10))
    DYNAMIC RESULT SETS 0
    modifies sql data
    language sql

    P1: BEGIN
    declare strSql varchar(300);
    declare rs_Row cursor with return for strSql;
    set strSql = 'select * from TEST.TESTSEARCH AS TESTSEARCH';
    if(m_MA is not null) then
    set strSql = strSql || ' where TESTSEARCH.MA = m_MA';
    end if;
    EXECUTE strSql;
    open rs_Row;
    END P1

    This stored procedure is built well, but everytime when I run it, I get this error:

    A database manager error occurred.[IBM][CLI Driver][DB2/NT] SQL0518N The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement. SQLSTATE=07003

    Please help me to solve it.
    Thanks and sorry for my poor English.
    Rock. :
    Last edited by hsrock; 12-21-05 at 04:11.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure why you are using dynamic SQL since the only thing that changes is the predicate value. You have one result set, and the proc does not modify any data in the database, so I changed those parms. Try something like this:

    CREATE PROCEDURE TEST.SEARCH_TESTSEARCH( in m_MA VARCHAR(10))
    RESULT SETS 1
    language sql

    P1: BEGIN

    declare rs_Row cursor with return for
    select * from TEST.TESTSEARCH
    where TESTSEARCH.MA = m_MA;

    if m_MA is not null then
    open rs_Row;
    end if;

    END P1
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    On the other hand, if you decide to stick with dynamic SQL please note that you can't use host variables there. Your code should look like this:
    Code:
    if(m_MA is not null) then
    set strSql = strSql || ' where TESTSEARCH.MA = ?';
    end if;
    EXECUTE strSql USING m_MA;

  4. #4
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Quote Originally Posted by n_i
    On the other hand, if you decide to stick with dynamic SQL please note that you can't use host variables there. Your code should look like this:
    Code:
    if(m_MA is not null) then
    set strSql = strSql || ' where TESTSEARCH.MA = ?';
    end if;
    EXECUTE strSql USING m_MA;
    The change suggested by Marcus is working, but getting the following error when implementing your change...
    HTML Code:
    db2 "create table testsearch(ma varchar(10), name varchar(10))"
    db2 "insert into testsearch values('aa', 'bb')"
    db2 "select * from testsearch"
    
    
    MA         NAME
    ---------- ----------
    aa         bb
    
      1 record(s) selected.
    
    
    
    CREATE PROCEDURE SEARCH_TESTSEARCH( in m_MA VARCHAR(10))
    DYNAMIC RESULT SETS 0
    modifies sql data
    language sql
    
    P1: BEGIN
    declare strSql varchar(300);
    declare rs_Row cursor with return for strSql;
    set strSql = 'select * from TESTSEARCH AS TESTSEARCH';
    if(m_MA is not null) then
    set strSql = strSql || ' where TESTSEARCH.MA = ?';
    end if;
    EXECUTE strSql USING m_MA;
    open rs_Row;
    END P1@
    
    $ db2 "call search_testsearch('aa')"
    SQL0518N  The statement named in the EXECUTE statement is not in a prepared state or is a SELECT or VALUES statement.  SQLSTATE=07003
    
    $
    Can you pls check.

    TIA.
    Last edited by ggnanaraj; 12-22-05 at 02:45.

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Right, my bad. You need to PREPARE your cursor, not EXECUTE it.:
    Code:
    PREPARE rs_Row FROM strSql;
    OPEN rs_Row USING m_MA;

  6. #6
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Quote Originally Posted by n_i
    Right, my bad. You need to PREPARE your cursor, not EXECUTE it.:
    Code:
    PREPARE rs_Row FROM strSql;
    OPEN rs_Row USING m_MA;
    Nope, it is not working still...

    HTML Code:
    $ db2 -td@ -vf sp77.txt
    CREATE PROCEDURE SEARCH_TESTSEARCH( in m_MA VARCHAR(10))
    DYNAMIC RESULT SETS 0
    modifies sql data
    language sql
    
    P1: BEGIN
    declare strSql varchar(300);
    declare rs_Row cursor with return for strSql;
    set strSql = 'select * from TESTSEARCH AS TESTSEARCH';
    if(m_MA is not null) then
    set strSql = strSql || ' where TESTSEARCH.MA = ?';
    end if;
    PREPARE rs_Row FROM strSql;
    OPEN rs_Row USING m_MA;
    END P1
    DB20000I  The SQL command completed successfully.
    
    $ db2 "call search_testsearch('aa')"
    SQL0514N  The cursor "CURS3" is not in a prepared state.  SQLSTATE=26501
    
    $ db2 "select * from TESTSEARCH"
    
    MA         NAME
    ---------- ----------
    aa         bb
    
      1 record(s) selected.
    
    $
    TIA

  7. #7
    Join Date
    Dec 2005
    Posts
    273
    I think you mixed up the name of the cursor and the name of the (prepared) statement.

    Try this:
    ...
    P1: BEGIN
    declare strSql varchar(300);
    declare cursor_name cursor with return for rs_Row;
    set strSql = 'select * from TEST.TESTSEARCH AS TESTSEARCH';
    if(m_MA is not null) then
    set strSql = strSql || ' where TESTSEARCH.MA = m_MA';
    end if;
    PREPARE rs_Row FROM strSql ;
    open cursor_name;
    END P1


    attention: You specified DYNAMIC RESULT SETS 0
    which means that no result set will be returned to the calling program.

    if you only read data but don't modify it, then specifying
    READS SQL DATA instead of MODIFIES SQL DATA might be the better choice
    Last edited by umayer; 12-23-05 at 08:03.

Posting Permissions

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