Sign in to follow this  

[web] Mysql: Stored procedures & transactions

This topic is 3551 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hi, I wonder whether its possible to use stored procedures and transactions in mysql 5.x
1225 -- accept application
1226 DROP PROCEDURE accept_alliance_application; //
1227 CREATE PROCEDURE accept_alliance_application (IN operatorid SMALLINT UNSIGNED,IN playerid SMALLINT UNSIGNED,
1228                 IN allyid INT UNSIGNED, IN time INT UNSIGNED)
1229
1230 BEGIN
1231         DECLARE cnt INT UNSIGNED DEFAULT 0;
1232         DECLARE cnt2 INT UNSIGNED DEFAULT 0;
1233         DECLARE rank INT UNSIGNED DEFAULT 0;
1234         DECLARE name CHAR(32) DEFAULT '';
1235         DECLARE opname CHAR(32) DEFAULT '';
1236
1237         CALL has_right_alliance(operatorid,4,allyid,cnt);
1238
1239 --      find start rank
1240         SELECT COUNT(*) INTO cnt2 FROM alliance_rank ar WHERE ar.allyid=allyid AND (rights&8) > 0 LIMIT 1;
1241
1242
1243
1244
1245         IF cnt > 0 AND cnt2 > 0 THEN
1246 --              make sure the applicant isn t in another alliance already
1247                 SELECT COUNT(*) INTO cnt2 FROM alliance_member am WHERE playerid=playerid LIMIT 1;
1248
1249                 IF cnt2 = 0 THEN
1250 --                      get the starter rank
1251                         SELECT ar.rankid INTO rank FROM alliance_rank ar WHERE ar.allyid=allyid AND (rights&(1<<7)) > 0 LIMIT 1;
1252 --                      get player&operator name
1253                         SELECT p.name INTO name FROM player p WHERE p.playerid=playerid LIMIT 1;
1254                         SELECT p.name INTO opname FROM player p WHERE p.playerid=operatorid LIMIT 1;
1255
1256                         CALL add_user_to_alliance(allyid,playerid,rank,time);
1257
1258                         CALL log_alliance_event(allyid,time,CONCAT(opname,' hat ',name,' in die Allianz aufgenommen'));
1259                 END IF;
1260         END IF;
1261 END;
1262 //



log_alliance_event stored some text information into a seperate table, I wonder whether it is possible to encapsulate the entire procedure into a transaction or if there are some problems, such as transactions being terminated when calling procedures inside a procedure thx in advance

Share this post


Link to post
Share on other sites

This topic is 3551 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this