Read/write SQLite 3.x files in PHP 5???

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
myoung
Forum Newbie
Posts: 6
Joined: Wed Aug 23, 2006 6:49 pm
Location: San Rafael, CA

Read/write SQLite 3.x files in PHP 5???

Post by myoung »

I have installed Apache 2.2.2 and PHP 5.1.4 on a Mac OS 10.4.7 computer. My main reason for this installation is to serve up dynamic web pages with SQLite data that I create with REALbasic in the most current version. (The same problem occurs when I use SQLite files created with SQLite Database Browser 1.2.1, the terrific open-source app.)

I'm new to PHP so I'm probably making a simple mistake, but a seemingly trivial program produces fatal errors in trying to open a SQLite file. I have a sinking feeling that the problem might be different incarnations of SQLite. The version that apparently comes prepackaged with PHP 5 is SQLite 2.8, but I believe REALbasic uses SQLite 3 something.

In any event, here's the error message:

Warning: sqlite_open() [function.sqlite-open]: unable to open database: /test_data/Yields.rsd in /Library/WebServer/Documents/tyields.php

Here's the code snippet that fails on the line with $db:

Code: Select all

<html>
<body>
<?php
$db = sqlite_open("/test_data/Yields.rsd");
$query = "SELECT * FROM YieldData";
$result = sqlite_query($db,$query) or die("Query failed: ".sqlite_error());
$row = sqlite_fetch_array($result);
while($row=sqlite_fetch_array($result))
  {
  foreach($row as $value)
  {
  echo "$value<br>";
  }
  }
  sqlite_close($db);
  ?>
  </body>
  </html>



The database called Yields.rsd was created with SQLite and even when I remove the file from the test_data directory I get the same error when I believe the correct behavior ought to be creation of the database in lieu of opening an existing one.

Any ideas about what might be amiss? Are there any PHP experts around who have sample code from which we might learn? Thanks in advance.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Is this "test_data" directory in the root of your file system? The leading slash tells php to look only in the root as it's an absolute path then.
myoung
Forum Newbie
Posts: 6
Joined: Wed Aug 23, 2006 6:49 pm
Location: San Rafael, CA

Post by myoung »

/test_data is a directory in the same directory as the PHP program. I've also tested, without success, putting the Yields.rsd data file in the same directory as the PHP program, but it doesn't make a difference.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Try using "./test_data/Yields.rsd"

Note the leading dot I've added to your original.
myoung
Forum Newbie
Posts: 6
Joined: Wed Aug 23, 2006 6:49 pm
Location: San Rafael, CA

Post by myoung »

Nope. Now the error message reads:

Warning: sqlite_open() [function.sqlite-open]: file is encrypted or is not a database in /Library/WebServer/Documents/tyields.php on line 16
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

hmm, maybe "./test_data/Yields"
myoung
Forum Newbie
Posts: 6
Joined: Wed Aug 23, 2006 6:49 pm
Location: San Rafael, CA

Post by myoung »

Feyd,

Perhaps now we/you are making progress. The line which now produces an error is number 17, which if I've counted correctly is the line:

Code: Select all

if (sqlite_num_rows($result) > 0)
The error message is:

Warning: sqlite_query() [function.sqlite-query]: no such table: YieldData in /Library/WebServer/Documents/tyields.php on line 17

Fatal error: Call to undefined function sqlite_error() in /Library/WebServer/Documents/tyields.php on line 17

Also, what happens now is that a new file called simply "Yields" is created in the /test_data/ directory alongside the already existing "Yields.rsd" file that I have been trying to read. When I try to read "Yields" with the SQLite Database Browser, I get an error message saying that the "Yields" file is not a SQLite 3 file so I presume that my program has created this file with the SQLite 2.8 library.

Is this starting to make sense?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

It makes sense. It's difficult for a library to be future compatible. the SQLite page mentions that PDO can interact with SQLite 3.
myoung
Forum Newbie
Posts: 6
Joined: Wed Aug 23, 2006 6:49 pm
Location: San Rafael, CA

Post by myoung »

feyd,

Sorry about the misspelling on the last note.

I've seen mention of PDO as a way of bridging versions of databases. If I knew what PDO was, I probably could offer more insight. Perhaps, some PDO command must be invoked to tell PHP that it should use the SQLite3 library rather than the SQLite2 library.

When I check phpinfo(), I find that the "PDO support" is indicated as the "PDO driver" and that which is "enabled" is "sqlite2." So, perhaps I need to enable sqlite3, if there is such a thing.

In the directory /php-5.1.4/ext/pdo_sqlite/sqlite, I find two files named sqlite3.def and sqlite3.pc.in.

In the directory /scrlib/apr-util/dbd, I find two files named apr_dbd_sqlite3.lo and apr_dbd_sqlite3.o and a compiled file that is named apr_dbd_sqlite3.c.

Do these look suspicious? Are we closer to the treasure? :?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Well, here are the links for both pieces of detail.

http://php.net/ref.pdo
http://php.net/ref.pdo-sqlite - this page indicates that it's SQLite 3 first but offers backward support for 2.x

If your version doesn't like the 3 file, you may need to recompile or get a different build from somewhere. Sorry I can't be of too much help here; I don't work with SQLite or PDO currently.
myoung
Forum Newbie
Posts: 6
Joined: Wed Aug 23, 2006 6:49 pm
Location: San Rafael, CA

Post by myoung »

feyd,

Thanks for the terrific help. I'll pass this info about PDO along to the support folks at http://www.PHPmac.com. Perhaps they will know what to do about implementing SQLite3 with PDO.

Good night,
Mike
Post Reply