Cannot update sqlite database

[jpmilin]

Hello,

I have a problem with this very simple code :


procedure TForm1.Button1Click(Sender: TObject);
var
con : TUniConnection;
query: TUniquery;
begin

con := TUniConnection.create(self);
con.Database := 'c:\prod\test.db';
con.ProviderName := 'SQLite';
con.connected := true;

query := TUniQuery.Create(self);
query.connection := con;
query.sql.text := 'insert into test values(10)';
query.execSql;

con.applyUpdates;
con.connected := false;

query.free;
con.free;
showmessage('done');
end;


Any SQL statement to create or drop a table works.
Any SQL statement to insert, delete or update records does not work!
It seems that delphi sees the database content (cached?) as changed, but this change is not reflected to the actual database. Am i doing something wrong? Thanks in advance for your help.

[AlexP]

Hello,

Your code works correctly - data is inserted into the table. Please specife the UniDAC and IDE versions, and provide the script for creating the table.

[jpmilin]

My environment :
windows XP (2600 sp2)
delphi 7 build 4.453
unidac 4.6.11.0
sqlite3.dll 3.7.15.2

Using another sqlite wrapper (sqlite3dll.pas) and calling directly the low level functions sqlite3_open_v2,
sqlite3_exec, etc doesnt work either. The symptoms are the same : the data seen by the program
are consistent during the execution (existence of table, inclusion or deletion of records) but nothing is ever saved into the actual database.

I infer that the problem is probably in the dll file rather than the unidac components, but
I still don't know what it may be

[AlexP]

hello,

We have checked the latest SQLite3 library version - data is still inserted into the table correctly. The problem may be due to your DB file. Please create a new file and table and try to insert data. If the data is inserted to the new DB correctly, you should check the settings of the existent DB.

[jpmilin]

I deleted the database, and modified the code to let delphi create a new one.
Still no error when running, but nothing at all on the disk . The file is not even created !


procedure TForm1.Button1Click(Sender: TObject);
var
con : TUniConnection;
query: TUniquery;
begin

con := TUniConnection.create(self);
con.Database := 'c:\prod\test.db';
con.ProviderName := 'SQLite';
con.SpecificOptions.values['ForceCreateDatabase'] := 'True';
con.connected := true;

query := TUniQuery.Create(self);
query.connection := con;

query.sql.text := 'create table test (x integer)';
query.execSql;

query.sql.text := 'insert into test values(10)';
query.execSql;

con.applyUpdates;
con.connected := false;

query.free;
con.free;
showmessage('done');
end;

[AlexP]

Hello,

We still cannot reproduce the problem, your code creates a DB, a table, and records data to it. If you are using not an official SQLite3.dll build, try downloading it at the official developers website: http://sqlite.org/download.html ; in addition, try to execute your code using the Direct mode:

Code: Select all

...
con := TUniConnection.create(self);
con.Database := 'c:\prod\test.db';
con.ProviderName := 'SQLite';
con.SpecificOptions.values['ForceCreateDatabase'] := 'True';
con.SpecificOptions.values['Direct'] := 'True';
con.connected := true;
...

[jpmilin]

I am completely at lost. I've tried disabling the windows disk cache, using a pragma clause
to change the sqlite page_size, etc, to no avail...

As I think the problem is not specific to unidac, I will stop posting in this thread, except
if I finally find the explanation.

Thanks for your help anyway.

[AlexP]

Hello,

Please verify whether the problem when using the Direct mode is solved in UniDAC and try running the application on another PC, and let us know the result.