Stored Procedure Error Message

[armlocker]

Dear All - can anyone see what the syntax error is in the following stored procedure error?

Thanks

 

 

 

SQL query:

DELIMITER $$ CREATE PROCEDURE select_categories( TYPE VARCHAR( 6 ) ) BEGIN IF TYPE = 'coffee' THEN SELECT *
FROM general_coffees
ORDER BY category;
 

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 'DELIMITER $$
CREATE PROCEDURE select_categories (type VARCHAR(6))
BEGIN
IF ty' at line 1
 

[Larry]

Are you still having a problem with this or is this related to your other thread in which you've pointed out that your host doesn't support stored procedures?

[armlocker]

All sorted. My web hosting company didn't support Stored Procedures so I found a alternative php script written by the one and only LU is some of his blogs; here are the links for future references:

 

 

CHAPTER 8 ALTERNATIVE TO STORED PROCEDURES = shop.php / browse.php / sales.php / index.php

http://www.larryullm...dard-php-mysql/

 

CHAPTER 9 ALTERNATIVE TO STORED PROCEDURES = cart.php / wishlist.php

http://www.larryullm...ql-2-chapter-9/

 

CHAPTER 10 ALTERNATIVE TO STORED PROCEDURES = checkout.php / billing.php / final.php

http://www.larryullm...l-3-chapter-10/

[costaM]

Dear Larry,

I have an issue on the same thread.

I defined procedures in mysql.inc.php and called them in different file such as shop.php. Is it correct or I may create their own file to be included in files needs them?

And I have a error message: unexpected '$' in the line of DELIMITER $$. What Can I do to work out this sign $ that has problem?

 

Your advises are imperative as my system is meeting the requirement for procedures. PHP and Mysql are  5+.

[Larry]

Your understanding of this is incorrect. The stored procedures are defined in the database, not in any PHP file.

[costaM]

Dear Larry,

 

I succeeded to connect to mysql client throught dos and insert some data into tables. I tried many time till I did the following with database name to succeed:

 

INSERT INTO `mydbase_name`.`mytbl_name`(colmn1, colmn2, colmn3,...)

VALUES (' ',' ',.....)

 

But I failed to create the procedure as following:

DELIMITER $$

CREATE PROCEDURE select_category (type VARCHAR(6))

BEGIN

IF type = 'Foods' THEN

SELECT *

FROM tblcategory

ORDER BY strCat_Keyword;

END IF;

END$$

 

 

From END$$ without typing DELIMITER; the error message is splashed: No database selected!

How to make it?

 

I have tried the following:

 

DELIMITER $$

CREATE PROCEDURE mydatabase_name.select_category (type VARCHAR(6))

BEGIN

IF type = 'Foods' THEN

SELECT *

FROM tblcategory

ORDER BY strCat_Keyword;

END IF;

END$$

Then, without typing DELIMITER;, another error message is splashed: Access denied to connect to mydatabase_name @ localhost...

May you help me?

[Larry]

What is your actual database name? Because you need to select it (USE ) before you can create the stored procedure in it.

[costaM]

Thank you Larry, it works