Achievo Community
http://www.atk-framework.com/forum/

RollBack Commit working example (engine=innodb)
http://www.atk-framework.com/forum/viewtopic.php?f=2&t=36389
Page 1 of 1

Author:  dgangal [ Mon Oct 02, 2017 3:30 pm ]
Post subject:  RollBack Commit working example (engine=innodb)

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;


Page 1 of 1 All times are UTC + 1 hour
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/