I'm using Codeigniter transactions
$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->trans_complete();
This works fine , the problem I have is that inside the trans_start
and trans_complete
I'm calling other functions and those functions deals with database so they contains inserts and update and some deletes ... ex:
$this->db->trans_start();
$this->utils->insert_function($data);
$this->utils->update_function2($test);
$this->db->trans_complete();
Now if those functions are executed and some errors occur CodeIgniter won't do a rollback.
What is the best way to deal with such issue?
The only solution, I have in mind, is to return an error from those functions and inside those function add (trans_stat
and trans_complete
) And if it returns an error test an do $this->db->trans_rollback
ex:
$this->db->trans_start();
$result = $this->utils->insert_function($data);
if($result === false){
$this->db->trans_rollback();
}
$this->db->trans_complete();
Is there a better way of doing this?
Update 1:
As requested a sample of the external function i'm calling :
// insert_function contains
$rec = array(
'numero' => $numero,
'transaction_id' => $id,
'debit' => $product_taxes['amount_without_taxes'],
'date' => $data['date_transaction'],
);
$this->addExerciceAccountingRecords($rec);
and addExerciceAccountingRecords contains
function addExerciceAccountingRecords($records) {
$this->db->insert('transactions_exercices', $records);
}
Using
transactions
means supporting databases in inserting data safely. So in Codeigniter we write every database related functions in the Model not in Controller.. And in your second code(which is not working)you have pointed model on there.(utils
). So simple I'm sure this will not work. Because it's not an insert data with the model and Controller parallel. Transaction should be coded in the Model(I will write in Model in my answer).
Load this stuff as well
Assumptions
You have used $data
and $test
in your code as arrays. So, I assume there are two arrays for inserting and updating data.
Your data sets
$data = array(
'title' => 'My title',
'name' => 'My Name',
'date' => 'My date'
);
$id = 007;
$test = array(
'title' => $title,
'name' => $name,
'date' => $date
);
Your Code
$this->db->trans_start(); # Starting Transaction
$this->db->trans_strict(FALSE); # See Note 01. If you wish, you can remove as well
$this->db->insert('table_name', $data); # Inserting data
# Updating data
$this->db->where('id', $id);
$this->db->update('table_name', $test);
$this->db->trans_complete(); # Completing transaction
/*Optional*/
if ($this->db->trans_status() === FALSE) {
# Something went wrong.
$this->db->trans_rollback();
return FALSE;
}
else {
# Everything is Perfect.
# Committing data to the database.
$this->db->trans_commit();
return TRUE;
}
Notes
This is the same as CI3 (Above but). They added a few changes in CI4 (v4).
Keyword change in short
Feature | CodeIgniter 3 (CI3) | CodeIgniter 4 (CI4) |
---|---|---|
Start Transaction | $this->db->trans_start(); |
$this->db->transBegin(); |
Complete Transaction | $this->db->trans_complete(); |
$this->db->transCommit(); |
Rollback Transaction | $this->db->trans_rollback(); |
$this->db->transRollback(); |
Transaction Status | $this->db->trans_status(); |
$this->db->transStatus(); |
Error Handling | Transaction automatically rolled back on error if trans_complete() returns FALSE . |
Transactions automatically rolled back on error if transStatus() returns FALSE . |
Exception Handling | Not available | $this->db->transException(); |
For Error Handling
You can use a regular
if ($this->db->transStatus() === false) {
}
or try{}, catch {}
use CodeIgniter\Database\Exceptions\DatabaseException;
try {
$this->db->transException(true)->transStart();
$this->db->query('SQL QUERY...');
$this->db->transComplete();
} catch (DatabaseException $e) {
// Automatically rolled back already.
}
To Disable transactions
By default, it's on, as you know before. To set it off
$this->db->transOff();
...
For Testing
$this->db->transStart(true); # set this to true
This will roll back automatically.