Declare Cursor Statement In Stored-Procedure

[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