SQLite and default Lazarus db components

[Elmug]

Hi everyone,

Please check me on this:

I am pretty conclusive that with Lazarus and the standard db components for SQLite3 (SQLite3 connection1) applications can only be single-user, even though SQLite3 has quite a bit of capability that can allow multiple access. Not the same as a server type db, of course. And this is in using the DBGrid.

The main reason, seems to me, that disconnecting the SQLite3Connection disables the dbGrid.

I was expecting that once one has the local data-set, one could disconnect, and still work on the dbGrid, connect back for only the small time necessary to update the database, but this does not seem possible.

If one uses other than DBGrid, perhaps this is achievable.

However, I don't see a reason why disconnecting disallows working with the local dataset and DBGrid.

This has nothing to do with "timing" and "synchronizing", as far as I can tell, since it's mostly a go-no-go situation.

I would appreciate some advice/correction on what I have gathered on this so far.

If so, I am going to try other components that can do concurrent use. I like the functionality of the standard components very much, except for this I find, and would hope is not so.

Thanks.

[Knipfty]

Hi Elmug,

In order for a dataset to connect to a DB, you need to have a connection.  DBGrid connects to a dataset thru a datasource.  If you change the connection object to false, you shutdown all access to the DB which includes the DBGrid.

You are not really saving anything by turning the connection off.  If you are trying to be able to read data only there is a connection property for ReadOnly.  I'm not sure, but am guessing that you would have to disconnect, change the property and reconnect.  This could be a but dis-concerning to you users as I'm pretty sure the data will disappear  and reappear.

Knipfty

[Elmug]

Hi Elmug,

In order for a dataset to connect to a DB, you need to have a connection.  DBGrid connects to a dataset thru a datasource.  If you change the connection object to false, you shutdown all access to the DB which includes the DBGrid.

You are not really saving anything by turning the connection off.  If you are trying to be able to read data only there is a connection property for ReadOnly.  I'm not sure, but am guessing that you would have to disconnect, change the property and reconnect.  This could be a but dis-concerning to you users as I'm pretty sure the data will disappear  and reappear.

Knipfty

For multi user access, you do have to disconnect the database, to liberate resources. That's actually the proper way to do it.

Connect, use local data set, connect to update, disconnect, etc.

Actually, connected-time should be a minimum. The way I find it to be, as said, is connection time is ALWAYS, or nothing, with the standard components.

That's for desktop use, or single user.

[Leledumbo]

For multi user access, you do have to disconnect the database, to liberate resources. That's actually the proper way to do it.

Nope, it's not required. See this. Not even other dbms I'm aware of incapable of handling multiple connections (after all, if you need to disconnect first, what's the point of having concurrency control?).

Connect, use local data set, connect to update, disconnect, etc.

Never heard of such a concept. Where did you get it?

[Knipfty]

Elmug,

I have not used SQLite all that much.  However, if you really need multi-user access, you should be running a real database server that has full concurrency control.  If you DB is residing on a network share, and you are expecting SQLite to manage the DB like a full DBMS, you may be in trouble.

I say may because the DBMS will have to resort to locking tables and indexes during updates while a server resorts to table locking only if row and page level locking are insufficient.

What I can tell you with certainty is that disconnecting will not give you the results you are looking for.

[Elmug]

For multi user access, you do have to disconnect the database, to liberate resources. That's actually the proper way to do it.

Nope, it's not required. See this. Not even other dbms I'm aware of incapable of handling multiple connections (after all, if you need to disconnect first, what's the point of having concurrency control?).

Connect, use local data set, connect to update, disconnect, etc.

Never heard of such a concept. Where did you get it?

I am checking right now with SQLite3, when one Lazarus app opens the database, another Lazarus appp can not open it, even if the first one is not writing. It is only because it has it opened.

SQLite states that various applications can have an SQLite3 opened but only one can write at one time, and in order to write, it has to lock the ENTIRE file. When you have a file open for reading you don't really have any say on it. Remember you open files both for reading and for writing. You open to write and then you should close immediately.

Server databases the good practice is to close after each transaction. Concurrency handles which transaction writes first and which next. After the server receives the data, it does not really need the client connected. The transaction writings are pretty fast.

Don't forget that the number of ports are limited too.

[Elmug]

Elmug,

I have not used SQLite all that much.  However, if you really need multi-user access, you should be running a real database server that has full concurrency control.  If you DB is residing on a network share, and you are expecting SQLite to manage the DB like a full DBMS, you may be in trouble.

I say may because the DBMS will have to resort to locking tables and indexes during updates while a server resorts to table locking only if row and page level locking are insufficient.

What I can tell you with certainty is that disconnecting will not give you the results you are looking for.

No. There are installations of SQLite3 that do a pretty good job with concurrency, using the right components that allow for multiuser access, unlike the default Lazarus, but I have no plans to use SQLite but for testing applications and data that I will move to Postgres.

The question is if any one can confirm that the Lazarus default components pretty much enable a single user setup.

If you have not used SQLite much, then to me it mostly means that we'd be going no where.

If some one does at least more than one user with SQLite and Lazarus default connections and DBGrid, I'd be very happy to know how.

Thanks.

[ludob]

For multi user access, you do have to disconnect the database, to liberate resources. That's actually the proper way to do it.

Connect, use local data set, connect to update, disconnect, etc.

Everybody is trying to re-use connections as much as possible to avoid the authorisation overhead of connecting. Connection pooling was invented for certain type of applications to solve the re-connection problem. You are saying everybody is wrong there?

I am checking right now with SQLite3, when one Lazarus app opens the database, another Lazarus appp can not open it, even if the first one is not writing. It is only because it has it opened.

Again wrong. The database is locked because a transaction has started and a select has been executed (the one to fill in the dbgrid). What is your source of information for all these statements?

As usual, you won't believe me. So here is a simple example of a program with a dbgrid that can be started as many times as you want and allows updating data simultaneously from the different programs, with the usual caveats of updating records that are deleted in another session but as a db expert you should be able to handle that easily. Nothing sqlite specific to that.

Code: [Select]

procedure TForm1.FormCreate(Sender: TObject);
begin
  SQLQuery1.SQL.Text:='create table if not exists test (id integer primary key, firstname varchar(20), lastname varchar(20))';
  SQLQuery1.ExecSQL;
  SQLQuery1.SQL.Text:='select * from test';
  SQLQuery1.Open;
  SQLTransaction1.CommitRetaining;
end;

procedure TForm1.BtnApplyClick(Sender: TObject);
begin
  SQLQuery1.ApplyUpdates;
  SQLTransaction1.CommitRetaining;
  SQLQuery1.Refresh;
  SQLTransaction1.CommitRetaining;
end;
Key to the operation is the CommitRetaining after every Refresh or Open (select) operation. Normally you wouldn't need the SQLQuery1.Refresh; but you don't want sqldb to do a refresh behind your back and blocking the database again. I'm not saying that you will be able to capture all cases where sqldb does a refresh behind your back (dblookup controls or master detail for example) but the demo shows clearly why the database gets locked.

The main reason, seems to me, that disconnecting the SQLite3Connection disables the dbGrid.

I was expecting that once one has the local data-set, one could disconnect, and still work on the dbGrid, connect back for only the small time necessary to update the database, but this does not seem possible.

If one uses other than DBGrid, perhaps this is achievable.

However, I don't see a reason why disconnecting disallows working with the local dataset and DBGrid.

This has nothing to do with "timing" and "synchronizing", as far as I can tell, since it's mostly a go-no-go situation.

Let me correct this because I wouldn't have other readers pick up on the confusion that reigns in this post.
- dbGrid is not disabled by disconnecting the SQLite3Connection. The dataset is closed when the connection goes away. DbGrid is still enabled but there is no data to show and work with.
- working with a disconnected set is possible with TSQLquery. It inherits from tbufdataset and setting the filename property of tsqlquery before closing the connection allows to continue working with the dataset. To disconnect your connection and continue working with the dataset do

Code: [Select]

  SQLQuery1.FileName:='store.dat';
  SQLQuery1.Close;
  SQLite3Connection1.Connected:=false;
  SQLQuery1.Open;
You can now work on the data without being connected. ApplyUpdates will obviously not work because that is meant to send data to the database back-end. 'store.dat' is not a special name and can be anything. With some precaution you can even stop the program and restart it later and pick up your local changes you made previously.
To reconnect do simply

Code: [Select]

  SQLite3Connection1.Connected:=true;
and you are back on-line. You can call ApplyUpdates and the local changes will be sent to the dabase.
- DBGrid is not related at all to this. Any data aware control behaves the same: closed dataset = no data to display.

[Elmug]

The dataset is closed when the connection goes away. DbGrid is still enabled but there is no data to show and work with.
- working with a disconnected set is possible with TSQLquery. It inherits from tbufdataset and setting the filename property of tsqlquery before closing the connection allows to continue working with the dataset. To disconnect your connection and continue working with the dataset do
Code: [Select]

  SQLQuery1.FileName:='store.dat';
  SQLQuery1.Close;
  SQLite3Connection1.Connected:=false;
  SQLQuery1.Open;

Thanks Ludob, I will check along this line and see if I can achieve what I said is needed for access from more than one user or application, which is to have the Connection OPEN most of the time, and only use it when reading or writing, as briefly as possible, meaning only a few milliseconds, for average needs.

This is what I was asking about, and not for arguments as to "keep the connection" closed, etc.

You don't mention how to use "store.dat" for reconnecting. If there is need for that, please do, if you don't mind.

Also, is "store.dat" a file actually written to disk, or is that a memory file?

Thanks again.

[ludob]

You don't mention how to use "store.dat" for reconnecting. If there is need for that, please do, if you don't mind.

The code I pasted is behind a de-connect and a re-connect button. Assigning a filename and closing the dataset will copy the current dataset to the file. Re-opening the dataset without a connection will load from file. To reconnect, just set the connection to true. The files is not used from there on. If you want you can assign the filename once in an oncreate event.

Code: [Select]

Also, is "store.dat" a file actually written to disk, or is that a memory file?AFAIK all filename properties in fpc/lazarus represent a physical file. This is not different.

This is what I was asking about, and not for arguments as to "keep the connection" closed, etc.

Well, if you are asking for help and start with the wrong facts then correcting them is part of the answer. The filename property is meant for applications that have off line/ on line (as in mobile) needs. Using that to solve a sqlite3 database locked problem is just overkill and wrong. I explained the use of filename to correct your wrong statement that off line use of datasets is not possible.

[Elmug]

Well, if you are asking for help and start with the wrong facts then correcting them is part of the answer.

.................
I simply started by asking the question, and there are some that without understanding it, jump on to suggest "facts" that even if they were, they did not apply to what I am doing.



I explained the use of filename to correct your wrong statement that off line use of datasets is not possible.
.....................................
Actually, this is something I did not state.

I do thank you for understanding my question. Your good answering it proves that there was nothing wrong with the question, and that more than answers, some  just throw in points of view, to which I replied with basis of MY case in question and needs of the moment.

I think where you mention confusion is because some don't quite understand the question and want to get into it any way.

In this case I know exactly why I asked and what I am after, if possible, as originally stated.

I really wish these things were not happening.

Thanks again.

[teos]

I am building a client-server database connection using Indy and TBufDataset, and maybe I can give some hints on this.

SQLLite allows multi-user access to a database file but that is not the approach one should follow: it simply is not a database server.

Funny to read how hard it seems to be to get data from a database in a situation where you can edit your data and send it back.  *GRIN*
We Delphians have used KbmMemtable for that in older Delphi versions, the MemDataset and the BufDataset of Lazarus do the job perfectly: Run a query on the SQLite database, copy the data from your TDataset to a memory dataset and of you go: you can disconnect untill the user edits data: in the afterpost event of the memory dataset, you build a query that runs a query again that updates your data.

In multi-user acces though, be aware that the last user who saves a record, is the record in the database, thus overwriting modifications of others.

I am writing code for the same, in a client-server approach where multi-user access is taken care of. So when retrieving data and before editing, a lock record is stored on the server and when posting data, the data is updated to SQlite and the lock is removed and the server signals for an update.

[anonim]

I have some strange problem with sqlite3, lazarus 1.0.8.:
I have 2 DBGrids in one form. I cannot save one of the tables, I get an exception, saying that the "code" field must be not null, but have filled this field!
code:

Code: [Select]

  with dmData do begin
    if (quSourceObj.Active) then begin
      quSourceObj.ApplyUpdates(0);
      SQLTransaction1.CommitRetaining;
    end;
    if quSettings.Active then begin //exception happens here
      quSettings.ApplyUpdates(0);
      SQLTransaction1.CommitRetaining;
    end;
  end;
If I don't call ApplyUpdates, my changes are not saved (as it shoud be) when I restart the app.
I tried to call ApplyUpdates for the problem table only. The same problem.
Strange thing is that IndexName=DEFAULT_ORDER for the quSettings query, while the second query in the same form has IndexName empty.
I tried to remove DEFAULT_ORDER but it is not possible to remove. When I exit the object inspector field it restores itself to DEFAULT_ORDER
Any ideas?

[Lacak2]

Are these two tables in master-detail relationship ? or are independent ?
Arfe you doing insert new record or update of existing record ?

[anonim]

Are these two tables in master-detail relationship ? or are independent ?
Arfe you doing insert new record or update of existing record ?

There is no relationship between tables. Table settings consists of fields Code, value, name, there is a unique index on Code.
I have faced with one more strange error saying something about "underlying dataset". The solution was create a new query and delete the old one. Apparently, there is something in the component or IDE code which is the reason (I have found a similar issue on the russian website, and there the solution was to delete queries and connection and then, recreate them)
When I deleted the TSQLQuery components and recreated both problems are gone. Strange. There were no events attached, SQL was "select * from Settings"