Sign in to follow this  
Followers 0
IchBistTod

executing multiple sqlite queries at once

3 posts in this topic

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');")

[center][/center][center]=][u][/u][/center][center][/center]

Share this post


Link to post
Share on other sites

#2 ·  Posted (edited)

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 by zorphnog

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by jchd

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0