Support for multiple statements

[vetu11]

Hi, I'm looking for a way to do multiple statements in just one query. I use a file (like DatabaseSetup.sql) to set up the database in which I have all the statements required for it, but when passing it as text to query() only the first statement is executed.

Doing this is very helpfull, as I can write how the database is constructed in a separated file. Also, when dealing with database schema updates, having a file with all the statements that update it is very usefull.

Will this feature be supported in the future?

Thanks

[TGRCdev]

This seems to be an intentional behavior from SQLite itself. As such, I don't see much reason to change it. Not entirely true

To work around this, you could end each of your queries in your .sql file with semicolons, like such:

CREATE TABLE IF NOT EXISTS operating_systems (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);
INSERT INTO operating_systems (id, name) VALUES (0, 'Windows 10');
INSERT INTO operating_systems (id, name) VALUES (1, 'Ubuntu');
INSERT INTO operating_systems (id, name) VALUES (2, 'OSX');

then load the .sql file as text:

var sqlfile = File.new();
sqlfile.open("res://DatabaseSetup.sql", File.READ);
var sqltext = sqlfile.get_as_text();

and split the queries into an array by the semicolons. Then pass each query to your database:

var queries = sqltext.split(";", false); # The second argument prevents empty strings
for query in queries:
    db.query(query);

I have tested this solution and can confirm that it executes all queries, including queries split by newlines. Hope this helps.

[TGRCdev]

From what I have read, SQLite has sqlite3_exec(), a convenience method for reading lists of statements separated by semicolons. The query function in gdsqlite uses sqlite3_prepare_v2() which, from observed behavior, only executes the first statement and skips the rest.

I could change query to use sqlite3_exec, but I cannot change query_with_args because sqlite3_exec goes from source to result without preparing the statement or allowing the binding of arguments.

The best action I could take is to add a new method, probably named query_all, that takes a semicolon-separated string of SQL commands, and processes them all using the sqlite3_exec function.

[TGRCdev]

I've created the function query_all which does what I described in my previous comment. It is available for testing in my nightly build.

[vetu11]

Awesome!

You are doing an great job with this project. I think you should be the repository maintainer, I'm using your Android build and it works fantastic.