[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Problem in executing the Db2 Stored procedure

Posted on 2011-10-21
6
Medium Priority
?
263 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:rakhy_rakey
  • 3
  • 2
6 Comments
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 400 total points
ID: 37010032
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
 
LVL 37

Assisted Solution

by:momi_sabag
momi_sabag earned 1600 total points
ID: 37010053
try replacing line 15 with


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


does it work now?
0
 

Author Comment

by:rakhy_rakey
ID: 37012009
Hi Kdo...


Thanks for the reply.

I tried your suggestion, but still its not working.

The version is v9.7

Thanks,
Rakhy.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rakhy_rakey
ID: 37012032
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
 
LVL 37

Accepted Solution

by:
momi_sabag earned 1600 total points
ID: 37012091
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
 

Author Comment

by:rakhy_rakey
ID: 37012302
Thank you momi...

It worked for me...
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the pain points with developing AJAX, JavaScript, JQuery, and other client-side behaviors is that JavaScript doesn’t allow for cross domain request for pulling content. For example, JavaScript code on www.johnchapman.name could not pull conte…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month18 days, 1 hour left to enroll

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question