Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Is it possible to use transactions (and rollbacks) with sqlite3 drivers in PHP? I havn't found infos here: http://de2.php.net/manual/en/book.sqlite3.php

I dont want to use PDO...

Thanks for your help

Yes, transactions and rollbacks are possible even without PDO. Astonishing how difficult it is to find an example that explains how to accomplish this. Had to actually dig into some ready code to find out.

$db=new MyDB("database.db", SQLITE3_OPEN_READWRITE);
$db->exec('BEGIN;');
$stmt=$db->prepare('UPDATE table SET name = :name WHERE id = :id');
$stmt->bindValue(':id', $id, SQLITE3_INTEGER);
$stmt->bindValue(':name', $name, SQLITE3_TEXT);
$stmt->execute();
$db->exec('COMMIT;');
Source for logic: sombra2eternity, source for 'MyDB': php.net
                The difficult part is when to execute imo. A lot of room for error in that regard. I would also suggest wrapping this in an try catch with rollback in the catch.
– T.Woody
                Apr 25, 2019 at 18:28
a short extension to F-3000's answer above. if you create your own class, you can write your own wrapper functions as well. for example:
class MyDB extends SQLite3 {
  public function __construct(string $filename, int $flags = SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE, string $encryptionKey = '') {
    parent::__construct($filename, $flags, $encryptionKey);
    $this->exec('PRAGMA busy_timeout = 9900');
    $this->exec('PRAGMA encoding = "UTF-8"');
    // more pragmas ...
  public function enum(string $table, string $key, string $value) {
    $enum = array();
    $sql = "select distinct $table.$key, $table.$value
      from $table
      where $table.$key is not null";
    if ($key !== $value) {
      $sql .= "
        and $table.$value is not null";
    $sql .= "
      order by null";
    $result = $this->query($sql);
    if ($result !== false) {
      $row = $result->fetchArray(SQLITE3_NUM);
      while ($row !== false) {
        $enum[$row[0]] = $row[1];
        $row = $result->fetchArray(SQLITE3_NUM);
    return $enum;
  public function begin() {
    $this->exec('BEGIN');
  public function commit() {
    $this->exec('COMMIT');
  public function rollback() {
    $this->exec('ROLLBACK');
  // more functions ...
} // MyDB //
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.