Sign in to follow this  
Basiror

[web] Mysql: Stored procedures & transactions

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

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