SQLite open table for read only purpose

[kjteng]

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]

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]

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]

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]

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.

[mangakissa]

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.

[kjteng]

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]

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]

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!