SQLite datase created only in memory? (Windows CE)
lazarus / 16-Databases

----------------------------------------------------------------------
GeorgiBonchev · 2018-07-31 07:31

I am testing SQLite database connection  in an app running under
Windows CE. I downloaded sqlite3ce.dll that is needed for Win CE and
renamed it sqlite3.dll as the program was searching for this dll.

Then I followed the instructions given in this tutorial:

http://wiki.freepascal.org/SQLdb_Tutorial1

I placed all needed components and I made connections between them as
it was described. Then I placed a button with this code:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button2Click(Sender: TObject);begin  try   
DBConnection.DatabaseName:= '.\test.sqlite';   
DBConnection.Connected:= True;    SQLTransaction1.Active:= True;   
Unit_CommonProcedures.WriteLog('Connected!');     SQLQuery1.Close;   
SQLQuery1.SQL.Text:= 'CREATE TABLE "names" ("id" INTEGER PRIMARY KEY
AUTOINCREMENT, "name" TEXT)';    SQLQuery1.ExecSQL;   
Unit_CommonProcedures.WriteLog('Created!');     SQLQuery1.Close;   
SQLQuery1.SQL.Text:= 'select * from names';    SQLQuery1.ExecSQL;   
Unit_CommonProcedures.WriteLog('Opened!');      SQLQuery1.SQL.Text:=
'insert into "NAMES" (name) values (''Pesho'')';   
SQLQuery1.ExecSQL;    Unit_CommonProcedures.WriteLog('Inserted!'); 
Except    On E: Exception Do Begin     
Unit_CommonProcedures.WriteLog(E.Message);    End;  End;end; 
Unit_CommonProcedures is a unit of mine and WriteLog just writes given
text to a log file. When I press the button everything seems fine but
the database  "test.sqlite" is not created. I checked the whole disk
not just app folder and it cannot be found anywhere. If I press the
button second time there will be error in the log file as the table
"names" is already created. This is how the log file look:

31.07.2018 10:24:03 Connected!
31.07.2018 10:24:04 Created!
31.07.2018 10:24:04 Opened!
31.07.2018 10:24:04 Inserted!
31.07.2018 10:24:04 Connected!
31.07.2018 10:24:04 DBConnection : table "names" already exists

However if I quit the app and run it again and press the button it
will work without error. It will create "names" again. The app is
acting as if the table and the whole database is virtual, just in
memory. It vanishes everytime I close the program. Then I created
"test.sqlite" with an external database menager and even created table
"names". The result was the same. My apps ignores real file and still
seems to work with some virtual database.

I don't know if it has anything to do that this is a Windows CE app
probably not but who knows...

Any help?

----------------------------------------------------------------------
Thaddy · 2018-07-31 07:50

- You have to test for the file to exist, if it doesn't exist THEN
create it.
- I am missing a commit call...
- Your current code will indeed recreate the database

----------------------------------------------------------------------
GeorgiBonchev · 2018-07-31 08:30

Quote from: Thaddy on July 31, 2018, 09:50:49 am- I am missing a
commit call...

That was the problem. This was missing:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
SQLTransaction1.Commit;

----------------------------------------------------------------------
Thaddy · 2018-07-31 08:50

Glad to be of help. Plz mark as Solved.

----------------------------------------------------------------------
GeorgiBonchev · 2018-08-01 12:01

Well, I see "MARK UNREAD" but not "Mark Solved" 

Quote from: Thaddy on July 31, 2018, 10:50:25 amGlad to be of help.
Plz mark as Solved.