Solved

transaction & rolback mysql function

Posted on 2013-11-15
14
313 Views
Last Modified: 2013-11-17
following to this question: http://www.experts-exchange.com/Database/MySQL/Q_28295248.html
how to alert when no more seats
and how to write a function to rollback sets after booking expired?
Thank you
0
Comment
Question by:dorarishome
  • 10
  • 4
14 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 39654499
See fiddle: http://sqlfiddle.com/#!2/7aeca1/1


CREATE PROCEDURE `reserve` (_pnr int, _seats int)
READS SQL DATA
BEGIN

  DECLARE no_more_rows BOOLEAN;
  DECLARE row_id int default 0; 
  DECLARE row_pnr varchar(4) default '';
  DECLARE row_seats int default 0;

  DECLARE pnr_cur CURSOR FOR
    SELECT
        ID, PNR, SEATS
    FROM pnr_summary
    WHERE pnr_id = _pnr;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

  DROP TABLE IF EXISTS tmp;
  create temporary table tmp (
    pnr varchar(4),
    seats int,
    err varchar(15)
    );

  START TRANSACTION;

  OPEN pnr_cur;

  the_loop: LOOP

    FETCH  pnr_cur
    INTO   row_id, row_pnr, row_seats;

    IF no_more_rows THEN
        CLOSE pnr_cur;
        LEAVE the_loop;
    END IF;

    IF _seats < row_seats THEN
      UPDATE pnr_summary SET seats = seats - _seats WHERE ID = row_id;
      INSERT tmp (pnr, seats) VALUES (row_pnr, _seats);
      SET _seats = 0;
      CLOSE pnr_cur;
      LEAVE the_loop;
    END IF;

    IF _seats >= row_seats THEN
      UPDATE pnr_summary SET seats = 0 WHERE ID = row_id;
      INSERT tmp (pnr, seats) VALUES (row_pnr, row_seats);
      SET _seats = _seats - row_seats;
    END IF;
   
  END LOOP the_loop;

  IF _seats > 0 THEN
      INSERT tmp (pnr, seats, err) VALUES (null, _seats, 'ERROR: NO SEATS');
      SELECT * FROM tmp;
      ROLLBACK;
  ELSE
      COMMIT;
      SELECT * FROM tmp;
  END IF;

END//

Open in new window

0
 

Author Comment

by:dorarishome
ID: 39654535
hi,
what i mean  by rollback is a condition where i need to return seats to its original place!
how to achieve this?
Thank you
0
 

Author Comment

by:dorarishome
ID: 39654558
'summary_pnr' Table
Hi,
Attached a picture of my summary_pnr table NOT pnr_summary
I fixed the procedure but its giving both pnr rows as -value
Could you please help
In addition to another function where I am allowed to return seats
0
 

Author Comment

by:dorarishome
ID: 39654579
CREATE TABLE summary_pnr
	(`tID` int, `id` int, `pnr` varchar(4), `seats` int);
	
INSERT INTO summary_pnr
	(`tID`, `id`, `pnr`, `seats`)
VALUES
	(1, 123, 'ABC1', 50),
	(2, 123, 'ABC2', 100),
	(3, 124, 'XYZ', 200);


CREATE PROCEDURE `reserve` (_pnr varchar(20), _seats int)
READS SQL DATA
BEGIN

  DECLARE no_more_rows BOOLEAN;
  DECLARE row_id int default 0; 
  DECLARE row_pnr varchar(4) default '';
  DECLARE row_seats int default 0;

  DECLARE pnr_cur CURSOR FOR
    SELECT
        id, pnr, seats
    FROM summary_pnr
    WHERE id = _pnr;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

  DROP TABLE IF EXISTS tmp;
  create temporary table tmp (
    pnr varchar(4),
    seats int,
    err varchar(15)
    );

  START TRANSACTION;

  OPEN pnr_cur;

  the_loop: LOOP

    FETCH  pnr_cur
    INTO   row_id, row_pnr, row_seats;

    IF no_more_rows THEN
        CLOSE pnr_cur;
        LEAVE the_loop;
    END IF;

    IF _seats < row_seats THEN
      UPDATE summary_pnr SET seats = seats - _seats WHERE id = row_id;
      INSERT tmp (pnr, seats) VALUES (row_pnr, _seats);
      SET _seats = 0;
      CLOSE pnr_cur;
      LEAVE the_loop;
    END IF;

    IF _seats >= row_seats THEN
      UPDATE summary_pnr SET seats = 0 WHERE ID = row_id;
      INSERT tmp (pnr, seats) VALUES (row_pnr, row_seats);
      SET _seats = _seats - row_seats;
    END IF;
   
  END LOOP the_loop;

  IF _seats > 0 THEN
      INSERT tmp (pnr, seats, err) VALUES (null, _seats, 'ERROR: NO SEATS');
      SELECT * FROM tmp;
      ROLLBACK;
  ELSE
      COMMIT;
      SELECT * FROM tmp;
  END IF;

END//

Open in new window



Up is what I need but its giving -value on reserve seats
please help
0
 

Author Comment

by:dorarishome
ID: 39654587
please this is urgent if you can help as soon as possible to close it
Thank you
0
 

Author Comment

by:dorarishome
ID: 39654600
CREATE TABLE summary_pnr
	(`tID` int, `id` varchar(20), `pnr` varchar(20), `seats` varchar(20));
	
INSERT INTO summary_pnr
	(`tID`, `id`, `pnr`, `seats`)
VALUES
	(1, 123, 'ABC1', 50),
	(2, 123, 'ABC2', 100),
	(3, 124, 'XYZ', 200);


CREATE PROCEDURE `reserve` (_pnr varchar(20), _seats varchar(20))
READS SQL DATA
BEGIN

  DECLARE no_more_rows BOOLEAN;
  DECLARE row_id varchar(20) default ''; 
  DECLARE row_pnr varchar(20) default '';
  DECLARE row_seats varchar(20) default '';

  DECLARE pnr_cur CURSOR FOR
    SELECT
        id, pnr, seats
    FROM summary_pnr
    WHERE id = _pnr;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

  DROP TABLE IF EXISTS tmp;
  create temporary table tmp (
    pnr varchar(20),
    seats varchar(20),
    err varchar(15)
    );

  START TRANSACTION;

  OPEN pnr_cur;

  the_loop: LOOP

    FETCH  pnr_cur
    INTO   row_id, row_pnr, row_seats;

    IF no_more_rows THEN
        CLOSE pnr_cur;
        LEAVE the_loop;
    END IF;

    IF _seats < row_seats THEN
      UPDATE summary_pnr SET seats = seats - _seats WHERE id = row_id;
      INSERT tmp (pnr, seats) VALUES (row_pnr, _seats);
      SET _seats = 0;
      CLOSE pnr_cur;
      LEAVE the_loop;
    END IF;

    IF _seats >= row_seats THEN
      UPDATE summary_pnr SET seats = 0 WHERE ID = row_id;
      INSERT tmp (pnr, seats) VALUES (row_pnr, row_seats);
      SET _seats = _seats - row_seats;
    END IF;
   
  END LOOP the_loop;

  IF _seats > 0 THEN
      INSERT tmp (pnr, seats, err) VALUES (null, _seats, 'ERROR: NO SEATS');
      SELECT * FROM tmp;
      ROLLBACK;
  ELSE
      COMMIT;
      SELECT * FROM tmp;
  END IF;

END//

Open in new window



This almost close to what I need but Its not deducting properly
0
 

Author Comment

by:dorarishome
ID: 39654606
when i run:
select * from summary_pnr;

Open in new window


before calling the procedure its giving wrong result!
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:dorarishome
ID: 39654669
Hi,
I notice the data type too much affect the outcome
tid = int  ->key
id=varchar 20 ->stored in another table as reference and contain timestamp
pnr=varchar 20
seats=int 20

Thank you
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 39654757
so I need to understand your data.

My interpretation was that one id can have multiple pnrs, so that if a large order for seats comes in, it will take from several pnrs as long as the id remains the same, right?

What would help is for you to give me some sample data and your expected result.
0
 

Author Comment

by:dorarishome
ID: 39654777
Hi,
here is a dump to my table's sql:

CREATE TABLE `summary_pnr` (
  `tID` int(20) NOT NULL AUTO_INCREMENT,
  `id` int(20) NOT NULL,
  `pnr` varchar(20) DEFAULT NULL,
  `seats` int(20) DEFAULT NULL,
  `seats_org` int(20) DEFAULT NULL,
  PRIMARY KEY (`tID`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of summary_pnr
-- ----------------------------
INSERT INTO `summary_pnr` VALUES ('41', '1384257192', 'XYZ123', '-50', '100');
INSERT INTO `summary_pnr` VALUES ('42', '1384257192', 'ABC321', '-50', '50');
INSERT INTO `summary_pnr` VALUES ('43', '1384424767', 'pnr1', '100', '5');
INSERT INTO `summary_pnr` VALUES ('46', '1384528791', 'pnr1', '50', null);
INSERT INTO `summary_pnr` VALUES ('47', '1384528791', 'pnr2', '100', null);
INSERT INTO `summary_pnr` VALUES ('48', '1384530569', 'pnr1', '0', '100');
INSERT INTO `summary_pnr` VALUES ('49', '1384530569', 'pnr2', '50', '50');
INSERT INTO `summary_pnr` VALUES ('50', '1384530637', 'pnr1', '-25', '100');
INSERT INTO `summary_pnr` VALUES ('51', '1384530637', 'pnr2', '-25', '50');
INSERT INTO `summary_pnr` VALUES ('52', '1384530711', 'pnr1', '-1', '100');
INSERT INTO `summary_pnr` VALUES ('53', '1384530711', 'pnr2', '-1', '50');

Open in new window



and here is a scenario

I have booking table
I have summary table

when staff make a booking for customer->
booking data saved in booking table
then i need to deduct seats from summary_pnr
I first go to booking table and grab the package from which the booking was made
i.e. summary table have several packages from which booking made

once i reach the package-->pnr_id
I start to deduct according to your routine

my expected result would be if i make a booking for 110 seats from pnr id 1384528791
to deduct from 100 to be 0 then 10 from 50 to be 40
also i need a solution when some booking canceled


can i get your sk*pe

still i will finilize here
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 39654816
I think we somehow got confused with the id and the tid. Try this:

http://sqlfiddle.com/#!2/2e8a9d/1

CREATE TABLE `summary_pnr` (
  `tID` int(20) NOT NULL AUTO_INCREMENT,
  `id` int(20) NOT NULL,
  `pnr` varchar(20) DEFAULT NULL,
  `seats` int(20) DEFAULT NULL,
  `seats_org` int(20) DEFAULT NULL,
  PRIMARY KEY (`tID`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `summary_pnr` VALUES ('41', '1384257192', 'XYZ123', '-50', '100');
INSERT INTO `summary_pnr` VALUES ('42', '1384257192', 'ABC321', '-50', '50');
INSERT INTO `summary_pnr` VALUES ('43', '1384424767', 'pnr1', '100', '5');
INSERT INTO `summary_pnr` VALUES ('46', '1384528791', 'pnr1', '50', null);
INSERT INTO `summary_pnr` VALUES ('47', '1384528791', 'pnr2', '100', null);
INSERT INTO `summary_pnr` VALUES ('48', '1384530569', 'pnr1', '0', '100');
INSERT INTO `summary_pnr` VALUES ('49', '1384530569', 'pnr2', '50', '50');
INSERT INTO `summary_pnr` VALUES ('50', '1384530637', 'pnr1', '-25', '100');
INSERT INTO `summary_pnr` VALUES ('51', '1384530637', 'pnr2', '-25', '50');
INSERT INTO `summary_pnr` VALUES ('52', '1384530711', 'pnr1', '-1', '100');
INSERT INTO `summary_pnr` VALUES ('53', '1384530711', 'pnr2', '-1', '50');


CREATE PROCEDURE `reserve` (_pnr int, _seats int)
READS SQL DATA
BEGIN

  DECLARE no_more_rows BOOLEAN;
  DECLARE row_tid int default 0;
  DECLARE row_id int default 0; 
  DECLARE row_pnr varchar(4) default '';
  DECLARE row_seats int default 0;

  DECLARE pnr_cur CURSOR FOR
    SELECT
        tid, ID, PNR, SEATS
    FROM summary_pnr
    WHERE id = _pnr;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

  DROP TABLE IF EXISTS tmp;
  create temporary table tmp (
    tid int,
    id int,
    pnr varchar(4),
    seats int,
    err varchar(15)
    );

  START TRANSACTION;

  OPEN pnr_cur;

  the_loop: LOOP

    FETCH  pnr_cur
    INTO   row_tid, row_id, row_pnr, row_seats;

    IF no_more_rows THEN
        CLOSE pnr_cur;
        LEAVE the_loop;
    END IF;

    IF _seats < row_seats THEN
      UPDATE summary_pnr SET seats = seats - _seats WHERE tid = row_tid;
      INSERT tmp (tid, id, pnr, seats) VALUES (row_tid, row_id, row_pnr, _seats);
      SET _seats = 0;
      CLOSE pnr_cur;
      LEAVE the_loop;
    END IF;

    IF _seats >= row_seats THEN
      UPDATE summary_pnr SET seats = 0 WHERE tID = row_tid;
      INSERT tmp (tid, id, pnr, seats) VALUES (row_tid, row_id, row_pnr, row_seats);
      SET _seats = _seats - row_seats;
    END IF;
   
  END LOOP the_loop;

  IF _seats > 0 THEN
      INSERT tmp (pnr, seats, err) VALUES (null, _seats, 'ERROR: NO SEATS');
      SELECT * FROM tmp;
      ROLLBACK;
  ELSE
      COMMIT;
      SELECT * FROM tmp;
  END IF;

END//

Open in new window



Then, when that's done, run this command:

call reserve(1384528791,110);
select * from summary_pnr;
0
 

Author Comment

by:dorarishome
ID: 39654982
ok its working
but is it easy to create another function called 'return_seats'
i need to call this function when booking canceled.
lets say return_seats(1384257192,10);
Thank you
0
 
LVL 24

Assisted Solution

by:mankowitz
mankowitz earned 500 total points
ID: 39655008
That's not a simple question because you don't know which row gets the returned seats.
0
 

Author Closing Comment

by:dorarishome
ID: 39655054
Thank you,
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Creating and Managing Databases with phpMyAdmin in cPanel.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now