[SOLVED] mysql cursor and stored procedure problems

mfoley · 09-21-2019, 11:24 AM

Sorry to be such a mysql idjit! I have the stored procedure script shown below. I've followed examples on the web, but getting an error. Unfortunately, mysql error messages are not very helpful.

Code:

DROP PROCEDURE IF EXISTS blueDuesNotice_sp;

DELIMITER //

CREATE procedure blueDuesNotice_sp()
BEGIN

CREATE TEMPORARY TABLE memberDues (
    personId    mediumint,
    duesType    varchar(60),
    amount      decimal(6,2)
);

CREATE TEMPORARY TABLE duesLetter (
    mailTo      varchar(60),
    addrLine1   varchar(60),
    addrLine2   varchar(60),
    addrLine3   varchar(60),
    ZIPcode     varchar(20),
    email       varchar(80),
    duesType    varchar(60),
    duesAmount  decimal(6,2)
);

-- This year's 50+ dues
insert into memberDues select personId, 'Endowment:', 28
  from orgMember
  where orgId = 10 and standing = 1 and orgStatus <> 4 and serviceYears >= 50;

-- This year's regular dues

insert into memberDues select personId, 'Regular Dues:', 88
  from orgMember
  where orgId = 10 and standing = 1 and orgStatus <> 4 and serviceYears < 50;

DECLARE xxmailTo                varchar(60);
DECLARE xxaddrLine1     varchar(60);
DECLARE xxaddrLine2     varchar(60);
DECLARE xxaddrLine3     varchar(60);
DECLARE xxphone         varchar(20);
DECLARE xxZIPcode       varchar(20);
DECLARE xxemail         varchar(80);
DECLARE xxduesType      varchar(60);
DECLARE xxduesAmount    decimal(6,2);

DECLARE MyCursor CURSOR FOR
select a.mailTo, a.addrLine1, a.addrLine2, coalesce(a.addrLine3,'') addrLine3, 
  coalesce(a.homePhone,'') phone, coalesce(a.email,'') email, d.duesType, d.amount
from memberDues d
join vwMailingAddress a on a.personId = d.personId
order by d.personId;

-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

OPEN myCursor;

getDues: LOOP
  FETCH myCursor INTO xxmailTo, xxaddrLine1, xxaddrLine2, xxaddrLine3, xxphone, xxZIPcode, xxemail, xxduesType, xxduesAmount
  IF finished = 1 
  THEN 
    LEAVE getDues;
  END IF;

  insert into duesLetter select xxmailTo, xxaddrLine1, xxaddrLine2, xxaddrLine3, xxZIPcode, xxemail, xxduesType, xxduesAmount;
END LOOP getDues;

CLOSE myCursor;
select * from  duesLetter;

END //

and the error is:

Code:

ERROR 1064 (42000) at line 5: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE xxmailTo                varchar(60);
DECLARE xxaddrLine1     varchar(60);
DECLARE xxaddrLi' at line 32

The line numbers appear meaningless. Line 5 is the "CREATE procedure" statement. Line 32 is the second "insert into memberDues" statement. I've previously tested through and excluding the "DECLARE xxmailTo". Now I'm trying to declare some local variables and create a cursor. I've done this 4-zillion times on SQL Server, first time trying with mysql.

scasey · 09-21-2019, 11:50 AM

Again, something is not syntactically correct at the point identified in the error message:
“DECLARE xxmailto ....”'
Review the documentation for declaring a variable within a stored procedure.
(Which is exactly what the error message is saying)

Have you done that? What did you need help with understanding about what the documentation said?
I wouldn’t think the syntax is a lot different, but if that code would work in SQLServer, there must be some differences..

Line numbers: 5 is the line number of the CREATE, in which the error occurs. Not sure what the line 32 at the end of the quoted code is about.

As I pointed out in your other thread, those error messages are very explicit about where the problem is...what they don’t do is tell what the problem is...they just say “look it up”.

EDIT: I tried to look it up, but didn't find anything obvious (to me)
Some ideas:
Move the DECLAREs to the top of the stored proc.
Review the commands that are not allowed in stored procs...

NevemTeve · 09-21-2019, 01:41 PM

https://dev.mysql.com/doc/refman/8.0/en/declare.html

Quote:

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. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations.

scasey · 09-21-2019, 01:52 PM

Quote:

Originally Posted by NevemTeve

https://dev.mysql.com/doc/refman/8.0/en/declare.html

Quote:

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. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations.

There you go.

Nice of you to look it up for the OP

I saw the second part but not the first. Still, my guess at moving the DECLAREs to the top would probably have worked.

mfoley · 09-21-2019, 03:59 PM

Quote:

Originally Posted by scasey

Again, something is not syntactically correct at the point identified in the error message:
“DECLARE xxmailto ....”'
Review the documentation for declaring a variable within a stored procedure.

As I pointed out in your other thread, those error messages are very explicit about where the problem is...what they don’t do is tell what the problem is...they just say “look it up”.

Quote:

Originally Posted by NevemTeve

https://dev.mysql.com/doc/refman/8.0/en/declare.html

Ok, I did check this link and do see where it says, "... must be at its start, before any other statements." Before trying to write this script, as it is my 1st mysql SP, I checked at least 3 other links, including one on DECLAREs in stored procedures (e.g. http://www.mysqltutorial.org/variabl...rocedures.aspx), and none of the ones I checked mentioned that the DECLARE had to be at the beginning of the stored procedure.

So that done, I still have problems. Again, sorry to be trouble on this, but still struggling. I cut/pasted the declare line to the beginning of the script and tried it. Now I get:

Code:

ERROR 1064 (42000) at line 5: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE MyCursor CURSOR FOR select a.mailTo, a.addrLine1, a.addrLine2, coalesce(' at line 42

Line 42 is the one having, "insert into memberDues select personId, 'Regular Dues:', 88", which is now the line(s) just before the "DECLARE MyCursor" line. I don't see the "where it's at" in this case being informatively "explict", as you (scasey) wrote. If I remove lines starting from the "DECLARE myCursor" line and following, leaving the allegedly offending "insert into memberDues select ..." line 42, it runs OK.

I've spent a good hour looking at this code and checking syntax on various pages and I simply do not see the problem. I've not re-included the script here because all I've done is cut/paste the DECLARE variable lines to the beginning. All else is the same.

scasey · 09-21-2019, 04:14 PM

See #3.

Quote:

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. Cursor declarations must appear before handler declarations. Variable and condition declarations must appear before cursor or handler declarations.

As I read that, ALL DECLAREs have to be at the top of the stored procedure.
If I understand what you did, you only moved the variable DECLAREs, yes?

Try this:

Code:

DROP PROCEDURE IF EXISTS blueDuesNotice_sp;

DELIMITER //

CREATE procedure blueDuesNotice_sp()
BEGIN
DECLARE xxmailTo                varchar(60);
DECLARE xxaddrLine1     varchar(60);
DECLARE xxaddrLine2     varchar(60);
DECLARE xxaddrLine3     varchar(60);
DECLARE xxphone         varchar(20);
DECLARE xxZIPcode       varchar(20);
DECLARE xxemail         varchar(80);
DECLARE xxduesType      varchar(60);
DECLARE xxduesAmount    decimal(6,2);

DECLARE MyCursor CURSOR FOR
select a.mailTo, a.addrLine1, a.addrLine2, coalesce(a.addrLine3,'') addrLine3, 
  coalesce(a.homePhone,'') phone, coalesce(a.email,'') email, d.duesType, d.amount
from memberDues d
join vwMailingAddress a on a.personId = d.personId
order by d.personId;

-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

CREATE TEMPORARY TABLE memberDues (
    personId    mediumint,
    duesType    varchar(60),
    amount      decimal(6,2)
);

CREATE TEMPORARY TABLE duesLetter (
    mailTo      varchar(60),
    addrLine1   varchar(60),
    addrLine2   varchar(60),
    addrLine3   varchar(60),
    ZIPcode     varchar(20),
    email       varchar(80),
    duesType    varchar(60),
    duesAmount  decimal(6,2)
);

-- This year's 50+ dues
insert into memberDues select personId, 'Endowment:', 28
  from orgMember
  where orgId = 10 and standing = 1 and orgStatus <> 4 and serviceYears >= 50;

-- This year's regular dues

insert into memberDues select personId, 'Regular Dues:', 88
  from orgMember
  where orgId = 10 and standing = 1 and orgStatus <> 4 and serviceYears < 50;


OPEN myCursor;

getDues: LOOP
  FETCH myCursor INTO xxmailTo, xxaddrLine1, xxaddrLine2, xxaddrLine3, xxphone, xxZIPcode, xxemail, xxduesType, xxduesAmount
  IF finished = 1 
  THEN 
    LEAVE getDues;
  END IF;

  insert into duesLetter select xxmailTo, xxaddrLine1, xxaddrLine2, xxaddrLine3, xxZIPcode, xxemail, xxduesType, xxduesAmount;
END LOOP getDues;

CLOSE myCursor;
select * from  duesLetter;

END //

Variables, then cursors, then handlers -- all "before any other statements"

mfoley · 09-21-2019, 07:01 PM

Quote:

Originally Posted by scasey

See #3.
As I read that, ALL DECLAREs have to be at the top of the stored procedure.
If I understand what you did, you only moved the variable DECLAREs, yes?

Variables, then cursors, then handlers -- all "before any other statements"

Yes, you are right! When I moved all declares to the top, it worked. When I re-read your referenced link now, I also get that understanding. I guess a couple of decades of writing SQL Server and Oracle procedures has laid down pretty deep tracks in the coding hemisphere of my brain.

I liked mysql a lot better BEFORE this exercise! The "what is wrong" part of error messages suck!

Thanks for your patience and help. Hopefully, I won't be back on this particular project!

scasey · 09-21-2019, 07:23 PM

You’re most welcome.
I find the documentation at dev.mysql.com is comprehensive, but that makes for a lot of reading.

scasey · 09-21-2019, 08:55 PM

Quote:

Originally Posted by mfoley

Yes, you are right! When I moved all declares to the top, it worked. When I re-read your referenced link now, I also get that understanding. I guess a couple of decades of writing SQL Server and Oracle procedures has laid down pretty deep tracks in the coding hemisphere of my brain.

Just curious...are SQL Server and Oracle less restrictive about the use of DECLARE in stored procedures?
I’ve found very few syntactic differences between Oracle and MySQL. They both seem to adhere to the standards pretty well.