Uncaught PDOException for SQLite - unable to open database file

Question

I have a php web site with a SQLite database. This code opens and queries the database without error:

use App\SQLiteConnection;
$pdo = (new SQLiteConnection())->connect();
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$stmt = $pdo->query("SELECT empid, fullname FROM employees ORDER BY fullname");

while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
    $employees[] = [
        'empid' => $row['empid'],
        'fullname' => $row['fullname']
    ];
}

This code here:

use App\SQLiteConnection;
$pdo = (new SQLiteConnection())->connect();
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$training_id = $_POST['id'];

$stmt = $pdo->prepare("SELECT description from training WHERE id = :training_id");
$stmt->bindParam(':training_id', $training_id);
$stmt->execute();
echo $stmt->fetchColumn();

gets an error of:

Fatal error: Uncaught PDOException: SQLSTATE[HY000] [14] unable to open database file in C:\Bitnami\wampstack-7.3.10-0\apache2\htdocs\arborcircle\app\SQLiteConnection.php:23 Stack trace: 0 C:\Bitnami\wampstack-7.3.10-0\apache2\htdocs\arborcircle\app\SQLiteConnection.php(23): PDO->__construct('sqlite:db/EmpTr...') 1 C:\Bitnami\wampstack-7.3.10-0\apache2\htdocs\arborcircle\functions\get_training_description.php(10): App\SQLiteConnection->connect() 2 {main} thrown in C:\Bitnami\wampstack-7.3.10-0\apache2\htdocs\arborcircle\app\SQLiteConnection.php on line 23

Here is my SQLiteConnection class:

class SQLiteConnection {
    /**
     * PDO instance
     * @var type 
     */
    private $pdo;

    /**
     * return in instance of the PDO object that connects to the SQLite database
     * @return \PDO
     */


    public function connect() {
        if ($this->pdo == null) {
            $this->pdo = new \PDO("sqlite:" . Config::PATH_TO_DB_FILE);
        }
        return $this->pdo;
    }
}

Both of the examples of how I am querying the database seems similar to me, but I cannot see why the 2nd example is throwing an error of opening the same database.

Any help appreciated.

This is probably a namespace issue, In the SQLiteConnection you can try to place the absolute class name for Config::PATH_TO_DB_FILE which is probably either \Config::PATH_TO_DB_FILE or \App\Config::PATH_TO_DB_FILE or you may have two different scripts trying to open the same sqlite database at the same time, which may conflict — Nathanael

paravibe paravibe · Accepted Answer · 2019-10-15T14:40:57

Information to consider:

SQLite uses reader/writer locks on the entire database file. That means if any process is reading from any part of the database, all other processes are prevented from writing any other part of the database. Similarly, if any one process is writing to the database, all other processes are prevented from reading any other part of the database. For many situations, this is not a problem. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

Also try to add PDO::ATTR_PERSISTENT => true in your SQLiteConnection class:

public function connect() {
    if ($this->pdo == null) {
        $this->pdo = new \PDO(
            "sqlite:" .  Config::PATH_TO_DB_FILE, '', '',     
            array(
                PDO::ATTR_PERSISTENT => true
            )
        );
    }
    return $this->pdo;
}

Uncaught PDOException for SQLite - unable to open database file

1 Answers