Peter Jay Salzman wrote:
Hi all,
I'd like to write database agnostic SQL. The SQL itself isn't complicated,
so I'm hoping it's possible.
One thing that I can't seem to get around is auto_increment. According to
the SQLite FAQ, auto_increment can be simulated by declaring:
CREATE TABLE 'wheatblog_categories' (
'id' INTEGER PRIMARY KEY NOT NULL, <---- auto inc.
'category' VARCHAR(30) NOT NULL default ''
);
According to SQLite documentation, if I do:
INSERT INTO wheatblog_categories (id, category)
VALUES (null, '$the_category')
then the record id will be incremented.
If I understand MySQL documentation correctly (haven't looked at PostgreSQL
yet), this insert will cause an error. I take that to mean "INTEGER PRIMARY
KEY NOT NULL"'s aren't auto incremented when they're handed null values in
MySQL.
Is it possible to create this table in a database agnostic way? Or do I
need to keep separate SQL statements for SQLite and MySQL?
Also, I feel embarrased to ask, but is "INTEGER PRIMARY KEY NOT NULL" the
same thing as "INTEGER NOT NULL PRIMARY KEY"?
Thanks!
Pete
You can do
INSERT INTO wheatblog_categories (category)
VALUES ('$the_category')
which works in all databases.
John