SQLite open table for read only purpose
lazarus / 16-Databases

----------------------------------------------------------------------
kjteng · 2017-11-27 01:24

How do I open a table for read only purpose (so that another user can
edit the table)?

I tried setting the readonly property to true but it doesnt work. (I
work around solution this issue by coping the table to a buffdataset
and then disconnect the sqlite database. However this is only
practical for small table due to memory constraint.)

I read somewhere in internet that we can connect a sqlite database in
readonly mode using url filename. How can this be done in lazarus ?

----------------------------------------------------------------------
HeavyUser · 2017-11-27 06:23

try opening the params property and adding Read only=True or mode=ro I
don't recall which one works in sqlite (maybe both).

----------------------------------------------------------------------
mangakissa · 2017-11-27 07:49

I can't reproduce it.
I have a dbgrid and navigator on form . Set property
TSQLQuery.readonly to true and run my program. Now I can't insert /
modify a record.

----------------------------------------------------------------------
kjteng · 2017-11-27 14:50

Quote from: HeavyUser on November 27, 2017, 07:23:31 amtry opening the
params property and adding Read only=True or mode=ro I don't recall
which one works in sqlite (maybe both).

I have tried both the above suggestions (Readonly=True and mode=ro).
The database is locked when a select sql statement is executed. .

----------------------------------------------------------------------
taazz · 2017-11-27 16:40

Quote from: kjteng on November 27, 2017, 03:50:01 pmQuote from:
HeavyUser on November 27, 2017, 07:23:31 amtry opening the params
property and adding Read only=True or mode=ro I don't recall which one
works in sqlite (maybe both).

I have tried both the above suggestions (Readonly=True and mode=ro).
The database is locked when a select sql statement is executed.  .
not to second guess heavyuser I'm under the impression that s/he talks
about the TSQLite3connection object not the query here. Which means
that you have to use two different TSQlite2connection objects one for
read and one for write and keep the write one as sort as possible.

----------------------------------------------------------------------
mangakissa · 2017-11-28 07:39

Quote from: kjteng on November 27, 2017, 03:50:01 pmQuote from:
HeavyUser on November 27, 2017, 07:23:31 amtry opening the params
property and adding Read only=True or mode=ro I don't recall which one
works in sqlite (maybe both).

I have tried both the above suggestions (Readonly=True and mode=ro).
The database is locked when a select sql statement is executed. .
Please give us some code to see what you're doing.

----------------------------------------------------------------------
kjteng · 2017-11-29 12:20

mangakissa:

No code is required. Just try the following:
1. Drop a few database related components  (SQLite3Connection1,
SQLQuery1, SQLTransaction1, DataSource1, DBGrid1) on a  forms;
2. Connect the SQLite3Connection1 to a sqlite database,  and key in
'Select * from test'  into SQLQuery1.SQL   ("test" is one of the table
in the database);
3.Set the sqlQuery1.Active to True.
4. Now you can see the data in dbgrid1.  However you would not able to
commit any change in any of the table in the same database from other
application (eg. sqlliteStudio). sqlliteStudio will freeze (keep
trying to commit the transaction) until I change
sqlite3connection1.connected to FALSE

----------------------------------------------------------------------
tudi_x · 2017-11-29 12:34

alternative in sqlite if you want to expose read only content of a
table you can create a view of that table.
the advantage is that later on you could easily add columns from other
tables without modifying your inserts.
currently in sqlite views are read only.

----------------------------------------------------------------------
kjteng · 2017-11-29 12:44

Thank for the reply.  Yeah after browsing the source code for the
component (sqlite3conn.pas)  I noticed that it is still using the old
function (sqlite3_open)  to connect the database, not using
sqlite3_open_v2 .  I think that is the reason.

I have just tried to use ZeosLib to connect sqlite3 database. Problems
solved!