If you receive an error while trying to write to a sqlite database (update, delete, drop):
Warning: PDO::query() [function.query]: SQLSTATE[HY000]: General error: 1 unable to open database
The folder that houses the database file must be writeable.
SQLite 関数 (PDO_SQLITE)
はじめに
PDO_SQLITE は、PHP から SQLite 2 や SQLite 3 データベースへのアクセスを可能にするための PHP Data Objects (PDO) インターフェイス を実装したドライバです。
注意:
PDO_SQLITE は、文字列と
PDO::PARAM_LOBのストリームを区別して使うことができます。
インストール手順
PDO_SQLITE PDO ドライバはデフォルトで有効になります。無効にするには
--without-pdo-sqlite[=DIR] を使います。
オプションの [=DIR] は sqlite の基底インストールディレクトリです。
PHP 7.4.0 以降では、» libsqlite ≥ 3.5.0 が必須です。
それより前のバージョンでは、[=DIR] が省略された場合は
バンドル済みの libsqlite が代わりに使われていましたし、
またそれがデフォルトでした。
注意: PHP 7.4.0 以降の、Windows における追加セットアップ
この拡張モジュールを動作させるには、 Windows システムの PATH が通った場所に DLL ファイルが存在する必要があります。 FAQ の "Windows で PHP のディレクトリを PATH に追加するにはどうすればいいのですか?" で、その方法を説明しています。 DLL ファイルを PHP のフォルダから Windows のシステムディレクトリにコピーしても動作します (システムディレクトリは、デフォルトで PATH に含まれるからです) が、これは推奨しません。 この拡張モジュールを使用するには、以下のファイルが PATH の通った場所にある必要があります。 libsqlite3.dll.
目次
- PDO_SQLITE DSN — SQLite データベースに接続する
- PDO::sqliteCreateAggregate — SQL 文で使用する集約ユーザー定義関数 (UDF) を登録する
- PDO::sqliteCreateCollation — SQL文で collate 関数として使うユーザー定義関数を登録する
- PDO::sqliteCreateFunction — SQL 文で使用するユーザー定義関数 (UDF) を登録する
add a note
User Contributed Notes 8 notes
aidan at php dot net ¶
18 years ago
ohcc at 163 dot com ¶
3 years ago
With PDO SQLite driver, calculation within an SQL with multiple ? may not get results as you expect.
<?php
// ....
$stmt = $PDO->prepare('SELECT * FROM `X` WHERE `TimeUpdated`+?>?');
$stmt->execute([3600, time()]);
$data = $stmt->fetchAll();
print_r($data);
?>
To get the right results, you have more than 3 solutions.
1. Change 'SELECT * FROM `X` WHERE `TimeUpdated`+?>?' to 'SELECT * FROM `X` WHERE `TimeUpdated`>?' and do the math using Php (ie: $stmt->execute([time()-3600]); ).
2. Use PdoStatement::bindParam or PdoStatement::bindValue, and set the parameter type to PDO::PARAM_INT.
3. Change 'SELECT * FROM `X` WHERE `TimeUpdated`+?>?' to 'SELECT * FROM `X` WHERE `TimeUpdated`+?>?+0', here '?+0' may be replaced by another math function or another calculation, such as 'abs(?)', you can even wrap both ? with a math calculation.
nospam8715 at dririan dot com ¶
11 years ago
Instead of compiling an old version of SQLite to create a database using an older database format that the version of SQLite bundled with PDO can handle, you can (much more easily) just run the query "PRAGMA legacy_file_format = TRUE;" BEFORE creating the database (if you have an existing database, run ".dump" from the sqlite shell on your database, run the sqlite shell on a new database, run the PRAGMA, then paste the contents of the .dump). That will ensure SQLite creates a database readable by SQLite 3.0 and later.
Duffalo ¶
17 years ago
Note that as of the date of this post, PDO_SQLITE will not interact with database files created with the current version of the SQLite console application, sqlite-3.3.6.
It is currently necessary to obtain version 3.2.8, available from http://www.sqlite.org/ but only by entering the URI manually, as there is no link. Go to http://www.sqlite.org/download.html and find the URI of the version you're looking for, then make the appropriate version number substitution.
chrislangton84 at gmail dot com ¶
8 years ago
This page has been out of date for some time - Installation specifically.
As of PHP 5.4 sqlite is no longer part of PHP and in only available through PECL
gopal at gopalarathnam dot com ¶
17 years ago
If you get an error reporting "invalid resource" when trying to query the database table and looping through it, the version of the SQLite extension compiled in to PHP might be incompatible with the version that had created the database (like SQLite 2.x vs 3.x).
The database open itself might be successful, failing only when querying.
$dbh = new PDO('sqlite:/tmp/foo.db'); // success
foreach ($dbh->query('SELECT * FROM bar') as $row) // prints invalid resource
// ...
tris dot php at tfconsulting dot com dot au ¶
9 years ago
After wrestling with "General error: 5 database is locked" errors for a highly concurrent project I finally wrapped the PDO transaction code with a semaphore. No errors since...
Obviously only works if all processes use the subclass and wrap database modifying statements in beginTransaction() .. commit(). The same could be achieved with flock() if semaphore is not available on your system but will be slower.
<?php
class SQLitePDO extends PDO {
function __construct($filename) {
$filename = realpath($filename);
parent::__construct('sqlite:' . $filename);
$key = ftok($filename, 'a');
$this->sem = sem_get($key);
}
function beginTransaction() {
sem_acquire($this->sem);
return parent::beginTransaction();
}
function commit() {
$success = parent::commit();
sem_release($this->sem);
return $success;
}
function rollBack() {
$success = parent::rollBack();
sem_release($this->sem);
return $success;
}
}
?>
gmac63 at charter dot net ¶
17 years ago
Issue:
Error: SQLSTATE[HY000]: General error: 1 unsupported file format
Resolution:
To solve this (and/or many issues) involving this type of error, I assumed the error to be generated from php. Well, it was to an extent. The sqlite pdo code offered the solution:
I researched the error by grep'ing the php source code and found the error string to come from php-5.1.4/ext/pdo_sqlite/sqlite/src/prepare.c, lines 265:278 :
/*
** file_format==1 Version 3.0.0.
** file_format==2 Version 3.1.3.
** file_format==3 Version 3.1.4.
**
** Version 3.0 can only use files with file_format==1. Version 3.1.3
** can read and write files with file_format==1 or file_format==2.
** Version 3.1.4 can read and write file formats 1, 2 and 3.
*/
if( meta[1]>3 ){
sqlite3BtreeCloseCursor(curMain);
sqlite3SetString(pzErrMsg, "unsupported file format", (char*)0);
return SQLITE_ERROR;
}
This is interesting as I am running SQLite version 3.3.5 which the databases were created in. I see that the SQLite PDO source in the php source is :
# cat ext/pdo_sqlite/sqlite/VERSION
3.2.8
My solution was then to find a version of sqlite that was =< 3.1.4. I found source for 3.1.3, compiled, recreated my database using that version (NOTE: the databases are unsupported between 3.1.x and 3.2.x versions of SQLite). Once I did this it worked.
Also as a side note, to get SQLite compiled as a PDO, I had to:
1) configure with
...
--enable-pdo=shared \
--with-sqlite=shared \
--with-pdo-sqlite=shared
--with-zlib
... \
'make && make install' if configure is successful.
2) Make sure the pdo libs were copied/installed to the correct directory. On my installation it was /usr/local/include/php/ext/pdo/
3) Make these changes in my php.ini:
- change ' extension_dir = "./" ' to ' extension_dir="/usr/local/include/php/ext/pdo/" '
- add/edit in this order:
extension=pdo.so
extension=pdo_sqlite.so
extension=sqlite.so
4) test php with : 'php -m' at the command line and solve any issues from there. Mostly php.ini config issues. Also restart the http service!
