Problems with commit() function

Question

I want to do a transaction with php and my sql with PDO, the thing is that when i use the commit() function, the code returns a "FALSE" echo, but insert the data anyway. Maybe im doing something wrong, my code is this:

In the Connection class

protected function getConexion()
{
    try 
    {
        $params = array(PDO::ATTR_PERSISTENT=>true,PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"); 
        $this->conexion = new PDO($this->motor.":host=".self::$servidor.";dbname=".$this->db_name,self::$usuario,self::$password,$params);
        return $this->conexion;
    } 
    catch (PDOException $ex) 
    {
        echo "Error en la conexión : " . $ex->getMessage();
    }
}

and into my register function

if($miembro == "S")
    {
        try 
        {
            $this->getConexion()->beginTransaction();
            
            $sqlInsertUsuario = "INSERT INTO Usuario (apodo, password, correo, nombre, apellido, "
                    . "fchNacimiento, sexo, fchCreacion, ultimaSesion, ip, llave, activo, "
                    . "imgPerfil, nivel)"
                    . " VALUES (:apodo, :password, :correo, :nombre, :apellido, "
                    . ":fchNacimiento, :sexo, NOW(), NOW(), 'Ip aqui', :llave, '1', "
                    . "'imagen aqui', 'I')";
            $sqlInsertCandidato = "INSERT INTO Candidato (correo) VALUES (:correo)";
            
            $sentencia = $this->getConexion()->prepare($sqlInsertUsuario);
            $sentencia->execute(array(':apodo' => $apodo, ':password' => $clave, ':correo' => $correo,
                ':nombre' => $nombre, 'apellido' => $apellido, 'fchNacimiento' => date($fchNacimiento),
                ':sexo' => $sexo, ':llave' => $llave));
            
            $sentencia = $this->getConexion()->prepare($sqlInsertCandidato);
            $sentencia->execute(array(':correo' => $correo));
            
            $this->getConexion()->commit();
            
            echo 'TRUE';
        } 
        catch (Exception $ex) 
        {
            $this->getConexion()->rollBack();
            echo 'FALSE';
        }
    }

the problem is that returns "FALSE" but the data is inserted in the table anyway.

Have you checked what the actual exception is?? Currently your code just ignores the useful information from $ex (in the second snippet I mean) — ADyson
– ADyson, Commented May 9, 2021 at 7:24
I get this message: Stack trace: #0 C:\wamp64\www\AsatruUruguayPrototipo\code\domain\Usuario.php(80): PDO->commit() #1 C:\wamp64\www\AsatruUruguayPrototipo\code\logic\RegistrarUsuario.php(13): Usuario->registarUsuario('Olaf232', 'Santi123', 'sdasdfff32@blal...', 'undefined', 'undefined', '2021/1/1', 'N', 'S') #2 {main}PDOException: There is no active transaction in C:\wamp64\www\AsatruUruguayPrototipo\code\domain\Usuario.php:80 — Santiago Amaro
– Santiago Amaro, Commented May 9, 2021 at 18:23

Santiago Amaro · Accepted Answer · 2021-05-09 19:00:03Z

1

I found the problem, the thing is that I was calling the connection too many times in this method: $this->getConexion()->beginTransaction();

so the solution was to create a variable "$pdo" that saves the connection and later will use it for all the transaction.

$pdo = $this->getConexion();
$pdo->beginTransaction();

instead of this:

$sentencia = $this->getConexion()->prepare($sqlInsertUsuario);
$sentencia = $this->getConexion()->prepare($sqlInsertCandidato);
$this->getConexion()->commit();

use this:

$sentencia = $pdo->prepare($sqlInsertUsuario);
$sentencia = $pdo->prepare($sqlInsertCandidato);
$pdo->commit();

answered May 9, 2021 at 19:00

Santiago Amaro

617 bronze badges

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

Collectives™ on Stack Overflow

Problems with commit() function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related