FMX Desktop application with local SQL database. Database doesn't work properly

Question

I am creating Firemonkey Desktop Application with local SQLite Database (using Delphi XE4 Professional), but database do not work properly. There is no probelm, when I am inserting some data into database, but when I am trying to execute some other SQL commands on my database (especially DELETE and SELECT), I retrieve message "Database is locked" or something like "' 7 ' is not a valid integer value".

In my application I've used TSQLConnection, TSQLQuery, TDataSetProvider and TClientDataSet components to connect to my database. Everything is connected to existing database just like in this video http://www.youtube.com/watch?v=ljdo0yUNVmA only difference is, that my database isn't InterBase, but SQLite.

Database is created with SQLite3.7.11 from cmd

CREATE TABLE History ( 
  ID  INTEGER       PRIMARY KEY AUTOINCREMENT,
  Kod TEXT( 7 ));

CREATE TABLE Drogs ( 
  Kod    TEXT( 7 )   PRIMARY KEY,
  Naz    TEXT( 60 ),
  Dop    TEXT( 60 ),
  RegCis TEXT( 20 ),
  ATC    TEXT( 8 ),
  Hraz   TEXT( 1 ),
  Obch   TEXT( 1 ));

After connecting database to my application (as described above), data are inserted into table Drogs

sKod := quotedstr('1234567');
sNaz := quotedstr('Name');
sHraz := quotedstr('0');

SQLQuery1.SQL.Clear;
SQLQuery1.SQL.Add('INSERT INTO Drogs (Kod, Naz, Hraz) ');
SQLQuery1.SQL.Add('VALUES('+sKod+','+sNaz+','+sHraz+')');
SQLQuery1.ExecSQL;

This works without problems, but this code

SQLQuery1.SQL.Clear;
SQLQuery1.SQL.Add('SELECT * from Drogs');
SQLQuery1.Open;

gives error message like

'7 ' is not a valid integer value

and code

SQLQuery1.SQL.Clear;
SQLQuery1.SQL.Add('DELETE FROM Drogs WHERE Hraz = "0"');
SQLQuery1.ExecSQL;

"sometimes" gives error message

Database is locked

Any idea, why this happens? I'm out of options...

i think that using DBX for embedded database is overkill - also because DBX requires global state change of Windows. if you want to go embedded - then go embedded, use native SQLite API - there is a lot of wrappers starting with mORMot, ZeosDB and even more in Google and Torry.net Additionally while i agree that there is no point in using interbase - you can always use Firebird Embedded with lightweight libs like UIB or IBX+FBXUtils. Then you can find FB developers and communicate with them in Russian on gmane.org and/or SQL.ru that you cannot for SQLite — Arioch 'The
SQLQuery1.SQL.Add('VALUES('+sKod+','+sNaz+','+sHraz+')'); - bad code. Better use parameters and specify correct types for those parameters. bobby-tables.com/delphi.html — Arioch 'The
As I see, you are inserting sKod and Hraz in database as strings, although usually we would use integer in such case. When opening SQLQuery with select, you probably try to load selected data into some variables or controls that expect integer values and at that moment you get error '7 ' is not a valid integer value, because is stored as string. Check if you have any event on SqlQuery or assigned datasource. This is only my assumption. Database can be locked on delete if you haven't done commit after insert or update. — AvgustinTomsic

Allan Artuso Allan Artuso · Accepted Answer · 2013-08-17T21:58:21

Go to SQLQuery1 and set "Active = False"

FMX Desktop application with local SQL database. Database doesn't work properly

1 Answers