LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (https://www.linuxquestions.org/questions/programming-9/)
-   -   mysql cursor and stored procedure problems (https://www.linuxquestions.org/questions/programming-9/mysql-cursor-and-stored-procedure-problems-4175661258/)

mfoley 09-21-2019 11:24 AM

mysql cursor and stored procedure problems
 
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 (Post 6039041)
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 (Post 6039014)
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 (Post 6039041)

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 (Post 6039078)
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 (Post 6039122)
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.


All times are GMT -5. The time now is 02:16 PM.