After several hours of playing with sqlite I am struck by how seemingly incompatiable the available editing tools are.
If I create a sqlite db using the sqlite manager firefox addon in firefox, the db is usuable from a PHP script or sqlite3 cmd line interface.
If I create a sqlite db using the sqlite cmd line interface , the db is usuable from a PHP script or sqlite manger interface.
If I create the db from within a php script, similarly the cmd line interface and sqlite manager for firefox think the db is junk.
So depending on the medium of creation, that seems to dictate usage.
Yes, I can persist with one method 'scripting from php', however I had assumed I could perhaps use the sqlite manager gui in firefox to design a schema, push a stack of data at the db from a php script, then do a bit of analysis from the cmd line tool. All seemed very logical, and a neat solution, however I am not sure why I may be experiencing this behaviour from sqlite.
I am using win xp.
Any feedback as to why this is so, would be greatly appreciated. Kind regards, Andrew
SQLite Database Usage Problems
Moderator: General Moderators
Re: SQLite Database Usage Problems
Define "unusable". Do you get an error message? I imagine the problem is with the permissions - SQLite is file based, so the user running whatever is accessing the file must have permission to access the SQLite database file.
Re: SQLite Database Usage Problems
Hello and thanks onion2k, I thought I had ruled out permissions, but you may be right.
1. running the cmd slqite_open from a php script on a db authored in sqlite manager or in the cmd line tool, results in the creation of a new db of the same name (therefore it is not recognising the existing file as being present even when placed in the web root directory.
2. similarly a new db is created with the same name, when access a db created in the sqlite manager or from a php script when accessed from the cmd line tool (there again no recognising the existance of the present file I believe).
3. accessing either a database created by a php script, or cmd line interface from with sqlite manager results in the following error dialog:
Error in opening file db.sqlite - perhaps this is not an sqlite db file
Exception Name: NS_ERROR_FILE_CORRUPTED
Exception Message: Component returned failure code: 0x8052000b (NS_ERROR_FILE_CORRUPTED) [mozIStorageService.openUnsharedDatabase]
but if I create and access the db from the same tool no problems.
I am unsure given the only permissions issue I could envisage may have been when running php scripts in the web root folder.
Any help would be appreciated.
1. running the cmd slqite_open from a php script on a db authored in sqlite manager or in the cmd line tool, results in the creation of a new db of the same name (therefore it is not recognising the existing file as being present even when placed in the web root directory.
2. similarly a new db is created with the same name, when access a db created in the sqlite manager or from a php script when accessed from the cmd line tool (there again no recognising the existance of the present file I believe).
3. accessing either a database created by a php script, or cmd line interface from with sqlite manager results in the following error dialog:
Error in opening file db.sqlite - perhaps this is not an sqlite db file
Exception Name: NS_ERROR_FILE_CORRUPTED
Exception Message: Component returned failure code: 0x8052000b (NS_ERROR_FILE_CORRUPTED) [mozIStorageService.openUnsharedDatabase]
but if I create and access the db from the same tool no problems.
I am unsure given the only permissions issue I could envisage may have been when running php scripts in the web root folder.
Any help would be appreciated.
Re: SQLite Database Usage Problems
Solved:
OK after a night of frustration and eliminating all else, I guessed it was an issue of the version of sqlite as I have a new PC as it was a fresh install.
As it turns out, the sqlite library functions only support sqlite version 2.x.
If you wish to use sqlite 3 branch versions you must use the PDO library for sqlite.
It wasn't until I arrived at http://us.php.net/manual/en/ref.pdo-sqlite.php and saw the reference to
Anyway as people do migrate to the latest version of sqlite, newbies and the well seasoned developers alike might waste their time before they figure it out.
I hope this helps fellow travellers.
Kind regards, Andrew
OK after a night of frustration and eliminating all else, I guessed it was an issue of the version of sqlite as I have a new PC as it was a fresh install.
As it turns out, the sqlite library functions only support sqlite version 2.x.
If you wish to use sqlite 3 branch versions you must use the PDO library for sqlite.
It wasn't until I arrived at http://us.php.net/manual/en/ref.pdo-sqlite.php and saw the reference to
, did it become apparent. No mention of the version 2 restriction when you are perusing the docs for sqlite library functions like sqlite_open etc, so you are misled to believe you are implementing things correctly whilst being frustrated by lack of progress when using the sqlite library functions.PDO_SQLITE is a driver that implements the PHP Data Objects (PDO) interface to enable access to SQLite 3 databases.
Anyway as people do migrate to the latest version of sqlite, newbies and the well seasoned developers alike might waste their time before they figure it out.
I hope this helps fellow travellers.
Kind regards, Andrew
Re: SQLite Database Usage Problems
Yeah, I ran into this myself. I opened the "bad" file in a text editor and saw it was a 2.x sqlite database, though. In the future, I may try using the other method so I can take advantage of SQLite 3.