* GNATCOLL SQLite rollback vs cursor
@ 2012-08-31 10:34 Stephen Leake
2012-08-31 12:10 ` briot.emmanuel
0 siblings, 1 reply; 3+ messages in thread
From: Stephen Leake @ 2012-08-31 10:34 UTC (permalink / raw)
I've come across a strange problem using GNATCOLL with SQLite, and I'm
hoping someone can shed some light on it.
Here's code illustrating the problem:
-- Fixes bug
with Ada.Directories;
with Ada.Text_IO; use Ada.Text_IO;
with Ada.Unchecked_Deallocation;
with GNATCOLL.SQL.Exec; use GNATCOLL.SQL.Exec;
with GNATCOLL.SQL.Sqlite;
procedure Rollback_Bug_3
is
Db_Filename : constant String := "books.db";
Connection : Database_Connection;
type Cursor_Access_Type is access all Forward_Cursor;
Cursor : Cursor_Access_Type;
procedure Free is new Ada.Unchecked_Deallocation (Forward_Cursor, Cursor_Access_Type);
procedure Insert
(First : in String;
Last : in String)
is
begin
Put_Line ("Insert " & First & " " & Last);
Execute
(Connection,
"INSERT INTO Author (First, Last) VALUES (""" & First & """, """ & Last & """)");
if not Connection.Success then
-- GNATCOLL SQLite has obscure behavior with respect to
-- cursors and rollback; if a cursor is active, it prevents
-- Rollback from working (SQLite reports "database locked").
-- It doesn't prevent a successful INSERT. So we have to
-- Finalize any cursor before calling Rollback. Another
-- GNATCOLL quirk makes Finalize (Cursor) not visible, so we
-- use allocations. Sigh.
Free (Cursor); -- delete this to see the problem
Rollback (Connection);
else
Commit (Connection);
end if;
-- Find the just inserted ID for mapping (not using
-- Exec.Last_ID becuase I didn't know about it :)
if Cursor = null then
Cursor := new Forward_Cursor;
end if;
Cursor.Fetch
(Connection,
"SELECT ID, First, Last FROM Author WHERE First = """ & First & """ and Last = """ & Last & """");
if Cursor.Has_Row then
Put_Line ("ID => " & Cursor.Value (0));
end if;
end Insert;
begin
-- Create db from scratch
if Ada.Directories.Exists (Db_Filename) then
Ada.Directories.Delete_File (Db_Filename);
end if;
Connection := Build_Connection (GNATCOLL.SQL.Sqlite.Setup (Db_Filename));
-- Create Author table
Execute
(Connection,
"CREATE TABLE Author (ID INTEGER PRIMARY KEY, First TEXT, Last TEXT)");
Execute
(Connection,
"CREATE UNIQUE INDEX Author_Last on Author (Last, First)");
Execute
(Connection,
"CREATE UNIQUE INDEX Author_First on Author (First, Last)");
-- insert a couple of items, checking for success
Insert ("Ada", "Lovelace");
Insert ("Charles", "Babbage");
-- So far so good. Now try to insert Ada Lovelace again; fails
-- because of 'unique' constraint on Author_Name index
Insert ("Ada", "Lovelace");
-- Database be ok; try to add another name
Insert ("Grace", "Hopper");
end Rollback_Bug_3;
This code works properly, giving a reasonable error message for the
attempt to insert a duplicate message:
./rollback_bug_3.exe
Insert Ada Lovelace
ID => 1
Insert Charles Babbage
ID => 2
Insert Ada Lovelace
[SQL.ERROR] Failed to execute INSERT INTO Author (First, Last) VALUES ("Ada", "Lovelace") error=constraint failed
ID => 1
Insert Grace Hopper
ID => 3
However, my first version of this did not free the cursor; commenting
that out gives:
./rollback_bug_3.exe
Insert Ada Lovelace
ID => 1
Insert Charles Babbage
ID => 2
Insert Ada Lovelace
[SQL.ERROR] Failed to execute INSERT INTO Author (First, Last) VALUES ("Ada", "Lovelace") error=constraint failed
[SQL.ERROR] Failed to execute ROLLBACK error=database is locked
ID => 1
Insert Grace Hopper
[SQL.ERROR] Failed to execute BEGIN error=SQL logic error or missing database
[SQL.ERROR] Failed to execute ROLLBACK error=database is locked
Apparently the cursor holds some sort of lock on the database. But I can
insert new records while the cursor is active; the only thing I can't do
is rollback.
This seems to be an SQLite issue, not a GNATCOLL issue, so perhaps I
should take this to an SQLite mailing list.
--
-- Stephe
^ permalink raw reply [flat|nested] 3+ messages in thread
* Re: GNATCOLL SQLite rollback vs cursor
2012-08-31 10:34 GNATCOLL SQLite rollback vs cursor Stephen Leake
@ 2012-08-31 12:10 ` briot.emmanuel
2012-09-01 16:31 ` Stephen Leake
0 siblings, 1 reply; 3+ messages in thread
From: briot.emmanuel @ 2012-08-31 12:10 UTC (permalink / raw)
It is quite possible that this issue has in fact already been fixed in the development version of GNATCOLL, because now with or without the line that frees the cursor, the result is the same:
Insert Ada Lovelace
ID => 1
Insert Charles Babbage
ID => 2
Insert Ada Lovelace
[SQL.ERROR] Failed to execute INSERT INTO Author (First, Last) VALUES ("Ada", "Lovelace") error=columns First, Last are not unique
ID => 1
Insert Grace Hopper
ID => 3
Note also that we have improved the way we retrieve the error messages from sqlite, since the current one is clearer than just the "constraint error" you are getting.
Emmanuel
^ permalink raw reply [flat|nested] 3+ messages in thread
* Re: GNATCOLL SQLite rollback vs cursor
2012-08-31 12:10 ` briot.emmanuel
@ 2012-09-01 16:31 ` Stephen Leake
0 siblings, 0 replies; 3+ messages in thread
From: Stephen Leake @ 2012-09-01 16:31 UTC (permalink / raw)
briot.emmanuel@gmail.com writes:
> It is quite possible that this issue has in fact already been fixed in
> the development version of GNATCOLL, because now with or without the
> line that frees the cursor, the result is the same:
>
> Insert Ada Lovelace
> ID => 1
> Insert Charles Babbage
> ID => 2
> Insert Ada Lovelace
> [SQL.ERROR] Failed to execute INSERT INTO Author (First, Last) VALUES
> ("Ada", "Lovelace") error=columns First, Last are not unique
> ID => 1
> Insert Grace Hopper
> ID => 3
>
>
> Note also that we have improved the way we retrieve the error messages
> from sqlite, since the current one is clearer than just the
> "constraint error" you are getting.
Excellent.
--
-- Stephe
^ permalink raw reply [flat|nested] 3+ messages in thread
end of thread, other threads:[~2012-09-07 1:22 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2012-08-31 10:34 GNATCOLL SQLite rollback vs cursor Stephen Leake
2012-08-31 12:10 ` briot.emmanuel
2012-09-01 16:31 ` Stephen Leake
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox