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.
LVL 40
evilrixSenior Software Engineer (Avast)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
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?
evilrixSenior Software Engineer (Avast)Author Commented:
>>  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).
evilrixSenior Software Engineer (Avast)Author Commented:
>> 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 :)
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

evilrixSenior Software Engineer (Avast)Author Commented:
>> so let me check and get back to you
File permissions look fine. (rw-rw-rw-)
Dave BaldwinFixer of ProblemsCommented:
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)?
evilrixSenior Software Engineer (Avast)Author Commented:
>> "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

Dave BaldwinFixer of ProblemsCommented:
Can you try this in place of the Exception or maybe in addition?
    echo "\nPDO::errorInfo():\n";
I'm trying to get the actual error message.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
evilrixSenior Software Engineer (Avast)Author Commented:
The error: Array ( [0] => HY000 [1] => 11 [2] => malformed database schema - near "NO": syntax error )
evilrixSenior Software Engineer (Avast)Author Commented:
evilrixSenior Software Engineer (Avast)Author Commented:
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 BaldwinFixer of ProblemsCommented:
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 BaldwinFixer of ProblemsCommented:
And when I looked at the SQLite web site, there are some things that changed with one of the recent versions.
evilrixSenior Software Engineer (Avast)Author Commented:
>>  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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.