Recent

Author Topic: SQLite datase created only in memory? (Windows CE)  (Read 1443 times)

GeorgiBonchev

  • New member
  • *
  • Posts: 6
SQLite datase created only in memory? (Windows CE)
« on: July 31, 2018, 09:31:52 am »
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]
  1. procedure TForm1.Button2Click(Sender: TObject);
  2. begin
  3.   try
  4.     DBConnection.DatabaseName:= '.\test.sqlite';
  5.     DBConnection.Connected:= True;
  6.     SQLTransaction1.Active:= True;
  7.     Unit_CommonProcedures.WriteLog('Connected!');
  8.  
  9.     SQLQuery1.Close;
  10.     SQLQuery1.SQL.Text:= 'CREATE TABLE "names" ("id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT)';
  11.     SQLQuery1.ExecSQL;
  12.     Unit_CommonProcedures.WriteLog('Created!');
  13.  
  14.     SQLQuery1.Close;
  15.     SQLQuery1.SQL.Text:= 'select * from names';
  16.     SQLQuery1.ExecSQL;
  17.     Unit_CommonProcedures.WriteLog('Opened!');
  18.  
  19.  
  20.     SQLQuery1.SQL.Text:= 'insert into "NAMES" (name) values (''Pesho'')';
  21.     SQLQuery1.ExecSQL;
  22.     Unit_CommonProcedures.WriteLog('Inserted!');
  23.   Except
  24.     On E: Exception Do Begin
  25.       Unit_CommonProcedures.WriteLog(E.Message);
  26.     End;
  27.   End;
  28. 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

  • Hero Member
  • *****
  • Posts: 9184
Re: SQLite datase created only in memory? (Windows CE)
« Reply #1 on: July 31, 2018, 09:50:49 am »
- 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
also related to equus asinus.

GeorgiBonchev

  • New member
  • *
  • Posts: 6
Re: SQLite datase created only in memory? (Windows CE)
« Reply #2 on: July 31, 2018, 10:30:11 am »
- I am missing a commit call...

That was the problem. This was missing:

Code: Pascal  [Select]
  1. SQLTransaction1.Commit;

Thaddy

  • Hero Member
  • *****
  • Posts: 9184
Re: SQLite datase created only in memory? (Windows CE)
« Reply #3 on: July 31, 2018, 10:50:25 am »
Glad to be of help. Plz mark as Solved.
also related to equus asinus.

GeorgiBonchev

  • New member
  • *
  • Posts: 6
Re: SQLite datase created only in memory? (Windows CE)
« Reply #4 on: August 01, 2018, 02:01:52 pm »
Well, I see "MARK UNREAD" but not "Mark Solved"  :-\

Glad to be of help. Plz mark as Solved.