Declare Cursor Statement In Stored-Procedure

[Marcus@SQLyog]

Hi,

I'm Italian, so sorry for my english.

Using SQLyog 9.10 free edition on Windows Vista SP1 Home Edition.

I'd try to declare a cursor in a new stored procedure (“Create Stored Procedure” option), as show in follogin code

DELIMITER $$





CREATE DEFINER=`root`@`localhost` PROCEDURE `DBP_SHOW_DB_TABLE_DEPENDENCIES`(sDBTableName VARCHAR(254), sDBSchemataName VARCHAR(254))


BEGIN


/***********************************************************


*


*  Nome:   DBP_SHOW_DB_TABLE_DEPENDENCIES


*


*  Scopo:  Elenca gli oggetti che fanno uso di una tabella


*			in modo diretto.


*  


*  Parametri  


*		sDBTableName


*			Nome della tabella del database per la quale si


*			desidera conoscere le dipendenze	


*		sDBSchemaName


*			Schema nel quale si desidera cercare le 


*			dipendenze della tabella. Qualora non 


*			specificato la ricerca delle dipendenze avviene


*			utilizzando lo schema corrente.


*                                             


*	


*


*  Data di prima stesura:  01-giu-2011


*  Autore:                 Marco PODDA 


*


*  NOTE


*  -   


*


*  Revisioni successive


*  ========================================================


*  Data:           gg-mmm-aaaa


*  Autore:         Nome COGNOME


*  Descrizione:    [Testo libero]


*  ========================================================


*


***********************************************************/ 


DECLARE iContinue 			INT;


DECLARE iNoMoreData 		INT;


DECLARE sRoutineName 		VARCHAR(254);


DECLARE sRoutineType 		VARCHAR(254);


DECLARE sRoutineDefinition	VARCHAR(2000);











SET iContinue = 1;


IF  sDBSchemataName = '' THEN


SET sDBSchemataName = SCHEMA();


END IF;


SELECT CONCAT('sDBTableName=', sDBTableName, ' sDBSchemataName=' ,sDBSchemataName);





-- controllo che lo schema esista


IF NOT EXISTS(SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = sDBSchemataName) THEN


SELECT CONCAT('Schema ', sDBSchemataName, ' does not exists');


SET iContinue = 0;


END IF;


IF iContinue = 1 THEN


-- controllo l'esistenza della tabella


IF NOT EXISTS(SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = sDBSchemataName AND TABLE_NAME = sDBTableName) THEN


SELECT CONCAT('Table ',  sDBTableName, ' does not exists in schema ', sDBSchemataName);


SET iContinue = 0;


END IF;


END IF;


IF iContinue = 1 THEN





-- Elenca le tabella che fanno riferimento alla tabella specificata


SELECT 


TABLE_NAME AS TABLE_LIST


FROM 


information_schema.REFERENTIAL_CONSTRAINTS


WHERE 


CONSTRAINT_SCHEMA = sDBSchemataName 


AND 


REFERENCED_TABLE_NAME = sDBTableName;


-- Elenca tutte le routines (functions e stored-procedures) dove la tabella specificicata risulta usata


DECLARE crsRoutines CURSOR FOR 


SELECT 


R.ROUTINE_NAME, 


R.ROUTINE_TYPE, 


R.ROUTINE_DEFINITION 


FROM 


information_schema.ROUTINES AS R 


WHERE 


R.ROUTINE_SCHEMA = sDBSchemataName;





DECLARE CONTINUE HANDLER FOR NOT FOUND SET iNoMoreData = 1;





SET iNoMoreData = 0;


OPEN crsRoutines;


WHILE iNoMoreData = 0 DO


FETCH crsRoutines INTO sRoutineName, sRoutineType, sRoutineDefinition;


IF iNoMoreData = 0 THEN


SELECT CONCAT('sRoutineName=', sRoutineName, ' sRoutineType=',sRoutineType,' sRoutineDefinition=', sRoutineDefinition);


iNoMoreData = 1;


END IF;


END WHILE;





CLOSE crsRoutines;


END IF;


END$$





DELIMITER ;

I reiceve the following error message:

Code:

Query : CREATE DEFINER=`root`@`localhost` PROCEDURE `DBP_SHOW_DB_TABLE_DEPENDENCIES`(sDBTableName VARCHAR(254), sDBSchemataName VARCHAR(…

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE crsRoutines CURSOR FOR
SELECT
R.ROUTINE_NAME,
R.ROUTINE_TY' at line 70

Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
—————————————————

It seem the same message that MySQL out when we try to use the DELCARE CURSOR statement out of a stored-procedure (the DECLARE CURSOR statement can be used only in a stored-procedure/function code).

Is there a mistake made by myself or is a SQLyob wrong interpretation of the stored-procedure code?

Please ask me for any other useful informations

Thanks in advance for your reply

Regards

[peterlaursen]

SQLyog does not interpret at all. It just sends the CREATE PROCEDURE statement to the server. But there is of course the possibility that SQLyog 'distorts' what you are writing (by not handling DELIMITERS correctly for instance). But it looks mostly like you are writing some illegal construction.

We will check this. But we do not use 'stored programs' much with our own applications (mostly because there is no debugging API for MySQL stored programs and debugging those is almost hopeless with non-trivial examples). In the very first release if IssueBurner (http://issueburner.com/) we did use it but have now written the logic in the application code instead. So it could take a little time for us to spot exactly where 'the chain breaks' here. Simply because we ourselves are not very familiar with 'advanced' MySQL constructions inside 'stored programs'.

[Marcus@SQLyog]

OK Peter,

this evening I'll try to create a stored procedure that start with a cursor declaration only, then I'll add some other code. I hope this strategy allow me to trap the issue(s) or what is wrong in my stored procedure code.

I'll post the results as possible.

Thank you for the support.

Regards

[ashwin]

Yes, try to create a stored procedure that starts with a cursor declaration only. You can look into this: http://bugs.mysql.com/bug.php?id=27823

“DECLARE is allowed only inside a BEGIN … END compound statement and must be at its start, before any other statements.”

[Marcus@SQLyog]

Yes, ahswin

I read the documentation in more deep way.

But the DECLARE CURSOR documentation does not make any reference to the DECLARE statement.

Infact, the DECLARE CURSOR documentation only say that:

12.7.5.1. DECLARE for Cursors

Syntax: DECLARE cursor_name CURSOR FOR select_statement

Description: This statement declares a cursor. Multiple cursors may be declared in a stored program, but each cursor in a given block must have a unique name.

The SELECT statement cannot have an INTO clause.

Local variables should not be declared with the same name as columns referenced by the SELECT statement, for reasons described in Section 12.7.3.4, “Scope and Resolution of Local Variables”.

For information available through SHOW statements, it is possible in many cases to obtain equivalent information by using a cursor with an INFORMATION_SCHEMA table.

While the DECLARE statement well explain as follow

12.7.2. DECLARE Syntax

The DECLARE statement is used to define various items local to a program:

Local variables. See Section 12.7.3, “Variables in Stored Programs”.

Conditions and handlers. See Section 12.7.4, “Conditions and Handlers”.

Cursors. See Section 12.7.5, “Cursors”.

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

Declarations must follow a certain order. Cursors must be declared before declaring handlers, and variables and conditions must be declared before declaring either cursors or handlers.

The bold part is the key of my mistake.

I think that the 12.7.5.1 section should have at least a remaind to the 12.7.2 section (but is not a problem fixable by WEByog, of course!)

Thank you for the support.

Regards

[Author]

Posts