TransWikia.com

Show MySQL errors that occur when I excute $wpdb->insert()

WordPress Development Asked by Diego Valverde on December 15, 2021

I have the same problem as Nathan ($wpdb->last_error doesn't show the query on error), obviously, the solution to the specific case was to change the type of data to the column that caused the error … but if the error were different, for example, an incorrect date, or a null value where the field does not support it, as I can show the error that is generated in the database when the $ wpdb-> insert ($ this-> table, $ data_) function is executed;

I have tried to do this by enclosing that function in a try {} catch {} but I don’t have the expected behavior. I share the example code I am using in the class-example.php file.

<?php

class Example{

function insert_example(&$msgError){
  $msgError='';
  global $wpdb;
  try{
      
      //throw new Exception('Help please!!!!'); //This works correctly
  
      $wpdb->query('SET AUTOCOMMIT = 0');// Specify 0 here
      // Start Transaction
      $wpdb->query( "START TRANSACTION" );
      //Inserto cabecera
      $resul_insert_01 = $wpdb->insert( 'wp_wc_xxxxxx',   
        array( 
          'column_01' => $arry001['column_01'], 
          'column_02' => $arry001['column_01'],
        )
      );
      if (!($resul_insert_01))
      {
        $wpdb->query( "ROLLBACK" );
        throw new Exception('No se pudo grabar la informacion en wp_wc_xxxxxx');
        //exit($e->getMessage());
        //exit();
      }

      $Id_new = $wpdb->insert_id;
      //Insert details 01
      foreach ($array002 as $arr002) {  
        $resul_insert_02 = $wpdb->insert(   'wp_wc_yyyyyy',   
                        array( 
                          'id_wp_wc_xxxxxx' => $Id_new, 
                          'column_01' => $arr002['column_01'], 
                          'column_02' => $arr002['column_02']
                        )
        );
        if (!($resul_insert_02))
        {
            $wpdb->query( "ROLLBACK" );
            throw new Exception('No se pudo grabar la informacion en wp_wc_yyyyyy');
            //exit($e->getMessage());   
            //exit();
        }
      }
      
      //Insert details 02
      foreach ($array003 as $arr003) {
        

            $resul_insert_03 = $wpdb->insert(   'wp_wc_zzzzzz',   
                            array( 
                              'id_wp_wc_xxxxxx' => $Id_new, 
                              'column_01' => $arr003["column_01"], 
                              'column_01' => '0000' //column length is 2, but try insert 4 characters
                            )
            );
        
        if (!($resul_insert_03))
        {

            $wpdb->query( "ROLLBACK" );
            throw new Exception('No se pudo grabar la informacion en wp_wc_zzzzzz');
        }   
      }
      
      //Confirmo transacción

      $wpdb->query( "COMMIT" );
      return true;
  }catch ( Exception $e ) {
    
    //$wpdb->query( "ROLLBACK" );
    $msgError = $e->getMessage();
    //echo $e->getMessage();
    return false;
  }
}

}

If I remove the comment from the line * // throw new Exception ('Help please !!!!'); *, if I get the expected behavior, that is, I catch the error in the variable $ msgError, but with the rest I can’t get the other error that occurs on the line that indicates * // column length is 2, but try insert 4 characters *
When I call the function from another side (test.php), it doesn’t detect the error and the code execution doesn’t progress, however there is no sample of any error message. What I want to do is, capture the error message that occurs in the database and insert it as a note in a woocommerce order.

One Answer

This doesn't answer your question directly, but the solution here is to validate the data before you chuck it at the database and pray.

Sending bad data 'down the line' so that something else can check it and report back to you if it wasn't what they were expecting is bad design. You don't know why the data was bad in the first place, and as in this case you're limited in the error reporting capabilities of whoever is actually checking the data for you and hoping that they'll give you the type of information you need.

All the effort you're putting here into trying to get Wordpress or MySQL to tell you what's wrong could be largely mitigated with just checking yourself: 1. what is the data you've got, 2. what is the datatype of the field it's going on? These things should be known at the same level in your code, don't just guess. If you don't know how to look up the data type of that column, that sounds like a big problem that's worth figuring out.

"Be conservative in what you send, be liberal in what you accept"

So you could write a couple of quick functions yourself, or use PHP things like is_numeric or date_parse_from_format to know that you've got the right data, and bail out safely before you hit the database. This is particularly a good idea because you might get data this is actually valid but doesn't mean some requirements you have. E.g. it could be a valid date, but 1000 years ago. Or it could be a string but 1Mb long, or 1 character long, and maybe you know that's invalid but the database wouldn't.

Sorry this isn't more helpful to answer your question directly - as someone did in the question you linked to, you might want to look at what the Wordpress code looks like you're calling to see what it's doing, or you could also look at writing your own INSERT statements and calling those in a simpler way that might get you more direct access to a returned error code.

Answered by mozboz on December 15, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP