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; 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); |
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... |
https://dev.mysql.com/doc/refman/8.0/en/declare.html
Quote:
|
Quote:
I saw the second part but not the first. Still, my guess at moving the DECLAREs to the top would probably have worked. |
Quote:
Quote:
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 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. |
See #3.
Quote:
If I understand what you did, you only moved the variable DECLAREs, yes? Try this: Code:
DROP PROCEDURE IF EXISTS blueDuesNotice_sp; |
Quote:
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! |
You’re most welcome.
I find the documentation at dev.mysql.com is comprehensive, but that makes for a lot of reading. |
Quote:
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. |