[Solved] Problem with understanding SQLdb and SQLite

[JanRoza]

I decided to use SQLite via SQLdb instead of via the sqlite3dataset, but I think I'm missing a point here.
I have a connection to my SQLite database, a transaction linked to the connection, a SQlquery to a table in my database and a datasource all placed on a datamodule.
When starting my program I activate the connection and start the transaction in the oncreate procedure of the datamodule.
My form opens and a dbgrid and some dbedits show data from the table correctly.
In the formclose procedure of my form I do a SQLquery.applyupdates and a connection.transaction.commitretaining.
This works well for existing records and all changes I make in the form to my table are correctly saved.
The problem starts when in my form I add a new record to the table (via a EXECSQL with an INSERT statement), as long as I stay in my form the new record is shown correctly but as soon as I close my form the connection.transaction.commitretaining breaks my program with an error telling me the database is locked.
I must be doing something fundamentally wrong but cannot get my finger on it.
What's the correct way to insert, update and delete data in a table via a form and commit all changes?

I really hope someone can point out my error, because this is driving me crazy.
I'm using Lazarus 1.04 with FPC 2.6.0.

[Leledumbo]

Dig in some information from here:
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial2

[JanRoza]

I already studied SQLDB Tutorials 1, 2 and 3 (that's where I got the idea from to put in my form.close procedure), but as soon as the transaction.commitretaining statement is run I get the error that my database is locked. So any ideas?

[anyeos]

Some friend suggested me to use Zeos components. And when I begin to use it, some problems get solved.

It supports all functions needed for database (blob fields are sent correctly not truncated) and some more.
Working with it is a little easier than working with Lazarus SQLdb components.

So, give it a try. You only need to put a SQLConnection a Query and a DataSource (the same that you will be using right now less one: the SQLTransaction is not required in Zeos).


Anyway as I can see you are trying to force an insertion "outside of the control of the database core of Lazarus" (or as you indicated Lazarus to do the things).

You can try:

Code: [Select]

YourTQueryComponent.Insert;
And that will work hopefully in the correct way.

[JanRoza]

Finally solved my problem, it turned out to be a typo which caused a table to remain open after I thought it was closed. I know about ZEOS but I want to build this program using only standard Lazarus without extra packages (it's for educational purposes).
I'll explain my trainees about installing packages later when they are a bit more comfortable with Lazarus as is.