commit / rollback in two tables with PDO

Question

commit / rollback in two tables with PDO

Navigation

#1 by (2 votes)

1

First of all, the code below works, there is no error (but possibly can be improved). I have the following method in PHP:

<?php
public function ajustarUnidadeServidor($ajusteExercicio){
    try {
        $sqlInsert = "INSERT INTO tb_ajuste_exercicio ( siape_administrador,
                                                    siape_servidor,
                                                    id_unidade_anterior, 
                                                    id_nova_unidade) 
        VALUES (:siape_administrador,
                :siape_servidor,
                :id_unidade_anterior, 
                :id_nova_unidade);";

        $stmt = Conexao::instanciar()->prepare($sqlInsert);

        $stmt->bindValue(":siape_administrador", $ajusteExercicio);
        $stmt->bindValue(":siape_servidor", $ajusteExercicio);
        $stmt->bindValue(":id_unidade_anterior", $ajusteExercicio);
        $stmt->bindValue(":id_nova_unidade", $ajusteExercicio);

        $insert = $stmt->execute();

        $sqlUpdate = "UPDATE tb_servidor SET id_unidade_exercicio = :id_nova_unidade 
                    WHERE siape_servidor = :siape_servidor;";

        $stmt = Conexao::instanciar()->prepare($sqlUpdate);

        $stmt->bindValue(":id_nova_unidade", $idNovaUnidade);
        $stmt->bindValue(":siape_servidor", $siapeServidor);

        $update = $stmt->execute();


        return ($insert && $update);

    } catch (Exception $e) { 
        GeraLog::instanciar()->inserirLog("\nArquivo de origem: _ServidorCompletoDAO.php" . "\nCódigo do erro: " . $e-> getCode() . "\nMensagem: " . $e->getMessage());
    }
}
?>

My question is: how do I give commit and rollback in this code, considering that an error can occur in $sqlUpdate and I need to rollback the entire transaction? I have seen that I can $stmt->beginTransaction(); at the beginning and $stmt->commit(); or $stmt->rollBack(); on success / failure, but rollback will work for the two related tables?

php commit pdo rollback

asked by anonymous 30.09.2015 / 00:43

1 answer

How to take all the result of a for, and make it become a single variable Display 3 Models in a View

score 2 · Answer 1

Problem solved!

My mistake was trying to do stmt->commit() or stmt->rollBack() , where it was actually for me to have done this on the connection. I assigned the connection to a $conn variable and everything worked fine. My method looks like this:

public function ajustarUnidadeServidor($ajusteExercicio){
try {
    $sqlInsert = "INSERT INTO tb_ajuste_exercicio ( siape_administrador,
                                                    siape_servidor,
                                                    id_unidade_anterior, 
                                                    id_nova_unidade) 
        VALUES (:siape_administrador,
                :siape_servidor,
                :id_unidade_anterior, 
                :id_nova_unidade);";

    $conn = Conexao::instanciar();
    $conn->beginTransaction();

    $stmt = $conn->prepare($sqlInsert);
    $stmt->bindValue(":siape_administrador", $ajusteExercicio['siape_administrador']);
    $stmt->bindValue(":siape_servidor", $ajusteExercicio['siape_servidor']);
    $stmt->bindValue(":id_unidade_anterior", $ajusteExercicio['id_unidade_anterior']);
    $stmt->bindValue(":id_nova_unidade", $ajusteExercicio['id_nova_unidade']);
    $insert = $stmt->execute();


    $sqlUpdate = "UPDATE tb_servidor SET id_unidade_exercicio = :id_nova_unidade 
                    WHERE siape_servidor = :siape_servidor;";

    $stmt = $conn->prepare($sqlUpdate);
    $stmt->bindValue(":id_nova_unidade", $ajusteExercicio['id_nova_unidade']);
    $stmt->bindValue(":siape_servidor", $ajusteExercicio['siape_servidor']);
    $update = $stmt->execute();

    if($insert && $update){
        $conn->commit();
        return true;
    } else {
        $conn->rollBack();
        return false;
    }
} catch (Exception $e) { 
    GeraLog::instanciar()->inserirLog("\nArquivo de origem: _ServidorCompletoDAO.php" . "\nCódigo do erro: " . $e-> getCode() . "\nMensagem: " . $e->getMessage());
}
}