I am migrating a small Lazarus database application from PostgreSQL to SQLite (PostgreSQL was overkill, and I need something which is zero-config and can run from a flash drive on different machines). In the process, I have run into several little oddities in the way that SQLite differs from other DBMs with which I am familiar. Most of them I have been able to puzzle out, but this one has me befuddled.

I construct an SQL insert statement in variable sql, and then do the following:

    FormDatabase.SQLite3Connection.ExecuteDirect(sql);
    FormDatabase.SQLite3Connection.ExecuteDirect('commit');

First time through, no problems. Second time through with another row of data, and I get an exception with a message saying there is no active transaction when it reaches the commit (both inserts actually get saved in the table, however). If I omit the commit, nothing gets saved to the table on exit from the program. If I add the line:

    FormDatabase.SQLite3Connection.ExecuteDirect('begin');

...before the lines above, it gives an exception complaining that I can't nest transactions.

I am guessing that I am missing some basic detail in the setup somewhere, or not understanding some subtlety in the operation of SQLite. I would greatly appreciate somebody sending me a clue on how to deal with this. TIA!

On Wed, Jul 27, 2011 at 6:28 AM, Howard Lee Harkness <

Howard, looking at the file
\fpc\\source\packages\fcl-db\src\sqldb\sqlite\sqlite3conn.pp I suggest you to set breakpoints in StartdbTransaction and CommitRetaining and look at the call stack for the place that starts and ends the transaction without your intervention. The only problem could be the fpc sources usually compiled without debug info, don't know whether this applicable to fpc packages.

Max
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20110727/4b886011/attachment.html>

For now, I have a fairly ugly workaround, since I have another more important task to finish:

The performance of the SQLite database is very poor. I was able to speed it up a bit by putting indices on most of the commonly-search columns on the main table, but it's still about 20 to 30 times slower than the PostgreSQL version that I am replacing. As I slowly get back up to speed in Lazarus (it's been 3 years since I wrote the last iteration), it occurs to me that I may need some more sophisticated tools than I currently have. I also see that the current version of Lazarus made several subtle changes to the IDE, and I can't even find the way to get the stack trace anymore. Probably nothing that can't be solved with sufficient time and effort, but I was really hoping this migration would be a lot easier.

Is there some way that I can get SQLite to just read all of the tables into memory during the run of the program? Surely that would speed things up.

2011/7/27 Max Vlasov :

sqldb calls 'begin' when the transaction component is set to active. So your first 'commit' call works. The second has no 'begin'. Calling 'begin' after the transaction component called 'begin' already fails too.

Martin

On Thu, Jul 28, 2011 at 1:28 AM, Howard Lee Harkness <

Just to read data you can use TSQLite object from
\fpc\source\packages\sqlite\src\sqlite3db.pas unit. As long as I see It's not related to db unit and TDataSet.

Max
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20110729/dcf506c8/attachment.html>