current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 3 down vote favorite

I have this code

DELIMITER $$
DROP FUNCTION IF EXISTS `GetNextID` $$
CREATE FUNCTION `GetNextID`() RETURNS INT DETERMINISTIC
BEGIN
DECLARE NextID INT;
SELECT MAX(articleID) + 5 INTO NextID FROM table_article;
RETURN NextID;
END$$
DELIMITER ;

INSERT INTO table_article ( articleID, articleAlias ) VALUES ( GetNextID(), 'TEST' );

executed OK in phpMyAdmin, but it fails when i pass this query to mysql_query PHP function/ Me guess this is because of the function and semi-colons. What do i do?

share|improve this question
    
It would help to post your code and the error you're getting. –  jackbot Apr 14 '11 at 11:45
add comment

2 Answers

up vote 2 down vote accepted

DELIMITER is not a MySQL keyword: it is a reserved word parsed by clients (like mysql, phpMyAdmin etc.) which allows splitting the queries.

You should split it manually and submit the three queries:

DROP FUNCTION IF EXISTS `GetNextID`

,

CREATE FUNCTION `GetNextID`() RETURNS INT DETERMINISTIC
BEGIN
DECLARE NextID INT;
SELECT MAX(articleID) + 5 INTO NextID FROM table_article;
RETURN NextID;
END

and

INSERT INTO table_article ( articleID, articleAlias ) VALUES ( GetNextID(), 'TEST' )

in three separate calls to the database.

share|improve this answer
    
Thanks, that worked! –  dr3w Apr 14 '11 at 11:53
    
so mark it as correct... –  dogmatic69 Apr 14 '11 at 12:15
    
yeah, i was going to, but stackoverflow told me to wait for 3 minutes, though ;) here you go... –  dr3w Apr 14 '11 at 12:26
add comment
up vote 0 down vote

you have DECLARE NextID INT; and RETURN NextID; and another line with ; inside the DELIMITER $$ deceleration.

my advice is stop using $$ as a delimiter

share|improve this answer
    
what to use instead? can you rewrite the code the right way, pls? =] –  dr3w Apr 14 '11 at 11:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.