Welcome Guest, Not a member yet? Register   Sign In
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();

Website: marcomonteiro.net  | Blog: blog.marcomonteiro.net | Twitter: @marcogmonteiro | TILThings: tilthings.com
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:
dev@bloghost:~/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
#4

(This post was last modified: 08-17-2020, 06:43 AM by Gary.)

Bivanbi has a very good point... it seems like a pretty basic and important bit of functionality (in terms of one's faith in what is or isn't inside one's database) to be missing!!?

I also stumbled across this issue by accident... and although I'd previously noted the documentation on delete() (and many of the related Query Builder functions) was sparse, when I'd previously used it, having success confirmation wasn't particularly important... but what does one do when it is?

Is probing the Base Builder feedback the only means to establish what's really happening?
Reply
#5

Thank you bivanbi, I was struggling to get the exception error on delete from database, your solution helped me.
Reply
#6

The CodeIgniter Database has this built in also:

PHP Code:
if ( ! $db->simpleQuery('SELECT `example_field` FROM `example_table`'))
{
        $error $db->error(); // Has keys 'code' and 'message'

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#7

Thanks InsiteFX.

I see there is also a brief mention of return results here for regular queries (though not for ->update(), or ->delete() commands, where this thread started): https://codeigniter.com/user_guide/datab...s.html#id2. The text from the manual is: "When “write” type queries are run it simply returns TRUE or FALSE depending on success or failure."

I've not tested it yet, but I'd be inclined to want to believe a DELETE is a 'write' type query... so in concept, it should return TRUE if it is successful... though, this still leaves the question of when the query executes successfully, but finds nothing to delete (?).
Reply
#8

(08-21-2020, 08:06 AM)Gary Wrote: Thanks InsiteFX.

I see there is also a brief mention of return results here for regular queries (though not for ->update(), or ->delete() commands, where this thread started): https://codeigniter.com/user_guide/datab...s.html#id2.  The text from the manual is: "When “write” type queries are run it simply returns TRUE or FALSE depending on success or failure."

I've not tested it yet, but I'd be inclined to want to believe a DELETE is a 'write' type query... so in concept, it should return TRUE if it is successful... though, this still leaves the question of when the query executes successfully, but finds nothing to delete (?).

There is you need to scroll down the page.


PHP Code:
insert([$set NULL[, $escape NULL]])

Returns:    
TRUE on successFALSE on failure

---------------------------------------

update([$set NULL[, $where NULL[, $limit NULL]]])

Returns:    
TRUE on successFALSE on failure

---------------------------------------

delete([$where ''[, $limit NULL[, $reset_data TRUE]]])

Returns:    
BaseBuilder instance (method chaining) or FALSE on failure 
What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )
Reply
#9

(07-03-2020, 11:20 PM)bivanbi Wrote: 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:
dev@bloghost:~/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
(...)

Hi.. have you find a way to get affected rows or any message on success delete query (but no data been delete) on Model->delete on this problem (not the exists foreign key constraint) ?
Thanks.
Reply




Theme © iAndrew 2016 - Forum software by © MyBB