Error when executing statement in DB2 Stored Procedure

hsrock · #1 (**permalink**) 12-21-05, 03:06

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. :

Marcus_A · #2 (**permalink**) 12-21-05, 08:53

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

n_i · #3 (**permalink**) 12-21-05, 10:58

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;

ggnanaraj · #4 (**permalink**) 12-22-05, 01:42

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.

n_i · #5 (**permalink**) 12-22-05, 07:02

Right, my bad. You need to PREPARE your cursor, not EXECUTE it.:

Code:

PREPARE rs_Row FROM strSql;
OPEN rs_Row USING m_MA;

ggnanaraj · #6 (**permalink**) 12-22-05, 08:42

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

umayer · #7 (**permalink**) 12-23-05, 07:00

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

Internet Services

Web Development

Digital Marketing

Consumer Tech