I have created a stored procedure in MySQL. It works great in my localhost. But When I want to execute in my remote server using phpMyadmin, It generates an error.
Here is the message generate in phpMyadmin .
Error
SQL query:
CREATE PROCEDURE writermgt.approvalProcess (
IN articleId INT,
IN managerId INT
) BEGIN DECLARE amount FLOAT;
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 4
The server wants to say may be error in declare statement. But what’s the error?
Here is my code
DROP PROCEDURE IF EXISTS writermgt.approvalProcess;
CREATE PROCEDURE writermgt.`approvalProcess`(IN articleId INT, IN managerId INT)
BEGIN
DECLARE amount FLOAT;
DECLARE totalcost FLOAT;
DECLARE multiplier FLOAT;
DECLARE writerId INT;
START TRANSACTION;
UPDATE art_r_manager SET
status = 'A'
WHERE artid = articleId;
UPDATE art_r_writer SET
status = 'A'
WHERE artid = articleId;
SELECT cost INTO amount FROM article WHERE artid = articleId;
SELECT cost_multiplier INTO multiplier FROM acmanager WHERE pid = managerId;
SELECT (amount + (multiplier * amount)) INTO totalcost;
SELECT wid INTO writerId FROM art_r_writer WHERE artid = articleId;
INSERT INTO baccount_writer VALUES ('', writerId, articleId, amount, '0');
INSERT INTO baccount_manager VALUES ('', managerId, articleId, totalcost, '0');
COMMIT;
END;
Vary much thanks for ur comment. Again I got ERROR!
Error
There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem
ERROR: Unknown Punctuation String @ 11
STR: //
SQL: delimiter //
CREATE PROCEDURE actionsAddingToArticleQueue(
IN articleId INT,
IN workerId INT,
OUT success INT
)
BEGIN
DECLARE existWriter INT;
SQL query:
delimiter // CREATE PROCEDURE actionsAddingToArticleQueue( IN articleId INT, IN workerId INT, OUT success INT ) BEGIN DECLARE existWriter INT;
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 actionsAddingToArticleQueue(
IN articleId INT,
IN ’ at line 1
My code is as follows
delimiter //
CREATE PROCEDURE actionsAddingToArticleQueue(
IN articleId INT,
IN workerId INT,
OUT success INT
)
BEGIN
DECLARE existWriter INT;
DECLARE minimumRank INT;
START TRANSACTION;
SELECT 0 INTO success;
SELECT min_rank INTO minimumRank FROM article WHERE artid = articleId;
SELECT COUNT(*) INTO existWriter FROM writer WHERE pid = workerId AND rank >= minimumRank AND max_articles < 25;
if existWriter > 0 THEN
INSERT INTO art_r_writer VALUES ( articleId, workerId, 'I', '', now());
SELECT 1 INTO success;
END IF;
COMMIT;
END
//
delimiter ;
I have used import method from phpMyadmin. Also Under SQL run the above SQL query. Same thing happen.
Is the problem can be that the server not support…?