CODEWITHSUNDEEP
phpsqlitepdo
Ryan
Ryan

Reputation: 672

Uncaught PDOException for SQLite - unable to open database file

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.

Upvotes: 1

Views: 2716

Answers (1)

paravibe
paravibe

Reputation: 121

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;
}

Upvotes: 2

Related Questions