Recent

Author Topic: [Solved] Problem with understanding SQLdb and SQLite  (Read 3170 times)

JanRoza

  • Hero Member
  • *****
  • Posts: 538
    • http://www.silentwings.nl
[Solved] Problem with understanding SQLdb and SQLite
« on: December 26, 2012, 02:53:07 pm »
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.
« Last Edit: December 26, 2012, 11:14:28 pm by JanRoza »
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

Leledumbo

  • Hero Member
  • *****
  • Posts: 8112
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: Problem with understanding SQLdb and SQLite
« Reply #1 on: December 26, 2012, 03:02:29 pm »
Dig in some information from here:
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial2

JanRoza

  • Hero Member
  • *****
  • Posts: 538
    • http://www.silentwings.nl
Re: Problem with understanding SQLdb and SQLite
« Reply #2 on: December 26, 2012, 03:10:22 pm »
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?
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64

anyeos

  • New Member
  • *
  • Posts: 13
Re: Problem with understanding SQLdb and SQLite
« Reply #3 on: December 26, 2012, 05:50:58 pm »
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

  • Hero Member
  • *****
  • Posts: 538
    • http://www.silentwings.nl
Re: Problem with understanding SQLdb and SQLite
« Reply #4 on: December 26, 2012, 11:14:07 pm »
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.
OS: Windows 10 (64 bit) / Ubuntu 19.04 (64 bit)
Laz: Lazarus 2.0.4 FPC 3.0.4 i386-win32-win32/win64