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 9 of 9
  1. #1
    Senior Coder
    Join Date
    May 2004
    Posts
    1,441
    Thanks
    15
    Thanked 0 Times in 0 Posts

    stored procedure help

    i'm trying to create my first stored procedure.
    this is what i have so far. No procedural statements yet as I want to get this working first and then i'll add in more.
    now i'm still getting errors on every line.
    what am I doing wrong?

    Code:
    DELIMITER //
    
    CREATE PROCEDURE spinsertnew (_filesource int,_firstname char(200),_lastname char(200))
    
    BEGIN
    
    DECLARE _Random int;
    DECLARE _Upper int;
    DECLARE _Lower int;
    DECLARE _MyIdentity int;
    DECLARE _isdouble int;
    
    END;
    //
    Last edited by guelphdad; 08-28-2006 at 02:16 AM.

  • #2
    $object->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Jun 2003
    Posts
    3,073
    Thanks
    2
    Thanked 22 Times in 22 Posts
    I'm pretty new to stored procedures, but I think what is missing is the word AS:

    Code:
    DELIMITER //
    
    CREATE PROCEDURE spinsertnew (_filesource int,_firstname char(200),_lastname char(200))
    
    AS
    
    BEGIN
    
    DECLARE _Random int;
    DECLARE _Upper int;
    DECLARE _Lower int;
    DECLARE _MyIdentity int;
    DECLARE _isdouble int;
    
    END;
    //
    BTW, you should use code tags to distinguish your code from the rest of your post. That makes it much easier to read.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    SNAP to it!

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    are you using mysql 5? you can't create a stored procedure if you aren't using mysql 5. do a
    Code:
    select version()
    to see what version you are using.

  • #4
    Senior Coder
    Join Date
    May 2004
    Posts
    1,441
    Thanks
    15
    Thanked 0 Times in 0 Posts
    i'm using mysql 5 but maybe it's not installed properly as now I'm getting an error mysql.proc does not exist.

    by the way the as is used with sql sever but I don't think with mysql.

  • #5
    Regular Coder
    Join Date
    Nov 2002
    Location
    Loughborough (UK)
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Does version 4.0.23 not support Stored Procedures in any way at all?

    I've just signed up to a hosting package (with the above version of MySql) and am here because I can't create a stored procedure. Are you saying I will need to find a new hosting company to be able to use Stored Procs?

    Thanks for your help.
    Chris

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    no not at all, only 5 supports stored procedures.

    you should find another host 4.0.x is out of date, and they should be using 4.1 at a minimum. you are not able to use subqueries or GROUP_CONCAT nor manage collations and character sets in 4.0 the way you are able to do for 4.1 and above.

    Note that 5.0 is the current production version of mysql, supplanting 4.1 in October 2005. MySQL 4.1 supplanted 4.0.x in October 2004 so that is quite out of date.

  • #7
    Regular Coder
    Join Date
    Nov 2002
    Location
    Loughborough (UK)
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Return Identity

    Oh bum!

    To be honest, the only reason I used Stored Procedures was the ability to return the identity of newly inserted rows. Is there another way I can do this in MySQL 4.0?

    Thanks again for your help.

    Regards,
    Chris

  • #8
    New to the CF scene
    Join Date
    Jan 2007
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you are using PHP, mysql_insert_id()

  • #9
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    last_insert_id() is the function in mysql. php's mysql_insert_id() merely calls last_insert_id. I mention it because you can probably find something similar if you are using another application than php.


  •  

    Posting Permissions

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