commit/rollback in two tables with PDO

Asked

Viewed 586 times

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 may occur in the $sqlUpdate and I need to rollback the entire transaction? I saw what I can do $stmt->beginTransaction(); at the beginning and $stmt->commit(); or $stmt->rollBack(); in case of success/failure, but the rollback will work for the two related tables?

php pdo commit rollback

  • The rollback() will undo/undo all instructions within that block(transaction). O commit() will put all changes of that transaction into effect. siape seems familiar to me that name.

  • Yes, that information I knew. My problem is: how to put this in my code, I only have the concepts but not a practical example that fits my situation. And @rray, SIAPE is a number that identifies public servants.

1 answer

2

Problem solved!

My mistake was trying to do stmt->commit() or stmt->rollBack(), where I was actually supposed to have done this in connection. I assigned the connection to a variable $conn and it all worked out. My method was 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());
}
}

Browser other questions tagged php pdo commit rollback

You are not signed in. Login or sign up in order to post.