LinuxQuestions.org
Share your knowledge at the LQ Wiki.
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-20-2019, 09:56 PM   #1
mfoley
Senior Member
 
Registered: Oct 2008
Location: Columbus, Ohio USA
Distribution: Slackware
Posts: 1,668

Rep: Reputation: 134Reputation: 134
Need help with mysql stored procedure syntax


I have the following mysql script:
Code:
DELIMITER //
ALTER procedure blueDuesNotice_sp()
BEGIN

CREATE TEMPORARY TABLE memberDues (
    personId    mediumint,
    amount      decimal(6,2);
);
  
insert into memberDues select personId, 28
  from orgMembers
  where orgId = 10 and standing = 1 and serviceYears >= 50;
  
select * from memberDues;
  
END //
When I try to execute this, I get the error:
Code:
ERROR 1064 (42000) at line 2: 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 '()
BEGIN

CREATE TEMPORARY TABLE memberDues (
    personId    mediumint,
    amount' at line 1
Privileges are:
Code:
> show grants for 'user'@'localhost'
GRANT ALL PRIVILEGES ON `dbname`.* TO 'user'@'localhost'
I can't see the problem. If I replace everything between the BEGIN/END with a select statement, it works fine. Is there a problem with my CREATE TEMPORARY TABLE syntax? I've checked Internet references on this and don't see anything wrong.

Last edited by mfoley; 09-20-2019 at 10:14 PM.
 
Old 09-20-2019, 10:58 PM   #2
astrogeek
Moderator
 
Registered: Oct 2008
Distribution: Slackware [64]-X.{0|1|2|37|-current} ::12<=X<=14, FreeBSD_12{.0|.1}
Posts: 5,221
Blog Entries: 11

Rep: Reputation: 3171Reputation: 3171Reputation: 3171Reputation: 3171Reputation: 3171Reputation: 3171Reputation: 3171Reputation: 3171Reputation: 3171Reputation: 3171Reputation: 3171
I think the error is with the ALTER procedure statement.

Look closely at the error message, it is choking on the parenthesis.
 
1 members found this post helpful.
Old 09-20-2019, 11:29 PM   #3
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
^^ I agree. Here is the documentation.
 
Old 09-21-2019, 01:16 AM   #4
mfoley
Senior Member
 
Registered: Oct 2008
Location: Columbus, Ohio USA
Distribution: Slackware
Posts: 1,668

Original Poster
Rep: Reputation: 134Reputation: 134
OK, I manually dropped the procedure and replaced the ALTER with CREATE:
Code:
DELIMITER //

CREATE procedure blueDuesNotice_sp()
BEGIN

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

insert into memberDues select personId, 28
  from orgMember
  where orgId = 10 and standing = 1 and serviceYears >= 50;

select * from memberDues;
 
END //
Still have an error:
Code:
ERROR 1064 (42000) at line 3: 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 ';
);

insert into memberDues select personId, 28
  from orgMember
  where orgId ' at line 6
I have confirmed that the procedure is dropped:
Code:
> drop procedure blueDuesNotice_sp
ERROR 1305 (42000) at line 1: PROCEDURE members.blueDuesNotice_sp does not exist
To verify something works, I changed the procedure to:
Code:
DELIMITER //

CREATE procedure blueDuesNotice_sp()
BEGIN
select personId, 28 from orgMember where orgId = 10 and standing = 1 and serviceYears >= 50 limit 1;
 
END //
and that ran OK. But yes, you are both right. the ALTER doesn't work. I do have to DROP and CREATE.

Last edited by mfoley; 09-21-2019 at 01:21 AM.
 
Old 09-21-2019, 02:03 AM   #5
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, the error is clear...the semicolon at the end of the amount... line is syntactically incorrect.
 
2 members found this post helpful.
Old 09-21-2019, 02:06 AM   #6
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, the error is clear...the semicolon at the end of the amount... line is syntactically incorrect.
Sheesh! Yup, that was it. I removed the semi-colon and it worked!

Thanks!
 
Old 09-21-2019, 02:11 AM   #7
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
Glad we could help.
I, too, find that MySQL error messages appear to be vague, but they are usually not...they start at the specific point there is a problem. The “vagueness” happens, I think, because of quoting of context following the error...but as can be seen in this case, that was necessary to clarify which semicolon was the glitch.
 
  


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:52 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