Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Jun 2010
    Posts
    302
    Thanks
    68
    Thanked 8 Times in 8 Posts

    Unhappy Getting nowhere with procedures, please help!

    Hi all,

    I am trying to write MySQL procedures for the first time.

    I'm using MySQL 5.1 on Debian Squeeze.

    I tried to copy and paste the following example from the MySQL documentation: http://dev.mysql.com/doc/refman/5.1/...statement.html

    That threw up a whole load of errors, so I have simplified it right down to the following:

    Code:
    drop procedure if exists mytest;
    
    create procedure mytest()
    begin
      declare myval int default 5;
    end;
    The "drop procedure" bit works fine, but the create procedure block fails, with the following errors:

    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
    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 'end' at line 1


    I don't think it can get much simpler than that, what am I doing wrong??!

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    If you are running this SP in the mysql client, you need to change your delimiter first, otherwise it will think you have finished your SP at the first ; it comes to.

    DELIMITER #

    will change your delimiter to # so you'll have to type that at the very end of your SP to complete it. It now won't trip over the ; when it comes to it though. Then change back to ; after you have created your SP.

  • Users who have thanked guelphdad for this post:

    XmisterIS (06-07-2011)

  • #3
    Regular Coder
    Join Date
    Jun 2010
    Posts
    302
    Thanks
    68
    Thanked 8 Times in 8 Posts
    Many thanks for that, but goodness, the people at MySQL don't want to make it easy do they?! I would class that little tidbit of information regarding the change of delimiter as "crucial" ... but they make no mention of it at all in the examples!

    Also, I've noticed that sometime I'll run a command at the MySQL command line and I'll get back a glib little report like "Query OK, 0 rows affected, 1 warning (0.00 sec)". In other words, "there is a warning, but we're not going to tell you what it is". Great ... !!

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,659 Times in 4,621 Posts
    ???

    The first section of the docs re procedures shows using the delimiter. Discusses why it is needed and what you can use. Etc. Includes example.

    http://dev.mysql.com/doc/refman/5.1/...-defining.html

    And as for warnings:

    http://dev.mysql.com/doc/refman/5.1/...-warnings.html


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •