asked on

SQLite database works fine on dev machine but fails on live site

I've written a small bit of PHP to test reading data from an SQLite database. I created the database on my dev machine and when I try the PHP code from my dev web server all the data is read as I'd expect.

I've uploaded this to my live site, which is also running PHP5 and has SQLite3 support and when I try to read the table it fails. The database opens without error but when I do a query I get 0 rows back.

Dev platform is Windows 7 (and Linux if I change machines!) and live platform is Linux.

I made sure the DB file was uploaded as binary >:)

It was my understanding the a SQLite database file was platform portable (http://www.sqlite.org/onefile.html) and so I'm at a loss as to why this doesn't work.

My test code is here : http://crimsoncatz.com/dbtest.php

It's outputting phpinfo and then trying to read the data from the DB to generate a table. If all goes well it should look something like this...
http://crimsoncatz.com/nails.php

Basically, I've moving from having prices embedded in static HTML to being read from the DB file. I'm using SQLite as the data is small and we don't want the extra cost we'd need to pay for MySQL services.

I've attached a copy of the database file zipped.

I'd really like to try and get this working so help would be appreciated.

Note. I am not a PHP guru, this is my first time coding in it.

Thanks.

Dave Baldwin

According to this page, http://us.php.net/manual/en/book.sqlite.php , Sqlite2 and Sqlite3 require different functions in PHP. It looks like your PHP at http://crimsoncatz.com/dbtest.php supports both. Did you set access permissions on the live site so that your database can be read and written?

evilrix

ASKER

>> Did you set access permissions on the live site so that your database can be read and written?
When I tested this, by creating some test data I was able to create, write and read back and drop the table on the live site.

>> It looks like your PHP at http://crimsoncatz.com/dbtest.php supports both.
As I understand it -- please correct me if I am wrong, I am using PDO and this should take care of talking to the SQLite layer. The live site and my dev site are both PHP5 and, as I understand it, SQLite3 comes bundled with both. The phpinfo of the live site suggests it has SQLite3 support (although, not the exact version as my dev site it is a few point revisions out... I wouldn't expect the file format to have changed).

evilrix

ASKER

>> I was able to create, write and read back and drop the table on the live site.
Although... when I uploaded the file the perms might not be correct... so let me check and get back to you :)

evilrix

ASKER

>> so let me check and get back to you
File permissions look fine. (rw-rw-rw-)

Dave Baldwin

You have an error message at the bottom of the PHPinfo page.
"Caught exception: Unable to read treatments from database. "
If you are able to R/W the database, maybe your page code has a problem. Can you post that (edited to keep out username and password of course)?

evilrix

ASKER

>> "Caught exception: Unable to read treatments from database. "
Yes, that's my code generating that. See below.

I'm just wondering if it's because the SQLite on the live server is older than dev and the formats for the files have changed. Do you know if there is an easy way to programatically export and then reimport a database like you can in MySQL? If so, that's be fine as a solution.

$dbh = new PDO('sqlite:db/products.db');

$rows = $dbh->query("select * from treatments order by name;");
		
if(!$rows)
{
	throw new Exception('Unable to read treatments from database.');
}

Open in new window

ASKER CERTIFIED SOLUTION

Dave Baldwin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

evilrix

ASKER

The error: Array ( [0] => HY000 [1] => 11 [2] => malformed database schema - near "NO": syntax error )

evilrix

ASKER

Sorted :)

http://crimsoncatz.com/dbtest.php

evilrix

ASKER

Ok, so this is what I did to fix it...

I used the SQLite client with the .dump option to generate an SQL output dump of the tables. I then tried importing this using PHP (just read the text file of SQL in and used PDO exec to run it) and it failed. I used the PDO::errorInfo() to try and see why and the error I got was the same as the error above : malformed database schema - near "NO".

I checked the SQL and found I had an update foreign key constraint of "NO ACTION". Since this was the only use of the word NO in the SQL I removed this constraint and re-generated the database and it worked perfectly. I can live without the constraint :)

Although I figured this out myself in the end I couldn't have done it without you pointing me at PDO::erroInfo.

Thank you.

Dave Baldwin

Glad to help. I think you need to add this right after the <head> tag because I don't get the 'pound' symbol unless I force the character encoding to Latin1.

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">

Open in new window

Dave Baldwin

And when I looked at the SQLite web site, there are some things that changed with one of the recent versions.

evilrix

ASKER

>> I think you need to add this right after the tag

Oooh, thanks for that. I'm still learning as I go with this web dev stuff so that's a great tip. Cheers.