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/ |