php 8.1 + mysql 5.7: error handler in stored procedure dosen't work

Question

I have a stored procedure with which I try to insert some data into two tables but I don't want to insert duplicate keys. So I wrote the following procedure with the handling of error 1062: duplicate key. Here is the code:

DELIMITER $$
CREATE DEFINER=`root`@`localhost` 
PROCEDURE `insert_profilo_staff`(IN `id_persona` INT(11) UNSIGNED, IN `id_staff` VARCHAR(15) CHARSET utf8, IN `id_ruolo` VARCHAR(30) CHARSET utf8, IN `id_zona` VARCHAR(15) CHARSET utf8, IN `id_territorio` VARCHAR(15) CHARSET utf8, IN `gerarchia` INT(5) UNSIGNED, IN `ambito_giurisdizionale` VARCHAR(15) CHARSET utf8)
    MODIFIES SQL DATA
BEGIN
 
  
  DECLARE EXIT HANDLER FOR 1062 ROLLBACK;
  BEGIN
     SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT = "Il profilo che si sta cercando di inserire e' già associato all'utente.";

  START TRANSACTION; 
  
  IF gerarchia < 10000 THEN
    
      INSERT INTO tb_utenti_profili SET 
        id = id_persona,
        staff = id_staff, 
        ruolo = id_ruolo, 
        id_zona = id_zona;
        
     ELSE
     
      INSERT INTO tb_utenti_profili SET 
        id = id_persona,
        staff = id_staff, 
        ruolo = id_ruolo, 
        id_zona = id_zona;        
        
       INSERT INTO tb_utenti_territori_supervisori SET 
        id = id_persona,
        staff = id_staff, 
        id_territorio = id_territorio; 
        
     END IF;    
      -- SET @erroreOut = '00000';
     COMMIT;
     
   END;   
END$$
DELIMITER;

Then I execute the procedure with msqli inside a try catch block that you see below. But even if I insert non-existing n-uple into the DB I always get the error I managed inside the catch block. What is wrong?

   $db_connection = mysqli_connect($db_server, $db_username, $db_password, $db_name);
   $is_exception = 0;

   try {
          $query = "CALL insert_profilo_staff( ?, ?, ?, ?, ?, ?, ? ) ";
          $stmt = $db_connection->prepare($query);
          $stmt->bind_param("issssis", $inp_id_persona, $inp_id_staff, $inp_id_ruolo, $inp_id_zona, $inp_id_territorio, $inp_gerarchia, $inp_ambito_giurisdizionale);

          $inp_id_persona =  intval(trim($_POST['id_persona'] ?? ''));
          $inp_id_staff = trim($_POST['id_staff'] ?? '');
          $inp_id_ruolo = trim($_POST['id_ruolo'] ?? '');
          $inp_id_zona = trim($_POST['id_zona'] ?? '');
          $inp_id_territorio = trim($_POST['id_territorio'] ?? '');
          $inp_gerarchia = intval(trim($_POST['gerarchia'] ?? ''));
          $inp_ambito_giurisdizionale = trim($_SESSION['kaikan'] ?? '');

          $stmt->execute();
      }
      catch(Exception $e)
      {
            error_log( "l eccezione: " . $e->getMessage() );
            $is_exception = 1;
            error_log( "Call procedure insert_profilo_staff failed ");
            error_log( "Stato errore: " . $db_connection->sqlstate . " ");
            error_log( "N.ro errore: ". $db_connection->errno . " ");
            error_log( "Errore: " . $db_connection->error . " ");

            $result = 'error';
            $message= "" . $db_connection->error . " ";
            $mysql_data = [];
      }
      finally {
         $stmt->close();
         $db_connection->close();
         $query = "";
      }

        if ($is_exception == 0)
        {
           $result='success';
           $message='OK';
           $mysql_data = [];
        }

    $data = array(
        "result"  => $result,
        "message" => $message,
        "data"    => $mysql_data
        );

    // Convert PHP array to JSON array
    $json_data = json_encode($data);

Jeremy Caney · Accepted Answer · 2024-05-24 00:25:52Z

0

I resolved with the code's lines below in which I value the errno variable with the error number of the exception and then I'll read it when returning to Php. This is as suggested at the following link:

https://stackoverflow.com/a/46125452

DECLARE errno INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO;
    SELECT errno AS MYSQL_ERROR;
    ROLLBACK;
    END;

edited May 24, 2024 at 0:25

Jeremy Caney

7,798115 gold badges58 silver badges87 bronze badges

answered May 20, 2024 at 21:09

Marisa

11 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

php 8.1 + mysql 5.7: error handler in stored procedure dosen't work

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related