Update SQLite3 database with DBNavigator: cannot commit transaction...
lazarus / 16-Databases

----------------------------------------------------------------------
jeanchristophe · 2014-08-09 12:08

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 · 2014-08-09 17:34

Zip up your project (without executable(s)) and attach it so we can
help you

----------------------------------------------------------------------
jeanchristophe · 2014-08-09 18:45

Hello,

Here is the zip file!

Best Regards
Jean-Christophe

----------------------------------------------------------------------
kpeters58 · 2014-08-09 22:58

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.

----------------------------------------------------------------------
BigChimp · 2014-08-10 06:27

Not supplying the dll (or any executable) is a good thing IMO for
security reasons...

----------------------------------------------------------------------
jeanchristophe · 2014-08-10 13:40

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 · 2014-08-10 15:20

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';