Having a little trouble with SQLite; running 5.1.4 as CGI with php_sqlite.dll, php_pdo.dll, and (unnecessarily) php_pdo_sqlite.dll enabled, under Apache 2.0.55 on XP Pro Corporate SP2.
I’d like my script to create a new table only if said table doesn’t already exist. I read the correct syntax is “IF NOT EXISTS,” and that this is usable in SQLite, but when I test it with this example code:
<?php
$db = new SQLiteDatabase("test.db");
$db->query("CREATE TABLE IF NOT EXISTS foo(id INTEGER PRIMARY KEY, name CHAR(255)");
?>
I get the following error (script location edited):
Warning: SQLiteDatabase::query() [function.SQLiteDatabase-query]: near "NOT": syntax error in test.php on line 5
What’s going wrong? I know there’s got to be a way to get this to work, but I can’t find it! I’m excited to get to work with SQLite, as other tests show SQLite’s the perfect database implementation for my application, but am completely stuck because of this error.
why not just submit the CREATE TABLE statement? the database should give you an error code (sorry, i’ve never worked with SQLite, don’t know if it does or not) that basically means “can’t create dis table, already gots it” so you just ignore that error
This’s what I’ve been doing as I proceed developmentally (I realize I unintentionally sounded like I’d been halted in the first message), ignoring the produced warning, but it’s frustrating me that I can’t tell if it’s a mistake by me or a bug in SQLite (?!) that’s causing the error; I don’t really want to use the script in production producing any errors, especially such a silly one!
@r937
Certainly not, it’s likely my being confusing!
That’s the whole problem: what’s causing the response? I’m using the call in a foreach, so the result’s a long string of “already exists” errors, even with “IF NOT EXISTS” included in the query; I’m not trying to hide the error, but correct it!
As well, I’ve come across a couple more snags with SQLite. The one I can’t figure is its throwing a
Warning: sqlite_query() [function.sqlite-query]: near "(": syntax error in test.php on line 10
(as well as the “already exists” error) when querying
"CREATE UNIQUE INDEX IF NOT EXISTS foo_index ON (name(20))"
the intent of which is to prohibit duplicate entries of the first twenty characters in the “name” column.
Perhaps it’s just that I was (only slightly) weaned on SQL syntax exclusively through MySQL, and these queries are in format incorrect for SQLite, the syntax to which I find isn’t anywhere documented as well as MySQL’s.
I haven’t, but only 'cos there isn’t one provided with the embedded implementation bundled with PHP, which is what I’m using.
The embedding is a mystery to me as well; the version provided with and used by PHP is 2.1(.*), but, while SQLite 3 is significantly different enough to assure drop-in incompatability without test, there were more versions released between this and 3. Is php_sqlite.dll just the same DLL provided by Hwaci or some specialized modification thereof, I wonder.
I think it’s fixed in newer versions of sqlite, but I remember having the same problem recently. I found no other solution than to supress the errors and then issue the query without IF NOT EXISTS. Not exactly optimal, and I can see that you already came to that conclusion your self.
Well, hell, that sure answered it. Three days figuring that it just must be some fatal discrepancy in my code causing the error, but it’s simply the use of syntax from the future!
Thanks kyberfabrikken and everyone, for the help; I guess I’m doomed to adapt.
