PHP + POSTGRESQL (Begin, Commit, Rollback)

Question

In my application I have a certain process to perform test. This involves database with multi tables. I wonder if it is possible to start a BEGIN; make all manual development, and in case of error, perform a ROLLBACK;.

Note: Remembering that they will be in separate moments, not in the same process, only with the same connection.

Example:

passo 1: "BEGIN;".
passo 2: processor manuais com form em html
passo 3: "ROLLBACK;" para re-teste;

I did not find many things about this kind of situation and mostly it was recommended to delete in cascade or validation in Javascript, which is not possible.

Answer 1

Hello, you can use PDO, see this example. Source php.net

In some cases it is necessary to disable the auto commit of the databases (mysql/postgres), can be done using the constant PDO::ATTR_AUTOCOMMIT

It is also interesting to enable debug mode for error control with PDO::ATTR_ERRMODE and PDO::ERRMODE_EXCEPTION.

More PDO constants here: php.net

<?php
try {
  $dbh = new PDO('odbc:SAMPLE', 'db2inst1', 'ibmdb2', 
      array(PDO::ATTR_PERSISTENT => true));
  echo "Connected\n";
} catch (Exception $e) {
  die("Unable to connect: " . $e->getMessage());
}

try {
  $dbh->setAttribute(PDO::ATTR_AUTOCOMMIT,0);
  $dbh->setAttribute(PDO::ATTR_ERRMODE,1);
  $dbh->setAttribute(PDO::ERRMODE_EXCEPTION,1);

  $dbh->beginTransaction();
  $dbh->exec("insert into staff (id, first, last) values (23, 'Joe', 'Bloggs')");
  $dbh->exec("insert into salarychange (id, amount, changedate) 
      values (23, 50000, NOW())");
  $dbh->commit();

} catch (Exception $e) {
  $dbh->rollBack();
  echo "Failed: " . $e->getMessage();
}
?>

The real challenge of this model, would be to have separate database tables in the same transaction...I think q would be another issue.