Failed statements prevent me from closing database

[Richard Cobbe <cobbe@…>]

DrScheme? version version 4.2.2.6-svn4nov2009 [3m].

If a database statement fails to complete for any of various reasons, it can leave the database in a state where it's difficult to close the database.

To reproduce, create a SQLite database with the following schema:

CREATE TABLE table_1 (id integer primary key, name text not null);
CREATE TABLE table_2 (id integer not null references table_1 (id), age integer not null);
CREATE TRIGGER trigger_1 before insert on table_2 when

not exists (select 1 from table_1 where id = new.id)
begin

select raise(abort, 'constraint failed');

end;

Save it where you like; I put it in /tmp/database.sqlite3

Start DrScheme?. At the REPL, try the following:

> (define db (open (string->path "/tmp/database.sqlite")))
> (insert db "insert into table_2 values (1, 42)")
. . SQLite Error: Abort due to contraint violation

The error is perfectly correct; this is a constraint violation. But now my first attempt to close the database fails:

> (close db)
. . SQLite Error: The database file is locked

I can close the database on the second attempt.

I've seen the paragraph in the docs that suggest that this last error message is due to some unfinalized sql statements hanging around. But I'm not using prepared statements directly; the "insert" call created the prepared statement, and I don't have access to the prepared statement in order to finalize it.

[jaymccarthy]

I believe that I have fixed this by reviewing my handling of internally created statement handlers.