Problem in executing the Db2 Stored procedure

Hi Guys...

I am able to execute the stored proc successfully db2 if I write normally. Normally in the sense that if I give select statement directly after


DECLARE DB2_SP_SQL1 CURSOR WITH RETURN FOR

But here my requirement is;

I want to assign my stored proc to a variable and then I want to do concatenation for that string according the requirement and later convert this entire string to prepared statement and then execute.

Thats what I tried to do in attached code.

But its not succeeded. I am not getting any data.

I tried the same attached code just by directly giving the select statement. Its worked properly.

Any help is greatly appreciated guys...

Thanks in advance....

Rakhy.
CREATE PROCEDURE STOREDPROC1(IN "DEPTID" CHAR(5))
				SPECIFIC strproc1
				LANGUAGE SQL
				DYNAMIC RESULT SETS 1


P1: BEGIN
-- Declare variables
DECLARE Q1 VARCHAR(1000);
DECLARE Q2 VARCHAR(1000);  


DECLARE DB2_SP_SQL1 CURSOR WITH RETURN FOR Q2;

SET Q1 = 'SELECT DEPTNAME, DEPTNUM, EMPLCOUNT FROM DEPT WHERE DEPTID = "DEPTID"';



PREPARE Q2 FROM Q1;


OPEN DB2_SP_SQL1;
	
-- Cursor left open for client application.

--
END P1

@
-- Grant access privileges to procedure
GRANT EXECUTE ON  SPECIFIC PROCEDURE STOREDPROC1 TO PUBLIC
@

Open in new window

rakhy_rakeyAsked:
Who is Participating?
 
momi_sabagConnect With a Mentor Commented:
you can either use simple string concatenation:

SET Q1 = 'SELECT DEPTNAME, DEPTNUM, EMPLCOUNT FROM DEPT WHERE DEPTID = '''|| DEPTID || '''';
(note that those are not double quotes but rather 3 single quotes to the left and 4 to the right)

or a better solution which is not exposed to sql injection:

SET Q1 = 'SELECT DEPTNAME, DEPTNUM, EMPLCOUNT FROM DEPT WHERE DEPTID = ?';
and then
OPEN DB2_SP_SQL1 USING DEPTNO;
0
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:
Hi rakhy,

You didn't say which version of DB2 you're using (Z/OS, AS/400, or LUW), but since you're trying to tie it to a web application, it's probably LUW.

I have never seen DB2 programmed this way.  Even so, if you prepare a statement, you execute the statement with the EXECUTE statement.  You're explicitly opening the cursor that you're trying to prepare.

Try this -- replace line 22 with:

  EXECUTE Q2;


Kent
0
 
momi_sabagConnect With a Mentor Commented:
try replacing line 15 with


SET Q1 = 'SELECT DEPTNAME, DEPTNUM, EMPLCOUNT FROM DEPT;


does it work now?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rakhy_rakeyAuthor Commented:
Hi Kdo...


Thanks for the reply.

I tried your suggestion, but still its not working.

The version is v9.7

Thanks,
Rakhy.
0
 
rakhy_rakeyAuthor Commented:
Hi momi_sabag....Thanks for the reply....

I tried with your statement. It ran peoprly. So I observed that; I am facing the problem of passing the variable at run time.

So how to give DeptID as run time parameter ?

Thanks,
Rakhy.

0
 
rakhy_rakeyAuthor Commented:
Thank you momi...

It worked for me...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.