?
Solved

The use of MySQL cursor

Posted on 2012-09-13
10
Medium Priority
?
580 Views
Last Modified: 2012-09-17
it seems that MySQL complain that we can't define CURSOR within a Store procedure. we have to define it before other coding of the SP.

Are we allowed to declare CURSOR after other code in MySQL SP?

DELIMITER @@
DROP  PROCEDURE apDffFront@@
CREATE PROCEDURE apDffFront (
    param1 INT
    )
BEGIN
    DECLARE v_service_ref_id VARCHAR(255);
    DECLARE done INT DEFAULT FALSE;
    DECLARE a VARCHAR(255);

    SELECT ServiceRefID INTO v_service_ref_id FROM tblService WHERE InventoryID = param1; ¿ does not work as the cursor declaration is below
    
    DECLARE cur1 CURSOR FOR 
        SELECT Parent FROM tblInventory WHERE ID IN (
            SELECT InventoryID FROM tblService WHERE 
                ServiceRefID = v_service_ref_id AND 
                ServiceStatusID = "ASSIGNED" AND 
                InventoryID <> v_service_ref_id);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    SELECT ServiceRefID INTO v_service_ref_id FROM tblService WHERE InventoryID = param1; ¿ this will work but I need to pass the variable to the cursor
    
    OPEN cur1;
    FETCH cur1 INTO a;
    CLOSE cur1;    
END
@@

DELIMITER ;

Open in new window

0
Comment
Question by:marrowyung
  • 6
  • 4
10 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 38394112
From http://dev.mysql.com/doc/refman/5.5/en/declare.html:

"DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements."
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38394145
for any thing like Dynamic SQL or SP that wait for result from other source, which we can't declare something in advance, how can we solve this ?
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38394158
MySQL seems very bad on this and our developer very frautrate on this !
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 24

Expert Comment

by:johanntagle
ID: 38394460
google mysql dynamic sql and prepared statements.  Sorry can't help much right now, very busy.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38404395
Then in this case, we can't group store procedure in MySQL, like the MS SQL and Oracle do, rihgt?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38404410
I don't know what you mean by "group store procedure" - do you mean packages?  If that's what you mean, no MySQL doesn't support that yet.

Did you find out about dynamic sql in Mysql stored procs yet?  See
http://www.it-iss.com/mysql/mysql-writing-dynamic-sql-in-stored-procedures/
http://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38404545
"I don't know what you mean by "group store procedure" - do you mean packages?  If that's what you mean, no MySQL doesn't support that yet."

for example, if we have many SP, then for performance tunning point of view we need to COMBINE many SP into one SP, how can we do it if we need to use dynamic SQL? which we don't know the answer until it execute.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38404610
can MySQL enterprise get ride of this ? or we need to change to MSSQL or Oracle ?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38404658
I don't think MySQL, whether community or enterprise, supports it yet.  So if you really want to use dynamically created cursors you need to change your database.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38408039
Thanks, by this statement is seems that even the community or enterprise edition, the deadlock handling is the same? no detection can be done in DB side but only application side?
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 14 hours left to enroll

840 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