Hi, anyone able to help me with controlling database transaction over truncate table? The transaction not rollback when the insert fail. My code is as below:
$db->beginTransaction();
try {
if(!empty($system)){
$db->query("TRUNCATE TABLE systemmain");
}
$dataAfter = array ('name' => "Please Setup Company Information", 'keyId'=> "1");
$db->insert('systemmain', $dataAfter);
$db->commit();
}
catch (Exception $e) {
$db->rollBack();
$errorMsg[] = $e->getMessage();
}// end of catch exception

The MySQL docs (MySQL :: MySQL 5.0 Reference Manual :: 12.2.9 TRUNCATE Syntax) mention that "Truncate operations are not transaction-safe". Although it says that the truncate query should cause an error if in a transaction. This would still be my best guess at your problem.

Maybe you can use a "DELETE FROM systemmain WHERE 1" instead; it's not as performant though (especially on larger tables).

__________________
Brenton Alker
Brisbane, Australia

http://blog.tekerson.com/

Nice advice I try the DELETE FROM, hope it works