How to use transactions with the SQLite module?

[S.L.C]

I need to be able to use transactions in my scripts however the server hangs when I commit. Extending the code on my previous thread here I have the following.

Creating the database structure:

Code: [Select]

// Make sure that the database has the Streaks table
Exec(@"CREATE TABLE IF NOT EXISTS [Streaks] (
[Message] VARCHAR(128)  NOT NULL
)");

All queries in one string:

Code: [Select]

// Insert the default values into the Streaks table
Exec(@"BEGIN;
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
INSERT INTO [Streaks] ([Message]) VALUES ('%s');
COMMIT;",
@"%s got the taste of blood. Victim: %s Killstreak: %d",
@"%s starts to enjoy it. Victim: %s Killstreak: %d",
@"%s is feeling bold. Victim: %s Killstreak: %d",
@"%s wants to be heroic. Victim: %s Killstreak: %d",
@"%s just became fearless. Victim: %s Killstreak: %d",
@"%s feels the excitement. Victim: %s Killstreak: %d",
@"%s is a freak. Victim: %s Killstreak: %d",
@"%s went on a killing spree. Victim: %s Killstreak: %d",
@"%s is unstoppable. Victim: %s Killstreak: %d",
@"%s starts to dominate. Victim: %s Killstreak: %d",
@"%s is relentless. Victim: %s Killstreak: %d",
@"%s went on a rampage. Victim: %s Killstreak: %d",
@"%s is delusional. Victim: %s Killstreak: %d",
@"%s became a nemesis. Victim: %s Killstreak: %d",
@"%s is a monster. Victim: %s Killstreak: %d",
@"%s annihilates everything. Victim: %s Killstreak: %d",
@"%s is out of this world. Victim: %s Killstreak: %d",
@"%s massacred his opponents. Victim: %s Killstreak: %d",
@"%s became a savage. Victim: %s Killstreak: %d",
@"%s is a deamon. Victim: %s Killstreak: %d",
@"%s made a genocide. Victim: %s Killstreak: %d",
@"%s is a mass murderer. Victim: %s Killstreak: %d",
@"%s slaughtered all hopes. Victim: %s Killstreak: %d",
@"%s is immortal. Victim: %s Killstreak: %d",
@"%s reached divinity. Victim: %s Killstreak: %d",
@"%s is god-like. Victim: %s Killstreak: %d"
);

Queries executed individually:

Code: [Select]

// Insert the default values into the Streaks table
Exec(@"BEGIN;");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s got the taste of blood. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s starts to enjoy it. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s is feeling bold. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s wants to be heroic. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s just became fearless. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s feels the excitement. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s is a freak. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s went on a killing spree. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s is unstoppable. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s starts to dominate. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s is relentless. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s went on a rampage. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s is delusional. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s became a nemesis. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s is a monster. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s annihilates everything. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s is out of this world. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s massacred his opponents. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s became a savage. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s is a deamon. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s made a genocide. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s is a mass murderer. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s slaughtered all hopes. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s is immortal. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s reached divinity. Victim: %s Killstreak: %d");
Exec(@"INSERT INTO [Streaks] ([Message]) VALUES ('%s');", @"%s is god-like. Victim: %s Killstreak: %d");
Exec(@"COMMIT;");

Both methods hang when I commit. I've tested the generated query string in other software and it works perfectly.

[thijn]

With hang you mean it stops responding and the queries are never executed? Or it just hangs the server for a while and does execute them?

[S.L.C]

No. Just hangs at the query function which includes the COMMIT command. Which results in the database being unaffected by the query and the script execution simply dies/hangs at that function with no other output whatsoever. The server doesn't crash or anything else but simply stops executing Squirrel code.

[stormeus]

I can't reproduce the issue.

http://i.imgur.com/V8tVK0a.png

[S.L.C]

I can't reproduce the issue.

http://i.imgur.com/V8tVK0a.png

Weird thing is that it started to work for me too after rebooting my computer. I'm curious what caused this earlier I might look deeper into it tomorrow and maybe see why this was happening. The database wasn't opened by other software for me to think that the file was locked. So there must be some other reason.