Cannot created stored procedure (Using example from mysql manual) -- mysql 5.1.37 -- Ubuntu 9.10
am 16.12.2009 19:46:18 von Walton Hoops
------=_NextPart_000_0013_01CA7E45.655F2D60
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Hi all.
I am running into a very frustrating problem trying to created a stored
procedure.
I had originally assumed I was using bad syntax, but even examples copied
and pasted
directly from the manual are giving the same error.
mysql> CREATE DEFINER = 'walton'@'localhost' PROCEDURE account_count()
-> BEGIN
-> SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near '' at line 3
mysql>
This example can be found at:
http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html
Google has failed me on this one.
Can anyone advise me as to what I need to do to troubleshoot this? Also if
it is in error in the documentation,
how would I go about notifying someone so it can be corrected?
Any help would be greatly appreciated.
------=_NextPart_000_0013_01CA7E45.655F2D60--
RE: Cannot created stored procedure (Using example from mysqlmanual) -- mysql 5.1.37 -- Ubuntu 9.10
am 16.12.2009 19:55:11 von Gavin Towey
You need to use
DELIMITER //
Or some other symbol besides ; to change the client's end-of-statement symb=
ol. Otherwise it ends the statement at the first ; inside the procedure yo=
u use, but it's not yet complete.
This is described in the manual on that same page.
Regards
Gavin Towey
-----Original Message-----
From: Walton Hoops [mailto:walton@vyper.hopto.org]
Sent: Wednesday, December 16, 2009 10:46 AM
To: mysql@lists.mysql.com
Subject: Cannot created stored procedure (Using example from mysql manual) =
-- mysql 5.1.37 -- Ubuntu 9.10
Hi all.
I am running into a very frustrating problem trying to created a stored
procedure.
I had originally assumed I was using bad syntax, but even examples copied
and pasted
directly from the manual are giving the same error.
mysql> CREATE DEFINER =3D 'walton'@'localhost' PROCEDURE account_count()
-> BEGIN
-> SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near '' at line 3
mysql>
This example can be found at:
http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html
Google has failed me on this one.
Can anyone advise me as to what I need to do to troubleshoot this? Also if
it is in error in the documentation,
how would I go about notifying someone so it can be corrected?
Any help would be greatly appreciated.
This message contains confidential information and is intended only for the=
individual named. If you are not the named addressee, you are notified th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f you have received this e-mail by mistake and delete this e-mail from your=
system. E-mail transmission cannot be guaranteed to be secure or error-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate or incomplete, or contain viruses. The sender therefore does not accept=
liability for any loss or damage caused by viruses or errors or omissions =
in the contents of this message, which arise as a result of e-mail transmis=
sion. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA 94089,=
USA, FriendFinder.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
RE: Cannot created stored procedure (Using example from mysql manual) -- mysql 5.1.37 -- Ubuntu 9.10
am 16.12.2009 19:59:04 von Walton Hoops
> -----Original Message-----
> From: Walton Hoops [mailto:walton@vyper.hopto.org]
> Hi all.
> I am running into a very frustrating problem trying to created a stored
> procedure.
>
> I had originally assumed I was using bad syntax, but even examples
> copied
> and pasted
>
> directly from the manual are giving the same error.
> mysql> CREATE DEFINER = 'walton'@'localhost' PROCEDURE account_count()
>
> -> BEGIN
>
> -> SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
>
> ERROR 1064 (42000): You have an error in your SQL syntax; check the
> manual
> that corresponds to your MySQL server version for the right syntax to
> use
> near '' at line 3
>
> mysql>
> This example can be found at:
>
> http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html
>
Figures, I find the answer just as soon as I send the request for help.
I just needed to read the documentation better. As penance, here is
the answer I found:
From http://dev.mysql.com/doc/refman/5.1/en/stored-programs-defin ing.html:
"If you use the mysql client program to define a stored program that
contains the semicolon characters within its definition, a problem
arises. By default, mysql itself recognizes semicolon as a statement
delimiter, so you must redefine the delimiter temporarily to cause
mysql to pass the entire stored program definition to the server."
So the example I was using becomes:
DELIMITER |
CREATE DEFINER = 'admin'@'localhost' PROCEDURE account_count()
SQL SECURITY INVOKER
BEGIN
SELECT 'Number of accounts:', COUNT(*) FROM mysql.user;
END|
DELIMITER ;
Sorry for the unnecessary question.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org