php - PDO SQLite Cannot Open Database

725

I do alright with PHP in Linux environments, but I'm working on a PHP application that uses PDO's sqlite on IIS 8 on Windows Server 2012 R2.

Reading from the database works fine; attempting to insert or update results in the rather unhelpful error, "unable to open database file"

Code that works fine:

if (!($db = new PDO('sqlite:assets/database.db'))) { ?>
    ...
} else {
    $stmt="SELECT
             ... ";

    $stmt = $db->prepare($stmt);

    $stmt->execute(array(":person" => $this->uid));
}

Code that doesn't work:

if (!($db = new PDO('sqlite:assets/database.db'))) { ?>
    ...
} else {
    $stmt = "INSERT INTO bills(date, label, categ, amount, timestamp) VALUES(?, ?, ?, ?, ?)";

    $stmt = $db->prepare($stmt);
    var_dump($stmt->errorInfo()); 
    // yields : array(3) { [0]=> string(0) "" [1]=> NULL [2]=> NULL }

    $stmt->execute([$fData['date'], $fData['label'], $fData['categ'], $fData['amount'], $fData['timestamp']]);
    var_dump($stmt->errorInfo()); 
    // yields : array(3) { [0]=> string(5) "HY000" [1]=> int(14) [2]=> string(28) "unable to open database file" }
}

Things I've tried already:

  • Making sure the IIS user has read/write permissions of the database file, per this question
  • Making sure the IIS user has read/write permissions of the directory containing the database file. per this question
  • Check IIS error logs for any indication of what's going on. (The only thing present is an unrelated warning caused by this request failing.)
  • Making sure it works with another Database (it works fine with mySQL, but sqlite is a hard requirement for the final application.)

Any help greatly appreciated!

72

Answer

Solution:

I was having the same issue but working with Apache, turns out that the origin of the problem was the same.

Sqlite will need to create additional files in the directory to hold transactional data and since it does not have permission to write in the current folder it will give the that error while reading works fine.

175

Answer

Solution:

It turns out the issue was with the specific user account involved in running PHP on IIS. Apparently, the user is the IUSR, which is oddly not a part of the IIS_Users group. Thus, adding IUSR to the list of permitted users solved the problem, as described in this question, which I wish had come up in searches a few days ago.

People are also looking for solutions to the problem: php - Nginx root inside location not working

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.