Recent

Author Topic: SQLite open table for read only purpose  (Read 4067 times)

kjteng

  • Full Member
  • ***
  • Posts: 182
SQLite open table for read only purpose
« on: November 27, 2017, 02:24:55 am »
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

  • Sr. Member
  • ****
  • Posts: 263
Re: SQLite open table for read only purpose
« Reply #1 on: November 27, 2017, 07:23:31 am »
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

  • Hero Member
  • *****
  • Posts: 955
Re: SQLite open table for read only purpose
« Reply #2 on: November 27, 2017, 08:49:24 am »
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.
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

kjteng

  • Full Member
  • ***
  • Posts: 182
Re: SQLite open table for read only purpose
« Reply #3 on: November 27, 2017, 03:50:01 pm »
try 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

  • Hero Member
  • *****
  • Posts: 5363
Re: SQLite open table for read only purpose
« Reply #4 on: November 27, 2017, 05:40:44 pm »
try 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.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

mangakissa

  • Hero Member
  • *****
  • Posts: 955
Re: SQLite open table for read only purpose
« Reply #5 on: November 28, 2017, 08:39:19 am »
try 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.
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

kjteng

  • Full Member
  • ***
  • Posts: 182
Re: SQLite open table for read only purpose
« Reply #6 on: November 29, 2017, 01:20:35 pm »
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

  • Hero Member
  • *****
  • Posts: 532
Re: SQLite open table for read only purpose
« Reply #7 on: November 29, 2017, 01:34:40 pm »
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.
« Last Edit: November 29, 2017, 02:00:05 pm by tudi_x »
Lazarus 2.0.2 64b on Debian LXDE 10

kjteng

  • Full Member
  • ***
  • Posts: 182
Re: SQLite open table for read only purpose
« Reply #8 on: November 29, 2017, 01:44:35 pm »
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!