Recent

Author Topic: (What is) Proper was to use SQLite3 w/ Lazarus?  (Read 1028 times)

ozznixon

  • Jr. Member
  • **
  • Posts: 81
    • http://www.modernpascal.com/
(What is) Proper was to use SQLite3 w/ Lazarus?
« on: October 19, 2018, 12:17:18 am »
I have found by error, the right way to hook the components:

SQLite3Connection1.DatabaseName:='%path%/filename.db';
SQLite3Connection1.Transaction:=SQLTransaction1;
SQLQuery1.Database:=SQLite3Connection1;
// DO NOT SET SQLQuery1.DataSource //
SQLQuery1.Transaction:=SQLTransaction1;
DataSource1.DataSet:=SQLQuery1;

SQLQuery1.SQL.Text (via component inspector) = 'SELECT * FROM IMP ORDER BY KEY';
DBEdit1..DBEdit9 associate to fields.

I open all this up, Append new record via NAVBar ... when I save, the fields that have Database Rules all complain they are REQUIRED.

I saw another post where the developer mentioned OnAfterInsert - set SQLQuery1.FieldByName('KEY').Required:=FALSE; etc.

I do my edit, looks like all works, open DB on another machine - empty.

So, I checkbox SQLQuery1.Options = [sqoKeepOpenOnCommit,sqoAutoApplyUpdates,sqoAutoCommit,sqoRefreshUsingSelect] now when I edit and post, I get "Database is Locked".

Ozz
---
Want to kick the tires to a Free Pascal like script engine? http://www.ModernPascal.com/

bee

  • Sr. Member
  • ****
  • Posts: 349
Re: (What is) Proper was to use SQLite3 w/ Lazarus?
« Reply #1 on: October 19, 2018, 04:25:27 am »
I've made a simple code example of how to use FPC's sqlDB units with sqLite3 here. It doesn't use Lazarus, so it might not a direct solution to your problem but it could give you something to look at. Hope it helps. 😊
-Bee-

A long time pascal lover.

mangakissa

  • Hero Member
  • *****
  • Posts: 872
Re: (What is) Proper was to use SQLite3 w/ Lazarus?
« Reply #2 on: October 19, 2018, 11:18:04 am »
Your problem  SQLite is not in multiuser mode. Normally SQLite is a single database and will be locked entiraly. There's a way to make it multiuser, but it's better to look to anothber database like Firebird.

Working with SQLdb is the right way to do it.
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

Thaddy

  • Hero Member
  • *****
  • Posts: 7337
Re: (What is) Proper was to use SQLite3 w/ Lazarus?
« Reply #3 on: October 19, 2018, 12:10:33 pm »
I actually have a request on mantis to change calls to sqlite3_open to sqlite3_open_v2. The latter enables to configure for sequential and/or multithreaded mode. Both can be multi-user.
But mangakissa is correct: better use sqldb with sqlite3 anyway. The other class is strictly single user and has many more drawbacks..
« Last Edit: October 19, 2018, 12:12:24 pm by Thaddy »
Brexit. My Indonesian and Dutch friends know what " Tempo doeloe" means....There is no empire.

chrnobel

  • Full Member
  • ***
  • Posts: 241
Re: (What is) Proper was to use SQLite3 w/ Lazarus?
« Reply #4 on: October 25, 2018, 05:18:54 pm »
Your problem  SQLite is not in multiuser mode. Normally SQLite is a single database and will be locked entiraly. There's a way to make it multiuser.
Actually it works quite ok, if you use ZeosLib instead of the default SQLite connection - it does also have the advantage, that if you want to migrate to MySQL (or what ever database) it is very easy.

Thaddy

  • Hero Member
  • *****
  • Posts: 7337
Re: (What is) Proper was to use SQLite3 w/ Lazarus?
« Reply #5 on: October 25, 2018, 05:36:01 pm »
Your problem  SQLite is not in multiuser mode. Normally SQLite is a single database and will be locked entiraly. There's a way to make it multiuser.
Actually it works quite ok, if you use ZeosLib instead of the default SQLite connection - it does also have the advantage, that if you want to migrate to MySQL (or what ever database) it is very easy.
Indeed. I explained that in the other thread. But for it to work in the default sql components requires a small fix: sqlite3_open_v2 everywhere.
Brexit. My Indonesian and Dutch friends know what " Tempo doeloe" means....There is no empire.

Thaddy

  • Hero Member
  • *****
  • Posts: 7337
Re: (What is) Proper was to use SQLite3 w/ Lazarus?
« Reply #6 on: October 29, 2018, 12:25:27 pm »
Michael has implemented sqlite3_open_v2() as per my suggestion and the even better news is the target is FPC 3.2.0.
That means you can now query and specify the threading settings for sqlite3 exactly like I explained.
From the report:
Code: Pascal  [Select]
  1.   // VFS not supported at this time.
  2.   // Do not change the order. See NativeFlags constant in GetSQLiteOpenFlags.
  3.  
  4.   TSQLiteOpenFlag = (
  5.     sofReadOnly,
  6.     sofReadWrite,
  7.     sofCreate,
  8.     sofNoMutex,
  9.     sofFullMutex,
  10.     sofSharedCache,
  11.     sofPrivateCache,
  12.     sofURI,
  13.     sofMemory
  14.   );
  15.   TSQLiteOpenFlags = set of TSQLiteOpenFlag;
  16.  
"Default is set to
DefaultOpenFlags = [sofReadWrite,sofCreate];

Which should be backwards compatible."
Brexit. My Indonesian and Dutch friends know what " Tempo doeloe" means....There is no empire.