• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Detecting Model->delete() query result - how to detect error?

#1
The documentation is very sparse (= non-existent) on Model->delete() method error handling. I found out the hard way that when I do not use soft delete and the actual query fails – with, for instance, MySQL 1451 foreign key constraint –, Model->delete() does not return false, but a BaseResult object. But this BaseResult object does not seem to be very helpful, or at least violates clean code principles.

I looked at $BaseResult->resultID property, which seems to be either boolean true or false. this is confusing already. To further obfuscate its real purpose, the source code phpdoc type hint for resultID suggests that it should be a resource or object. So it might be an indicator of failed query or might not.

Then I looked at connID property, which, if I am not mistaken, is the actual database connection object (mysqli in my case). It eventually contains the last errno/message, but it would not be a good practice to build my app on implementation detail that should be hidden by BaseResult class.

Could not find any other property or method that would contain true/false or error code/message.

Please advise. Thanks in advance.
Reply

#2
You can catch those exceptions in your controller by doing something like:



PHP Code:
try {
   if ($foo->delete($id)) {
      echo 'success';
   } else {
      echo 'ooops';
   }
} catch (\
Exception $e) {
   $yourException $e->getMessage();

Reply

#3
The above example is wrong, it will not detect a failed query.

The easiest way to reproduce a failed query is, for instance, a foreign key constraint on the table you are trying to delete from.

SQL:
Code:
CREATE TABLE `blogpost` (
   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `content` text,
   PRIMARY KEY (`id`)
);
INSERT INTO blogpost SET content = 'this is my first blogpost';

CREATE TABLE `comment` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `blogpost_id` bigint(20) unsigned DEFAULT NULL,
  `comment` text,
  PRIMARY KEY (`id`),
  KEY `comment_fk_blogpost_id` (`blogpost_id`),
  CONSTRAINT `comment_fk_blogpost_id` FOREIGN KEY (`blogpost_id`) REFERENCES `blogpost` (`id`) ON UPDATE CASCADE
);
INSERT INTO comment SET blogpost_id = 1, comment = 'Wow great! Welcome to the blogger community!';

Doing a delete query on the above blogpost table row will result in SQL error:
Code:
MariaDB [blog]> SELECT * FROM blogpost;
+----+---------------------------+
| id | content                   |
+----+---------------------------+
|  1 | this is my first blogpost |
+----+---------------------------+
1 row in set (0.00 sec)

MariaDB [blog]> SELECT * FROM comment;
+----+-------------+----------------------------------------------+
| id | blogpost_id | comment                                      |
+----+-------------+----------------------------------------------+
|  1 |           1 | Wow great! Welcome to the blogger community! |
+----+-------------+----------------------------------------------+
1 row in set (0.00 sec)

MariaDB [blog]> DELETE FROM blogpost WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`blog`.`comment`, CONSTRAINT `comment_fk_blogpost_id` FOREIGN KEY (`blogpost_id`) REFERENCES `blogpost` (`id`) ON UPDATE CASCADE)

Now let's see what happens in CI4.
Controller code:
PHP Code:
<?php
namespace App\Controllers;
use 
CodeIgniter\Controller;

class 
BlogPostController extends Controller
{
    public function delete(int $id)
    {
        $model = new BlogPostModel();

        try {
            if ($result $model->delete($id)) {
                print "success\n";
                print "... or is it?\n";
                print_r($result);
            } else {
                print "ooops\n";
            }
        } catch (\Exception $e) {
            print "Caught exception: " $e->getMessage() . "\n";
        }
    }



And the result:
Code:
[email protected]:~/public$ php index.php blogpost/1/delete
success
... or is it?
CodeIgniter\Database\MySQLi\Result Object
(
    [connID] => mysqli Object
        (
            [affected_rows] => -1
            [client_info] => mysqlnd 5.0.12-dev - 20150407 - $Id: ************************* $
            [client_version] => 50012
            [connect_errno] => 0
            [connect_error] =>
            [errno] => 1451
            [error] => Cannot delete or update a parent row: a foreign key constraint fails (`blog`.`comment`, CONSTRAINT `comment_fk_blogpost_id` FOREIGN KEY (`blogpost_id`) REFERENCES `blogpost` (`id`) ON UPDATE CASCADE)
            [error_list] => Array
(...)
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.