[Solved] Problem with understanding SQLdb and SQLite
lazarus / 16-Databases

----------------------------------------------------------------------
JanRoza · 2012-12-26 13:53

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 · 2012-12-26 14:02

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

----------------------------------------------------------------------
JanRoza · 2012-12-26 14:10

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 · 2012-12-26 16:50

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 · 2012-12-26 22:14

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.