(What is) Proper was to use SQLite3 w/ Lazarus?
lazarus / 16-Databases

----------------------------------------------------------------------
Ozz_Nixon · 2018-10-18 22:17

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,sqoAutoApplyUp
dates,sqoAutoCommit,sqoRefreshUsingSelect] now when I edit and post, I
get "Database is Locked".

Ozz

----------------------------------------------------------------------
bee · 2018-10-19 02:25

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. 😊

----------------------------------------------------------------------
mangakissa · 2018-10-19 09:18

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.

----------------------------------------------------------------------
Thaddy · 2018-10-19 10:10

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..

----------------------------------------------------------------------
chrnobel · 2018-10-25 15:18

Quote from: mangakissa on October 19, 2018, 11:18:04 amYour 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 · 2018-10-25 15:36

Quote from: chrnobel on October 25, 2018, 05:18:54 pmQuote from:
mangakissa on October 19, 2018, 11:18:04 amYour 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.

----------------------------------------------------------------------
Thaddy · 2018-10-29 11:25

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   // VFS
not supported at this time.  // Do not change the order. See
NativeFlags constant in GetSQLiteOpenFlags.   TSQLiteOpenFlag = (   
sofReadOnly,    sofReadWrite,    sofCreate,    sofNoMutex,   
sofFullMutex,    sofSharedCache,    sofPrivateCache,    sofURI,   
sofMemory  );  TSQLiteOpenFlags = set of TSQLiteOpenFlag;  "Default is
set to
DefaultOpenFlags = [sofReadWrite,sofCreate];

Which should be backwards compatible."