LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices


Reply
  Search this Thread
Old 09-21-2019, 11:24 AM   #1
mfoley
Senior Member
 
Registered: Oct 2008
Location: Columbus, Ohio USA
Distribution: Slackware
Posts: 1,668

Rep: Reputation: 134Reputation: 134
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.

Last edited by mfoley; 09-21-2019 at 11:27 AM.
 
Old 09-21-2019, 11:50 AM   #2
scasey
Senior Member
 
Registered: Feb 2013
Location: Tucson, AZ, USA
Distribution: CentOS 7.6
Posts: 3,778

Rep: Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263
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...

Last edited by scasey; 09-21-2019 at 12:26 PM.
 
1 members found this post helpful.
Old 09-21-2019, 01:41 PM   #3
NevemTeve
Senior Member
 
Registered: Oct 2011
Location: Budapest
Distribution: Debian/GNU/Linux, AIX
Posts: 3,860

Rep: Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344Reputation: 1344
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.
 
3 members found this post helpful.
Old 09-21-2019, 01:52 PM   #4
scasey
Senior Member
 
Registered: Feb 2013
Location: Tucson, AZ, USA
Distribution: CentOS 7.6
Posts: 3,778

Rep: Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263
Quote:
Originally Posted by NevemTeve View Post
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.
 
1 members found this post helpful.
Old 09-21-2019, 03:59 PM   #5
mfoley
Senior Member
 
Registered: Oct 2008
Location: Columbus, Ohio USA
Distribution: Slackware
Posts: 1,668

Original Poster
Rep: Reputation: 134Reputation: 134
Quote:
Originally Posted by scasey View Post
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 View Post
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.

Last edited by mfoley; 09-21-2019 at 04:08 PM.
 
Old 09-21-2019, 04:14 PM   #6
scasey
Senior Member
 
Registered: Feb 2013
Location: Tucson, AZ, USA
Distribution: CentOS 7.6
Posts: 3,778

Rep: Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263
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"

Last edited by scasey; 09-21-2019 at 04:16 PM.
 
2 members found this post helpful.
Old 09-21-2019, 07:01 PM   #7
mfoley
Senior Member
 
Registered: Oct 2008
Location: Columbus, Ohio USA
Distribution: Slackware
Posts: 1,668

Original Poster
Rep: Reputation: 134Reputation: 134
Quote:
Originally Posted by scasey View Post
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!
 
Old 09-21-2019, 07:23 PM   #8
scasey
Senior Member
 
Registered: Feb 2013
Location: Tucson, AZ, USA
Distribution: CentOS 7.6
Posts: 3,778

Rep: Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263
Youíre most welcome.
I find the documentation at dev.mysql.com is comprehensive, but that makes for a lot of reading.
 
Old 09-21-2019, 08:55 PM   #9
scasey
Senior Member
 
Registered: Feb 2013
Location: Tucson, AZ, USA
Distribution: CentOS 7.6
Posts: 3,778

Rep: Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263Reputation: 1263
Quote:
Originally Posted by mfoley View Post
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.
 
  


Reply

Tags
mysql


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] mysql stored procedure help rootaccess Linux - General 1 10-18-2012 02:18 PM
Can bind dlz work with the mysql stored procedure? oranix Linux - Server 1 01-22-2011 06:58 AM
Problem creating Stored Procedure in MySQL Administrator andrewhiggs Linux - Software 1 07-23-2010 03:27 AM
MySQL Stored Procedure Question?? sehgals Linux - Software 5 01-31-2005 01:54 PM
stored procedure in mysql suchi_s Programming 2 10-01-2004 06:14 AM

LinuxQuestions.org > Forums > Non-*NIX Forums > Programming

All times are GMT -5. The time now is 10:55 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration