Recent

Author Topic: SQLite open and close database connection  (Read 473 times)

pmralbuquerque

  • New Member
  • *
  • Posts: 11
SQLite open and close database connection
« on: October 16, 2022, 06:19:09 pm »
Hello,

Please forgive the dumbness of the question, but I never worked with databases before.
I am trying to understand the process of using SQLite in a GUI application.

So far, I managed to establish the connection, create a logon form, and create a new database reading SQL code from a file. Next comes the new data, refresh, update, delete, etc.

But there is something I'm not sure of. Reading https://forum.lazarus.freepascal.org/index.php?topic=57083.0, I can see an ending group:
Code: Pascal  [Select][+][-]
  1. SQLQuery.Close;
  2.   SQLQuery.Free;
  3.   Transaction.Free;
  4.   SQLiteConn.Free;

So, my question is, when should I terminate/close the connection, and where in the application?
  • Open at startup and close at exit?
  • Open and close wherever it is used locally?
  • Is that group everything needed?

Thank you in advance for your answers.
Lazarus 2.2.4
Intel(R) Core(TM) i7-6500U CPU @ 2.50GHz   2.60 GHz RAM 16,0 GB

Zvoni

  • Hero Member
  • *****
  • Posts: 1591
Re: SQLite open and close database connection
« Reply #1 on: October 17, 2022, 09:13:51 am »
3) If you use the "native" Classes/Controls/Objects of FPC/Lazarus, you need the "Trinity": Connection, Transaction, Query, interconnected as documented. That's all you need. There is some "other" stuff (DataMadoule etc. resp. ZEOS instead) you could use additionally, though
1+2) Open/Closing: It depends.
If it's a single-user accessing hat DB (only one user will use your program), then open at startup, close on exit. If it's multi-user, then it also depends, how many users want to write to the DB at the same time. If you have more than one, open before needing, close after done. If multi-user, id'd use journal_mode=WAL (add as a connection-param).

EDIT: If it's a "real" Multi-user-environment, then SQLite is IMO the wrong DBMS (despite loving SQLite myself, i do know its limits). Then I'd rather go for a server-based DBMS (MySQL et al)
« Last Edit: October 17, 2022, 09:16:06 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

pmralbuquerque

  • New Member
  • *
  • Posts: 11
Re: SQLite open and close database connection
« Reply #2 on: October 18, 2022, 03:48:25 pm »
Hello @Zvoni, and thank you for your answer.

This is a single user environment, a learning project, something I'm trying to adapt from an old ZX Spectrum BASIC code.

So, opening at startup and close at shutdown it is.

Thanks a lot. :D
Lazarus 2.2.4
Intel(R) Core(TM) i7-6500U CPU @ 2.50GHz   2.60 GHz RAM 16,0 GB

 

TinyPortal © 2005-2018