SQLite Functions (PDO_SQLITE)
Introduction
PDO_SQLITE is a driver that implements the PHP Data Objects (PDO) interface to enable access to SQLite 3 databases.
In PHP 5.1, the SQLite extension also provides a driver for SQLite 2 databases; while it is not technically a part of the PDO_SQLITE driver, it behaves similarly, so it is documented alongside it. The SQLite 2 driver for PDO is provided primarily to make it easier to import legacy SQLite 2 database files into an application that uses the faster, more efficient SQLite 3 driver. As a result, the SQLite 2 driver is not as feature-rich as the SQLite 3 driver.
Note:
PDO_SQLITE allows using strings apart from streams together with
PDO::PARAM_LOB.
Installation
The PDO_SQLITE PDO driver is enabled by default. To disable, --without-pdo-sqlite[=DIR] may be used, where the optional [=DIR] is the sqlite base install directory.
Table of Contents
- PDO_SQLITE DSN — Connecting to SQLite databases
- PDO::sqliteCreateAggregate — Registers an aggregating User Defined Function for use in SQL statements
- PDO::sqliteCreateFunction — Registers a User Defined Function for use in SQL statements
- PHP Руководство
- Функции по категориям
- Индекс функций
- Справочник функций
- Расширения для работы с базами данных
- Уровни абстракции
- Объекты данных PHP
- CUBRID Functions (PDO_CUBRID)
- Microsoft SQL Server and Sybase Functions (PDO_DBLIB)
- Firebird Functions (PDO_FIREBIRD)
- IBM Functions (PDO_IBM)
- Informix Functions (PDO_INFORMIX)
- MySQL Functions (PDO_MYSQL)
- Microsoft SQL Server Functions (PDO_SQLSRV)
- Oracle Functions (PDO_OCI)
- ODBC and DB2 Functions (PDO_ODBC)
- PostgreSQL Functions (PDO_PGSQL)
- SQLite Functions (PDO_SQLITE)
- 4D Functions (PDO_4D)
Коментарии
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.
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.
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.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.