Recent

Author Topic: Update SQLite3 database with DBNavigator: cannot commit transaction...  (Read 5432 times)

jeanchristophe

  • Jr. Member
  • **
  • Posts: 88
I want to make a simple program out of the box, and I am running into several problems. I search on the forum and find a lot of post on the subject, that's why I decided to write it all in the following lines.
Maybe should it be on the wiki page, but I am not a professional and works mainly on one program, so my knowledge is not that deep. The second reason is that my explanation is just based on ”Make it works,  that's it."
So if a redactor find these lines useful, please use them as you want! :-)

I want to make this program to modify a field in a table by copying the file name of the picture into a field, so I can use it afterwards in an another program.

Code: [Select]
How to use Sqlite3 with a DBGrid and a DBNavigator
Components:
SQLite3Connection:
DatabaseName: full path of my database
Transaction: SQLTransaction
Connected: True
DataSource:
DataSet: SQLQuery
Enabled: True
SQLQuery
Database: SQLite3Connection
SQL: select * from whatever;
Transaction: SQLTransaction
Active: True
SQLTransaction
Database: SQLite3Connection
Active: True
OpenPictureDialog
DBGrid
Datasource: DataSource
DBNavigator
Datasource: DataSource

I add one more component to make my project:
DBEdit
Datasource: DataSource
Image

And three buttons related to my project:
Choosefile (onClick):
if OpenPictureDialog1.Execute then
begin
  filename := OpenPictureDialog1.Filename;
    Image1.Picture.LoadFromFile(OpenPictureDialog1.FileName);
end;
FileName (onClick):
filename  := StringReplace(filename, 'something', '..', [rfReplaceAll, rfIgnoreCase]);
  DBEdit2.Text:= filename;
Commit (onClick):
SQLQuery1.ApplyUpdates;
SQLTransaction1.CommitRetaining;

That's it!
How do I use it:
F9 -> Program runs
Choose the picture with ChooseFile button
Push the edit button on the DBNavigator
Push the FileName button
Push the post button on the DBNavigator
Push the Commit button

Unfortunately I get this message:
Code: [Select]
cannot commit transaction - SQL statements in progress.

I found 21 threads on the subject (SQLite3 DBNavigator), and I still have some to read, but if somebody could tell me, what I have not done right, it will be nice

Best Regards
Jean-Christophe

kpeters58

  • Sr. Member
  • ****
  • Posts: 267
Re: Update SQLite3 database with DBNavigator: cannot commit transaction...
« Reply #1 on: August 09, 2014, 07:34:49 pm »
Zip up your project (without executable(s)) and attach it so we can help you
Lazarus 2.0.4/FPC 3.0.4/Win 64

jeanchristophe

  • Jr. Member
  • **
  • Posts: 88
Re: Update SQLite3 database with DBNavigator: cannot commit transaction...
« Reply #2 on: August 09, 2014, 08:45:01 pm »
Hello,

Here is the zip file!

Best Regards
Jean-Christophe

kpeters58

  • Sr. Member
  • ****
  • Posts: 267
Re: Update SQLite3 database with DBNavigator: cannot commit transaction...
« Reply #3 on: August 10, 2014, 12:58:58 am »
Have a look at the changes I made to get it to work. BTW, you forgot to supply the 32bit SQLite DLL and also, more importantly, your database file.
I reverse-engineered something that may or may not make sense - my Danish isn't that great.

Lazarus 2.0.4/FPC 3.0.4/Win 64

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Update SQLite3 database with DBNavigator: cannot commit transaction...
« Reply #4 on: August 10, 2014, 08:27:15 am »
Not supplying the dll (or any executable) is a good thing IMO for security reasons...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

jeanchristophe

  • Jr. Member
  • **
  • Posts: 88
Re: Update SQLite3 database with DBNavigator: cannot commit transaction...
« Reply #5 on: August 10, 2014, 03:40:17 pm »
Thank you for your answer kpeters58!  :)

I try to run it, and I have now the following message:
Code: [Select]
...exception class 'EDatabaseError'...
Missing (compatible) underlying dataset. can not open

Best Regards
Jean-Christophe

kpeters58

  • Sr. Member
  • ****
  • Posts: 267
Re: Update SQLite3 database with DBNavigator: cannot commit transaction...
« Reply #6 on: August 10, 2014, 05:20:33 pm »
Downloaded and tested: Works just fine.....


You realize that you have to adjust this line in FormCreate to match your setup?


  SQLite3Connection1.DatabaseName := 'c:\temp\jc\picture\stamps.sqlite';
Lazarus 2.0.4/FPC 3.0.4/Win 64

 

TinyPortal © 2005-2018