asked on

transaction & rolback mysql function

following to this question: https://www.experts-exchange.com/questions/28295248/sql-help-needed-for-booking-system.html
how to alert when no more seats
and how to write a function to rollback sets after booking expired?
Thank you

mankowitz

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

dorarishome

ASKER

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

dorarishome

ASKER

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

dorarishome

ASKER

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

dorarishome

ASKER

please this is urgent if you can help as soon as possible to close it
Thank you

dorarishome

ASKER

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

dorarishome

ASKER

when i run:

select * from summary_pnr;

Open in new window

before calling the procedure its giving wrong result!

dorarishome

ASKER

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

mankowitz

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.

dorarishome

ASKER

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

ASKER CERTIFIED SOLUTION

mankowitz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dorarishome

ASKER

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

SOLUTION

mankowitz

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dorarishome

ASKER

Thank you,

transaction &amp; rolback mysql function

transaction & rolback mysql function