Insert record stored procedure problem

[nag]

whats wrong in below SP

create procedure Insertlink(IN text varchar(45),IN Description
varchar(45),IN link varchar(45))
begin
insert into menu values(text,Description,link);
end;


im getting following error
Error
SQL query:

CREATE PROCEDURE Insertlink( IN TEXT VARCHAR( 45 ) , IN Description
VARCHAR( 45 ) , IN link VARCHAR( 45 ) ) BEGIN INSERT INTO menu
VALUES (

TEXT, Description, link
);

MySQL said:

#1064 - 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

[Robert Gonzalez]

Is that your entire procedure creation routine? I'm thinking you might need to reassign your DELIMITER then write the proc then reassign your DELIMITER back.

DELIMITER $$

DROP PROCEDURE IF EXISTS `InsertLink`$$

/*

 * I like using input vars that are named in a way 

 * that would prevent using reserved words, like text ;)

 */

CREATE PROCEDURE IF NOT EXISTS `InsertLink` (

  IN iText VARCHAR(45),

  IN iDescription VARCHAR(45),

  IN iLink VARCHAR(45)

)

BEGIN

  INSERT INTO `menu` VALUES (iText, iDescription, iLink);

  /* Just for sanity, capture your affected rows as a return var */

  SELECT ROW_COUNT() AS `success`;

END$$

DELIMITER ;

I didn't test this but it looks like it would work as is.

--
This group is managed and maintained by the development staff at 360 PSG. An enterprise application development company utilizing open-source technologies for todays small-to-medium size businesses.

For information or project assistance please visit :
http://www.360psg.com

You received this message because you are subscribed to the Google Groups "Professional PHP Developers" group.
To post to this group, send email to Professi...@googlegroups.com
To unsubscribe from this group, send email to Professional-P...@googlegroups.com
For more options, visit this group at http://groups.google.com/group/Professional-PHP

[nag]

i tried ur sp sir,

DROP PROCEDURE IF EXISTS `InsertLink`;

CREATE PROCEDURE IF NOT EXISTS `InsertLink` (
IN iText VARCHAR (45),

IN Description VARCHAR (45),
IN Link VARCHAR (45)
)
BEGIN
INSERT INTO `menu` VALUES (iText, Description,Link)
END;



stil getting below error
Error
SQL query:

CREATE PROCEDURE IF NOT EXISTS `InsertLink` (

IN iText VARCHAR( 45 ) ,

IN Description VARCHAR( 45 ) ,

IN Link VARCHAR( 45 )

) BEGIN INSERT INTO `menu`
VALUES (

iText, Description, Link
)
END ;

MySQL said:

#1064 - 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 'IF NOT EXISTS `InsertLink` (
IN iText VARCHAR (45),
IN Description VARCH' at line 1




On Aug 23, 9:21 pm, Robert Gonzalez

> >http://groups.google.com/group/Professional-PHP- Hide quoted text -
>
> - Show quoted text -

[Robert Gonzalez]

Does your user have CREATE PROCEDURE permissions? Also, you still haven't handled your delimiters. Every query inside a stored procedure needs to be a fully executable query as-is, including the ending ";" or "\g"; That means that you will necessarily need to change your delimiters around the procedure so that your procedure create and your queries inside the procedure don't clash.

[sc_mach]

Your error starts exactly where your problem is... "IF NOT EXISTS". I
don't think you can put that syntax there, at least, I couldn't find
it in the documentation: http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

You also need to set your delimiter like Robert has been saying. Try
this:

DELIMITER $$

DROP PROCEDURE IF EXISTS `InsertLink`$$

CREATE PROCEDURE `InsertLink` (

IN iText VARCHAR (45),
IN Description VARCHAR (45),
IN Link VARCHAR (45)
)
BEGIN

INSERT INTO `menu` VALUES (iText, Description,Link);
END$$

DELIMITER ;

> > >http://groups.google.com/group/Professional-PHP-Hide quoted text -