Solved

transaction & rolback mysql function

Posted on 2013-11-15
14
317 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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
 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 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