database locked error when using DBNavigator
lazarus / 16-Databases

----------------------------------------------------------------------
mrj2 · 2011-11-22 13:02

I am using Lazarus v0.9.28.2 -8 on my Ubuntu PC.

I have created a unit for the data components :
     SQLite3Connection
     SQLQuery1,
     SQLTransacion1
     DataSource1

I have inserted text into the SQLQuery1.SQL :
SELECT RECORD_NO,
       DATE_PURCHASED,
       DATE_LAZARUS,
       BRAND,
       CATEGORY,
       DESCRIPTION,
       SUPPLIER,
       COST_PER_ITEM,
       NUMBER_OF_ITEMS_INCLUDED,
       NUMBER_OF_ITEMS_PURCHASED,
       NUMBER_OF_MEASURES,
       MEASURE
FROM ITEMS_LEDGER
ORDER BY  DATE_PURCHASED,
          DESCRIPTION,
          COST_PER_ITEM;    

I have linked this to a BDGrid.  This is all fine so far as data are
returned and showing in the grid.

I want to be able to insert/update/delete data in the table.  So I
have created a panel with combo boxes and edit boxes.  This is all
fine so far as data are seen in both the grid and the edit/combo
boxes.

I have inserted a DBNavigator and linked it to the data source.  Fine
so far.  I can move to the the next/previous record or go from the
first record to the last record.

What I cannot do is save edits, inserts and deletes.

I read on a previous thread about using ApplyUpdates.  I have tried
creating procedures in SQLQuery1 for both AfterDelete and AfterPost. 
These both contain the lines:
    SQLQuery1.ApplyUpdates;
    SQLTransaction1.Commit;

Now I get a database locked error whenever I post a change using the
DBNavigator.

Could someone please point me in the right direction.  Many thanks for
reading/replying to this thread.

----------------------------------------------------------------------
ludob · 2011-11-24 09:39

SQLTransaction1.Commit closes all datasets using SQLTransaction1 which
is probably not what you want. Use SQLTransaction1.CommitRetaining
instead.  This commits your transaction while continuing immediately
with a new transaction and leaving the dataset open.

----------------------------------------------------------------------
mrj2 · 2011-11-25 15:14

Thanks for the reply.

I have tried that and I get the same outcome.

I do have the UsePrimaryKeyAs Key set to True in SQL1Query1

I have also tried creating my own buttons with deleteSQL and updateSQL
commands with a try/catch statement.  Various error messages have
cropped up including SIGSEGV.

I have tried creating  a separate form to detach viewing from
inputting data.  Here I created a second query and made all the
connection components inactive before transferring the link to the
second query.  When executing the query, the same error came up
'Database locked.

I wonder whether this is to do with my SQLite library .so file  This
has made me think of using Firebird instead of SQLite3.  Would this be
a reasonable reaction?  Or, is there something very basic I am
missing?

----------------------------------------------------------------------
mrj2 · 2011-11-25 15:46

I seem to think I have go to the bottom of this.  I have created a
small test database in Firebird.  As suggested I used
'SQLTransaction1.CommitRetaining' and to my relief, I can insert edit
and delete records.  This must be some problem with my SQLite .so
file.  I have decided to give SQLite a miss.  This has created a bit
of work for myself as I now have to import the SQLite database into
Firebird, but I now it will work once this has been completed.