MySQL Procedures

[POG1]

I read about procedures on the mysql website and tried to give it a go, I however cannot seem to create even a simple procedure. Any suggestions? this is what i have come up with so far..

CREATE PROCEDURE buyShopItem

(item INT(11),shop INT(11),user INT(11),itemCost INT(11))

BEGIN

DELETE FROM `userShopItems` WHERE `itemId` = item;

UPDATE `userShops` SET `shopStock` = `shopStock` - '1' WHERE `shopId` = shop;

UPDATE `users` SET `money` = `money` - itemCost WHERE `userid` = user;

END;

[Lithium]

Re: MySQL Procedures

what version are you using on mysql?

[POG1]

Re: MySQL Procedures

5.summin

[Lithium]

Re: MySQL Procedures

 

5.summin

lol, i figured that, thought the .summin is important as if i can recall those are available only around 5.1.??

[POG1]

Re: MySQL Procedures

i will update my mysql version now, what is the newest release?

[Lithium]

Re: MySQL Procedures

hmmm newest newest might be 6.summin :P i'm using 5.1.1? and your routine also errors meaning i might be missing something as well

[codestryke]

Re: MySQL Procedures

I'm going to go out on a limb here and assume you are using phpMyAdmin to create this procedure. If you are then you need to do the following or it won't work:

Click the SQL tab enter the following for the SQL

DROP PROCEDURE IF EXISTS buyShopItem $$
CREATE PROCEDURE buyShopItem(item INT(11), shop INT(11), user INT(11), itemCost INT(11))
BEGIN
DELETE FROM `userShopItems` WHERE `itemId` = item;
UPDATE `userShops` SET `shopStock` = `shopStock` - 1 WHERE `shopId` = shop;
UPDATE `users` SET `money` = `money` - itemCost WHERE `userid` = user;
END $$

 

below where you enter the SQL there should be a text field called delimeter, enter $$

click Go and it should now work for you

also don't do field = field - '1'

'1' is a char, not a number ;)