executing multiple sqlite queries at once

[IchBistTod]

I was wondering how to execute multipule queries with one command.

Below shows what I would liek to be able to do but for some reason is failing

_SQLite_Exec (-1, "INSERT INTO TABLE(one,two)VALUES ('one','two');INSERT INTO TABLE(one,two)VALUES ('one','two');")

[zorphnog]

You can do it using the method that you provided. Your SQL is incorrect though. You are not specifying the table name. Should be something like:

_SQLite_Exec (-1, "INSERT INTO my_table_name (one,two) VALUES ('one','two');INSERT INTO my_table_name (one,two) VALUES ('one','two');")

I've run statements like this before without any problem:

$sCommand = "BEGIN;" & _
    "CREATE TABLE sup (id INTEGER NOT NULL PRIMARY KEY, title TEXT, type TINYINT, severity TINYINT, iavm TINYTEXT, rt_id TINYTEXT, gd_id TINYTEXT, description MEDIUMTEXT, enabled BOOLEAN, forced BOOLEAN, logic MEDIUMTEXT, installed BOOLEAN, sequence INTEGER);" & _
    "CREATE TABLE sup_type (id INTEGER NOT NULL PRIMARY KEY, type TINYTEXT);" & _
    "CREATE TABLE install (id INTEGER NOT NULL PRIMARY KEY, sup_id INTEGER NOT NULL CONSTRAINT fk_install REFERENCES sup(id), sequence TINYINT, type TINYINT, data1 TEXT, data2 TEXT, data3 TEXT, data4 TEXT);" & _
    "CREATE TABLE install_type (id INTEGER NOT NULL PRIMARY KEY, action TINYTEXT);" & _
    "CREATE TABLE verify (id INTEGER NOT NULL PRIMARY KEY, sup_id INTEGER NOT NULL CONSTRAINT fk_verify REFERENCES sup(id), type TINYINT, data1 TEXT, data2 TEXT, data3 TEXT, data4 TINYINT, data5 TINYINT, data6 TINYINT, verified BOOLEAN);" & _
    "CREATE TABLE options (id INTEGER NOT NULL PRIMARY KEY, option TEXT, setting TEXT);" & _
    "CREATE TRIGGER fki_verify_sup_id BEFORE INSERT ON verify FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table [verify] violates foreign key constraint [fki_verify_sup_id]') WHERE (SELECT id FROM sup WHERE id = NEW.sup_id) IS NULL;END;" & _
    "CREATE TRIGGER fku_verify_sup_id BEFORE UPDATE ON verify FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table [verify] violates foreign key constraint [fku_verify_sup_id]') WHERE (SELECT id FROM sup WHERE id = NEW.sup_id) IS NULL;END;" & _
    "CREATE TRIGGER fki_install_sup_id BEFORE INSERT ON install FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table [install] violates foreign key constraint [fki_install_sup_id]') WHERE (SELECT id FROM sup WHERE id = NEW.sup_id) IS NULL;END;" & _
    "CREATE TRIGGER fku_install_sup_id BEFORE UPDATE ON install FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table [install] violates foreign key constraint [fku_install_sup_id]') WHERE (SELECT id FROM sup WHERE id = NEW.sup_id) IS NULL;END;" & _
    "CREATE TRIGGER fkd_sup_id BEFORE DELETE ON sup FOR EACH ROW BEGIN DELETE FROM verify WHERE sup_id = OLD.id;DELETE FROM install WHERE sup_id = OLD.id;END;" & _
    "PRAGMA user_version = " & $g_DB_iVersion & ";" & _
    "COMMIT;"

If _SQLite_Exec($g_DB_Package, $sCommand) <> $SQLITE_OK Then Return SetError(1, 0, -1)

Edited May 13, 2010 by zorphnog

[jchd]

What is failing? What do you get as error, or stdout error trace? What is the schema of your base?

I see no problem beside using a reserved word as table name. Beware that the current behavior (SQLite benevolently turning a reserved word into a user name might not work in future versions). It's possible that the next-to-come 3.7.0 will stop being laxist on some syntax points, like single quotes being used instead of double quotes or square parenthesis and such blatant misuse of the syntax.

Edit: @ zorphnog: the syntax you give _is_ invalid because the TABLE reserved name can't appear in an insert statement.

I tried creating a table named table (and it worked), but SQLite barks on the inserts when such a tablename is used. That's why you see an error. Use a normal user-specified tablename and everything will work fine.

Alternatively, if you absolutely insist in naming a table TABLE you can still use the normal syntax:

_SQLite_Exec (-1, "INSERT INTO ""TABLE"" (one,two) VALUES ('one','two');INSERT INTO ""TABLE"" (one,two) VALUES ('one','two');")

_SQLite_Exec (-1, "INSERT INTO [TABLE] (one,two) VALUES ('one','two');INSERT INTO [TABLE] (one,two) VALUES ('one','two');")

Using the protective "..." or [...] you can use whatever table name you want. This will work:

_SQLite_Exec (-1, "INSERT INTO [insert into Žíšov БОЛЬШОЕ ГРИДИНО МЫТИЩИ-ДТИ نسيّ عربيّ] (one,two) VALUES ('one','two');")

with the part in blue being you table name.

Edited May 13, 2010 by jchd