improvement wish: be able to run several statement at a time (for sqlite)

[stonebig]

I don't know what is your design limitation,

example of typical wish:

-- SQLite Memo (Demo = click on green "->" and "@" icons)

-- to CREATE a table 'items' and a table 'parts' :
DROP TABLE IF EXISTS item; DROP TABLE IF EXISTS part;
CREATE TABLE item (ItemNo, Description,Kg  , PRIMARY KEY (ItemNo));
CREATE TABLE part(ParentNo, ChildNo , Description TEXT , Qty_per REAL);

-- to CREATE an index :
DROP INDEX IF EXISTS parts_id1;
CREATE INDEX parts_id1 ON part(ParentNo Asc, ChildNo Desc);

-- to CREATE a view 'v1':
DROP VIEW IF EXISTS v1;
CREATE VIEW v1 as select * from item inner join part as p ON ItemNo=p.ParentNo;

-- to INSERT datas
INSERT INTO item values("T","Ford",1000);
INSERT INTO item select "A","Merced",1250 union all select "W","Wheel",9 ;
INSERT INTO part select ItemNo,"W","needed",Kg/250 from item where Kg>250;



-- to use COMMIT and ROLLBACK :
BEGIN TRANSACTION;
UPDATE item SET Kg = Kg + 1;
COMMIT;
BEGIN TRANSACTION;
UPDATE item SET Kg = 0;
select Kg, Description from Item;
ROLLBACK;
select Kg, Description from Item;


-- to use SAVEPOINT :
SAVEPOINT remember_Neo;  -- create a savepoint
UPDATE item SET Description = 'Smith'; -- do things
SELECT ItemNo, Description FROM Item; -- see things done
ROLLBACK TO SAVEPOINT remember_Neo; -- go back to savepoint state
SELECT ItemNo, Description FROM Item;  -- see all is back to normal
RELEASE SAVEPOINT remember_Neo; -- free memory

I can contribute a home-made sql splitter, if it's the problem
https://github.com/stonebig/sqlite_bro/blob/master/sqlite_bro/sqlite_bro.py#L1393..L1493

[pbugnion]

Thanks for raising this.

Allowing multiple statements would be good. How would you expect this to behave if some of the statements return data?

[playermanny2]

@pbugnion coming in here, I also started to explore the idea of having multiple statement execution and would love the feature. specifically, for things like altering current schema in oracle db.

In terms of statements returning data, I would say it wouldn't make much sense to return multiple sets of data with it multiple execution statements, something like a sub queries should be used. To make it simple, i would say the last query should return data and any other statements should just execute.

[stonebig]

well, I'm creating a window each time there is a dataset return. like if executing in sql server front-end. big fan of sqlserver simplicity, or visadata application of last century