Light SQLite trouble.. ("IF NOT EXISTS")

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.

Any help is much, MUCH appreciated!

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!

no, wait, what’s wrong with getting an immediate, expected response message from the database and acting accordingly?

i’m being quite serious (i feel i should say that, because if you don’t know me, you’d think i was being sarcastic)

@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 found it pretty easy to find – http://www.sqlite.org/lang_createindex.html

and the syntax looks pretty easy too

you forgot the table name

:slight_smile: :slight_smile:

forgot the table name

Sadly, only due to editing in the copy-paste, and thus only in the post! :blush:

These docs’re the ones I’ve been using (here’s the “CREATE TABLE” page), was referring to, and am confused by. :sick:

have you tried issuing the statement not via php but rather via some front end application or command line or whatever

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’m not 100% sure on this, but I think that IF NOT EXISTS is mysql specific, and that might be the reason it’s not working on SQLLite.

But, as I said, I might be wrong…

yep, sorry, but you were wrong

have a look at the links in post #6 and #7

:slight_smile:

Hehe, I stand corrected :slight_smile:

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.