PHP 5.2.6 is creating SQLite 2.1 data files, which are unreadable by SQLite 3
Bill Hernandez
ms at mac-specialist.com
Wed Jul 16 22:11:18 PDT 2008
$ sudo port variants php5 | grep sqlite
---> sqlite: build sqlite support
$ sudo port installed | grep sqlite
---> php5 @5.2.6_1+apache2+fastcgi+ipc+macosx+mysql5+pcntl+pear
+postgresql83+readline+sockets+sqlite+tidy (active)
---> sqlite3 @3.5.9_0 (active)
$ php --version
---> PHP 5.2.6 (cli) (built: Jul 7 2008 15:05:26)
---> Copyright (c) 1997-2008 The PHP Group
---> Zend Engine v2.2.0, Copyright (c) 1998-2008 Zend Technologies
---> with Zend Debugger v5.2.12, Copyright (c) 1999-2007, by Zend
Technologies
When using SQLite Data Browser 1.3, I noticed that data files created
with PHP 5.2.6 generate the following error :
An error occurred: File is not a SQLite 3 database
When I open the SQLite data file created in the example below with
BBEdit, I noticed that PHP 5.2.6 is creating SQLite data files that
contain :
** This file contains an SQLite 2.1 database **
When I try to open a SQLite 3 data file with PHP 5.2.6, the following
error occurs:
Debug Warning: sqlite_demo.php line 25 - sqlite_open() [<a
href='function.sqlite-open'>function.sqlite-open</a>]: file is
encrypted or is not a database
----------------------------------------------
Sample code to generate SQLite data file
----------------------------------------------
<?php
$head = 'color: #0066CC; font-family: "comic sans ms"; font-size:
24px; font-weight: bold;' ;
echo "<span style='" . $head . "'>PHP - SQLite...</span>\n";
$dirpath = "./data/" ;
try
{
@mkdir ($dirpath, 0755, true);
if($php_errormsg != "") { throw new Exception("Data directory already
exists"); }
}
catch (Exception $e)
{
echo ("<br>Exception : " . $e->getMessage() . "<br>\n");
}
$filename = "sql_lite_db_08.db" ; // this was a sqlite version 3 data
file created with applescript
$filename = "sql_lite_db_09.db" ; // this is a sqlite version 2.1 data
file created below with PHP 5.2.6
$filepath = $dirpath . $filename ;
// +---------+---------+---------+---------+---------+
// open, or create new database (procedural interface)
// +---------+---------+---------+---------+---------+
// This needs to go before calling sqlite_open( )
$create_some_records = (! file_exists($filepath)) ? true : false ;
$db = sqlite_open("$filepath");
$tablename= "customers" ;
if($create_some_records)
{
// insert some sample data
$tablename = "books" ;
sqlite_query($db , "CREATE TABLE $tablename (id integer primary key,
title varchar(255) not null, author varchar(255) not null )");
sqlite_query($db, "INSERT INTO $tablename (title, author) values
('The Lord Of The Rings', 'J.R.R. Tolkien')");
sqlite_query($db, "INSERT INTO $tablename (title, author) values
('The Murders In The Rue Morgue', 'Edgar Allen Poe')");
sqlite_query($db, "INSERT INTO $tablename (title, author) values
('Three Men In A Boat', 'Jerome K. Jerome')");
sqlite_query($db, "INSERT INTO $tablename (title, author) values ('A
Study In Scarlet', 'Arthur Conan Doyle')");
sqlite_query($db, "INSERT INTO $tablename (title, author) values
('Alice In Wonderland', Lewis Carroll')");
$tablename = "customers" ;
sqlite_query($db , "CREATE TABLE $tablename (id INTEGER PRIMARY KEY,
fname CHAR(20), lname CHAR(20))");
sqlite_query($db, "INSERT INTO $tablename (fname, lname) VALUES
('Jane', 'Adams')");
sqlite_query($db, "INSERT INTO $tablename (fname, lname) VALUES
('Tom', 'Williams')");
sqlite_query($db, "INSERT INTO $tablename (fname, lname) VALUES
('Robert', 'Anderson')");
sqlite_query($db, "INSERT INTO $tablename (fname, lname) VALUES
('Anna', 'Meyers')");
}
$result = sqlite_query($db, "SELECT * FROM $tablename"); // execute
query
echo "<hr>" ;
echo "<h3>[$tablename] records</h3>\n";
echo "<pre>" ;
while ($row = sqlite_fetch_array($result, SQLITE_ASSOC))
{
print_r($row); // iterate through the retrieved rows
}
echo "</pre>" ;
sqlite_close($db); // close the database connection
// +---------+---------+---------+---------+---------+
// open, or create new database (object interface)
// +---------+---------+---------+---------+---------+
$header_style = 'color: #FF0000; background-color: #FFE4C4' ;
$tablename= "customers" ;
$db = new SQLiteDatabase($filepath);
$result_object = $db->unbufferedQuery("SELECT * FROM $tablename LIMIT
1");
$no_of_fields = $result_object->numFields(); // fetch the number of
fields
$s = "" ;
$s .= "<hr>" ;
$s .= "<h3>[$tablename] records</h3>\n";
$s .= "<table cellspacing='0' cellpadding='10' border='1' width='500'
bgcolor='#FFFFDD'>";
$s .= "<tr style='" . $header_style . "'>";
for ($i = 0; $i < $no_of_fields; $i++)
{
$field_name = $result_object->fieldName($i); // retrieve each
field
$s .= "<td><b>".$field_name."</b></td>";
}
$s .= "</tr>";
// Execute the query and retrieve all rows as an associated array
$result_array = $db->arrayQuery("SELECT * FROM $tablename",
SQLITE_ASSOC);
foreach($result_array as $key => $array)
{
$s .= "<tr>";
foreach($array as $k => $v)
{
$s .= "<td>".$v."</td>";
}
$s .= "</tr>";
}
$s .= "</table>";
echo $s ;
unset($db); // all done, destroy database object
// +---------+---------+---------+---------+---------+
// open, or create new database (object interface)
// +---------+---------+---------+---------+---------+
$db = new SQLiteDatabase($filepath);
$sql = "SELECT name FROM sqlite_master WHERE type = 'table' LIMIT 1";
$result_object = $db->unbufferedQuery($sql);
$no_of_fields = $result_object->numFields(); // fetch the number of
fields
$s = "" ;
$s .= "<hr>" ;
$s .= "<h3>[$filename] tables</h3>\n";
$s .= "<table cellspacing='0' cellpadding='10' border='1' width='500'
bgcolor='#FFFFDD'>";
$s .= "<tr style='" . $header_style . "'>";
for ($i = 0; $i < $no_of_fields; $i++)
{
$field_name = $result_object->fieldName($i); // retrieve each
field
$s .= "<td><b>".$field_name."</b></td>";
}
$s .= "</tr>";
$sql = "SELECT name FROM sqlite_master WHERE type = 'table'";
$result_array = $db->arrayQuery($sql, SQLITE_ASSOC);
foreach($result_array as $key => $array)
{
$s .= "<tr>";
foreach($array as $k => $v)
{
$s .= "<td>".$v."</td>";
}
$s .= "</tr>";
}
$s .= "</table>";
echo $s ;
unset($db); // all done, destroy database object
// +---------+---------+---------+---------+---------+
// open, or create new database (object interface)
// +---------+---------+---------+---------+---------+
$tablename= "customers" ;
$db = new SQLiteDatabase($filepath);
$result_object = $db->unbufferedQuery("SELECT * FROM $tablename LIMIT
1");
$no_of_fields = $result_object->numFields(); // fetch the number of
fields
echo "<hr>" ;
echo "<h3>[$tablename] field names</h3>\n";
$i = 0;
while ($i < $no_of_fields)
{
$field_name = $result_object->fieldName($i++); // retrieve each
field
echo $field_name."<br>\n";
}
unset($db); // all done, destroy database object
?>
Bill Hernandez
Plano, Texas
More information about the macports-users
mailing list