MySQL Forums
Forum List  »  Stored Procedures

Want to Avoid Cursor
Posted by: Shawn Hamzee
Date: January 30, 2006 12:41PM

I am trying to write an sp that has a cursor in it. This sp is complex:
the sp itself is written in one database; however it accesses multiple other databases and this needs to be done dynamically, which means that the definition of the cursor has to become dynamic. ok, so I have this now as my sp definition:


CREATE PROCEDURE `Home_kids` (IN homeroom varchar(4), IN school varchar(30),
OUT lname varchar(10), OUT fname varchar(10), OUT d_bdate date, OUT program_code varchar(10), OUT d_previous_school varchar(10), OUT hr varchar(10), OUT d_date date, OUT d_engagement varchar(10), OUT d_fluency varchar(10), OUT d_accuracy varchar(10), OUT d_comprehension varchar(10),OUT d_overall varchar(10), OUT d_fiction varchar(10), OUT d_stage varchar(10), OUT d_cycle varchar(10))

LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY INVOKER

BEGIN
DECLARE program_code varchar(30) DEFAULT 'None';
DECLARE d_previous_school char(4);
DECLARE hr, lname, fname, d_engagement, d_fluency, d_accuracy, d_comprehension, d_overall,d_fiction, d_stage, d_cycle varchar(10);
DECLARE d_date, d_bdate date;
DECLARE not_found BOOLEAN DEFAULT false;
DECLARE scho varchar(10);


DECLARE cur_1 CURSOR FOR
select last_name,
given_name,
birth_date,
program,
scho.name,
division,
dra_date,
dra_engagement,
dra_fluency,
dra_accuracy,
dra_comprehension,
dra_overall,
dra_fiction,
dra_stage,
dra_cycle
from school.stud
left join dublin_dra on id=student_id
left join scho on previous_school = scho.school_id

where division like concat(hr,'%') order by division, last_name, birth_date;

DECLARE CONTINUE HANDLER FOR NOT FOUND
set not_found = TRUE;

OPEN cur_1;
REPEAT
FETCH cur_1 into
lname,
fname,
d_bdate,
program_code,
d_previous_school,
hr,
d_date,
d_engagement,
d_fluency,
d_accuracy,
d_comprehension,
d_overall,
d_fiction,
d_stage,
d_cycle;

UNTIL not_found = TRUE

END REPEAT;
CLOSE cur_1;



this compiles fine if I change the school in the from clause of the cursor definition and replace it with a real database name; however, i really can't do that because the selection of the db is dynamic and in the hands of the user not the application.

i believe prepared statements do not work in cursors. can anyone suggest a solution to this problem?


thanks much,
shawn

Options: ReplyQuote


Subject
Views
Written By
Posted
Want to Avoid Cursor
2745
January 30, 2006 12:41PM
1758
January 30, 2006 06:24PM
1520
January 31, 2006 08:46AM
1697
January 31, 2006 10:12AM
1381
January 31, 2006 11:07AM
1440
January 31, 2006 02:28PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.