4 Replies - 5197 Views - Last Post: 01 November 2009 - 01:07 PM Rate Topic: -----

#1 n0c0d3  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 48
  • Joined: 21-December 08

SQLite query doesn't work

Post icon  Posted 01 November 2009 - 08:19 AM

I'm trying to learn PHP and sqlite from a tutorial which has the following code:
<?php
   $sqlite = new SQLiteDatabase('music.db') or die ("Could not open database");

   $sql = "SELECT artist_id, artist_name FROM artists";
   if ($result = $sqlite->query($sql)) {
	  if ($result->numRows() > 0) {
		 while($row = $result->fetch()) {
		 echo $row[0] . ":" . $row[1] . "\n";
			}
		 } else {
	  echo "No records matching your query were found.";
	  }
   } else {
	  echo "ERROR: Could not execute $sql. " . sqlite_error_string($sqlite->lastError());
   }

   unset($sqlite);
?>



Then I get this errormessage:

Quote

Fatal error: Uncaught exception 'SQLiteException' with message 'SQLiteDatabase::__construct() [<a href='sqlitedatabase.--construct'>sqlitedatabase.--construct</a>]: file is encrypted or is not a database' in D:\xampp\htdocs\sqlite\music.php:3 Stack trace: #0 D:\xampp\htdocs\sqlite\music.php(3): SQLiteDatabase->__construct('music.db') #1 {main} thrown in D:\xampp\htdocs\sqlite\music.php on line 3

I've checked the php.ini if the sqlite extension is "switched on", which it is. A few pages later in the book this same db-file is used with pdo, and then the querying works fine.

What could be going wrong?

Thank,
Bart

Is This A Good Question/Topic? 0
  • +

Replies To: SQLite query doesn't work

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4188
  • View blog
  • Posts: 11,857
  • Joined: 18-April 07

Re: SQLite query doesn't work

Posted 01 November 2009 - 10:23 AM

The music.db you are probably using is in the SQLite 3 version where the version and examples that are embedded in PHP are still using the older SQLite 2. This is the reason the PDO works fine since it uses version 3. So I recommend that you use PDO with that database until PHP can embed the latest version of SQLite. That or find a database that is in the SQLite version 2 format.

:)
Was This Post Helpful? 1
  • +
  • -

#3 n0c0d3  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 48
  • Joined: 21-December 08

Re: SQLite query doesn't work

Posted 01 November 2009 - 11:11 AM

View PostMartyr2, on 1 Nov, 2009 - 05:23 PM, said:

The music.db you are probably using is in the SQLite 3 version where the version and examples that are embedded in PHP are still using the older SQLite 2. This is the reason the PDO works fine since it uses version 3. So I recommend that you use PDO with that database until PHP can embed the latest version of SQLite. That or find a database that is in the SQLite version 2 format.

:)

I already thought it could be a version conflict. But I just noticed sqlite3 is included in the apache/bin directory of XAMPP I've installed. But some 2.8 version is there as well. I'll try it out when I've got some more time and I'll let you know if the issue is resolved.

Thanks for the help,
Bart
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5643
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: SQLite query doesn't work

Posted 01 November 2009 - 11:54 AM

Try "seeding" the thing. I don't know how the files was created and what security context it's running it. One way to be sure everything is on the same page is to create the file inside the PHP.

e.g.
<?php
	$db = openDatabase();
	showArtists($db);
	unset($db);

function showArtists($db) {
	$sql = "SELECT artist_id, artist_name FROM artists ORDER BY artist_name";
	if ($result = $db->query($sql)) {
		if ($result->numRows() > 0) {
			while($row = $result->fetch()) {
				echo $row[0] . ":" . $row[1] . "\n";
			}
		} else {
			echo "No records matching your query were found.";
		}
	} else {
		echo "ERROR: Could not execute $sql. " . sqlite_error_string($db->lastError());
	}
}

function addArtist($db, $name) { $db->queryExec ("INSERT INTO artists(artist_name) VALUES ('" . $name . "')"); }

function createTables($db) {
	$db->queryExec ("CREATE TABLE artists(artist_id INTEGER PRIMARY KEY, artist_name TEXT)");
}

function openDatabase() {
	$dbFile = realpath('music.db');
	echo $dbFile . "\n";
	$needDbCreate = !file_exists($dbFile);
	$db = new SQLiteDatabase($dbFile) or die((file_exists($dbFile)) ? "Unable to open" : "Unable to create");
	if($needDbCreate) {
		createTables($db);
		addArtist($db, 'Pink Floyd');
		addArtist($db, 'Beatles');
		addArtist($db, 'The Band');
	}
	return $db;
}

?>


Was This Post Helpful? 0
  • +
  • -

#5 n0c0d3  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 48
  • Joined: 21-December 08

Re: SQLite query doesn't work

Posted 01 November 2009 - 01:07 PM

It was the version conflict. I've created a new db-file with the sqlite2 from my xampp install and now the original script works fine with it.

And Baavgai, you made an interesting script. I just tried it and it works as well (of course) and I'll study some better to see how you tackled querying.

Thanks,
Bart
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1