* * *

Author Topic: (What is) Proper was to use SQLite3 w/ Lazarus?  (Read 630 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: 348
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: 840
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: 7087
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 »
inline variables like in D10.3 are a bit like Brexit: if you are given the wrong information it sounds like a good idea. Every kid loves candy, but it makes you fat and your teeth will disappear.

chrnobel

  • Full Member
  • ***
  • Posts: 225
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: 7087
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.
inline variables like in D10.3 are a bit like Brexit: if you are given the wrong information it sounds like a good idea. Every kid loves candy, but it makes you fat and your teeth will disappear.

Thaddy

  • Hero Member
  • *****
  • Posts: 7087
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."
inline variables like in D10.3 are a bit like Brexit: if you are given the wrong information it sounds like a good idea. Every kid loves candy, but it makes you fat and your teeth will disappear.

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus