All times are UTC + 1 hour




Post new topic Reply to topic  [ 1 post ] 
  Print view

RollBack Commit working example (engine=innodb)
Author Message
PostPosted: Mon Oct 02, 2017 3:30 pm 
Regular
User avatar
Offline

Joined: Thu Aug 25, 2011 9:31 pm
Posts: 303
Location: Mumbai, India
RollBack Commit working example (engine=innodb)

Code:
<?php
/*

Fri.29.Sep.2017 rollback_commit.php

copy this php in any app and run  config.inc.php db-settings will be taken

https://www.w3resource.com/mysql/mysql-transaction.php
MySQL and the ACID Model
--------------------------------------

following tables for this example  engine=innodb <===strictly

DROP TABLE IF EXISTS `pubhol`;
CREATE TABLE `pubhol` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `holdt` varchar(20) COLLATE latin1_general_ci NOT NULL DEFAULT '',
  `holremarks` varchar(30) COLLATE latin1_general_ci DEFAULT NULL,
  `last_changed_by` varchar(15) COLLATE latin1_general_ci DEFAULT '',
  `last_change_date` varchar(20) COLLATE latin1_general_ci DEFAULT '',
  `last_change_ip` varchar(20) COLLATE latin1_general_ci DEFAULT '',
  `created_by` varchar(15) COLLATE latin1_general_ci DEFAULT '',
  `created_date` varchar(20) COLLATE latin1_general_ci DEFAULT '',
  `created_ip` varchar(20) COLLATE latin1_general_ci DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `holdt` (`holdt`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

DROP TABLE IF EXISTS `qry`;
CREATE TABLE `qry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `material_type` varchar(10) COLLATE latin1_general_ci DEFAULT 'FG',
  `date_from` varchar(10) COLLATE latin1_general_ci DEFAULT '',
  `date_to` varchar(10) COLLATE latin1_general_ci DEFAULT '',
  `remark` varchar(500) COLLATE latin1_general_ci DEFAULT '',
  `last_changed_by` varchar(15) COLLATE latin1_general_ci DEFAULT '',
  `last_change_date` varchar(20) COLLATE latin1_general_ci DEFAULT '',
  `last_change_ip` varchar(20) COLLATE latin1_general_ci DEFAULT '',
  `created_by` varchar(15) COLLATE latin1_general_ci DEFAULT '',
  `created_date` varchar(20) COLLATE latin1_general_ci DEFAULT '',
  `create_ip` varchar(20) COLLATE latin1_general_ci DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;


*/


  $config_atkroot = "./";
  include_once("atk.inc");

      $db = &atkGetDb();
      $user = getUser(); //$user[userid]

    //30.Sep.2017 ref atk/db/classatkdb.inc
      $haltonerror_org = $db->m_haltonerror;
      $db->m_haltonerror = false; //#IMP#otherwise Uniq Key Constraint error is thrown ut with abort
      //$db->m_haltonerror = $haltonerror_org; //#IMP# RESTORE orgval otherwise Uniq Key Constraint error is thrown ut with abort

      $qry = "SET AUTOCOMMIT=0;";
      $rows = $db->getrows($qry);

      $qry = "START TRANSACTION;";
//      $rows = $db->getrows($qry); //somehow this is given error

      $flag = true;
      //-- 1st qry
      $qry = "INSERT INTO qry (material_type) VALUES ('rollbacktest')";
      $result = $db->getrows($qry);
      //if ($db->hasError() && $db->getErrorType() != "user")
      if ($db->hasError())
        {
          //triggerError($record, null, '', $db->getErrorMsg());
          $msg1="Qry1False".$db->getErrorMsg();
          $flag = false;
        }

      //-- 2nd qry  This qry has uniq constraints
      $qry = "INSERT INTO pubhol (holdt) VALUES ('rollbacktest')";
      $result = $db->getrows($qry);
      //if ($db->hasError() && $db->getErrorType() != "user")
      if ($db->hasError())
        {
          //triggerError($record, null, '', $db->getErrorMsg());
          $msg1="Qry2False".$db->getErrorMsg();
          $flag = false;
        }



    if ($flag) {$db->commit(); $msg = "Commit happened" ;}
    else {$db->rollback(); $msg = "RollBack happened"; }

    $db->m_haltonerror = $haltonerror_org; //#IMP# RESTORE orgval otherwise Uniq Key Constraint error is thrown ut with abort

    echo $msg . '<br><br>' . $msg1 . '<br><br>' . $msg2 . '<br><br>';


    //to check post rollback commit if this works
    //atkmysqlidb.inc->doConnect()-> mysqli_autocommit($this->m_link_id, TRUE);
      //$db = &atkGetDb(); //resets autocommit no does NOT work that way

      $qry = "SET AUTOCOMMIT=1;"; //resets autocommit  otherwise everyttime $db->commit();
      $rows = $db->getrows($qry);


      //Now autocommit=1 always settings restored.  so check once if it is inserting as standalone qry
      $qry = "INSERT INTO qry (material_type) VALUES ('rollbacktest')";
      $result = $db->getrows($qry);
     // $db->commit();
    echo "postRollBackCommit qry works"  . '<br><br>' ;


    return;



Top
 Profile  
 

Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 

All times are UTC + 1 hour


Who is online

Users browsing this forum: No registered users and 8 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group