Lazarus

Programming => Databases => Topic started by: eshjim on June 28, 2016, 12:25:14 am

Title: [SOLVED} DB Edits and DBGrid blank after posting record
Post by: eshjim on June 28, 2016, 12:25:14 am
I am gradually getting to grips with Lazarus/SQLite, but could I request help on one further problem? I have a test master/detail database. The connections are set up in the data module's On Create event:

Code: Pascal  [Select][+][-]
  1. procedure TdmData.DataModuleCreate(Sender: TObject);
  2. var
  3.   strDbName: String;
  4. begin
  5.   strDbName := SysUtils.ExtractFilePath(ParamStr(0)) + 'Music.sqlite3';
  6.   sqLite3Connection1.DatabaseName := strDbName;
  7.   sqLite3Connection1.Connected:=True;
  8.   sqlTransaction1.Active:=True;
  9.   sqlQueryMaster.Active:=True;
  10.   sqlQueryDetail.Active:=True;
  11. end;

The queries are in their After Post events:

Code: Pascal  [Select][+][-]
  1. procedure TdmData.SQLQueryMasterAfterPost(DataSet: TDataSet);
  2. begin
  3.   SQLQueryMaster.Edit;
  4.   SQLQueryMaster.ApplyUpdates();
  5.   SQLTransaction1.Commit;
  6.   SQLQueryMaster.Close;
  7.   SQLQueryMaster.Open;
  8. end;
  9.  
  10. procedure TdmData.SQLQueryDetailAfterPost(DataSet: TDataSet);
  11. begin
  12.   SQLQueryDetail.Edit;
  13.   SQLQueryDetail.ApplyUpdates();
  14.   SQLTransaction1.Commit;
  15.   SQLQueryDetail.Close;
  16.   SQLQueryMaster.Open; // need to open Master query so that it is visible to
  17.                        // detail query
  18.   SQLQueryDetail.Open;
  19. end;

When the program runs, all data is displayed correctly, and the detail table scrolls with the master record. After editing either the master or detail record, both tables become inactive and the data disappears. Upon re-opening the program, the data reappears and the edits have been posted successfully. I am obviously missing something, so any advice would be greatly appreciated.
Title: Re: DB Edits and DBGrid blank after posting record
Post by: mangakissa on June 28, 2016, 08:42:41 am
You are doing to much. After post this is only what you need
Code: Pascal  [Select][+][-]
  1. procedure TdmData.SQLQueryMasterAfterPost(DataSet: TDataSet);
  2. begin
  3.   SQLQueryMaster.ApplyUpdates();
  4.   SQLTransaction1.Commit;
  5. end;
  6.  
  7. procedure TdmData.SQLQueryDetailAfterPost(DataSet: TDataSet);
  8. begin
  9.   SQLQueryDetail.ApplyUpdates();
  10.   SQLTransaction1.Commit;
  11.  end;
  12.  
Quote
  SQLQueryMaster.Open; // need to open Master query so that it is visible to
                       // detail query
That's not true. Your detail record is a part of your master record (same id in master / detail). The master table ia already open. This procedure doesn't do anything.

I think you save problems with the transactions. Use separate transactions or use option 'read committed'

Where did you get that information of posting a record?
Title: Re: DB Edits and DBGrid blank after posting record
Post by: eshjim on June 28, 2016, 02:58:21 pm
You are doing to much. After post this is only what you need
Code: Pascal  [Select][+][-]
  1. procedure TdmData.SQLQueryMasterAfterPost(DataSet: TDataSet);
  2. begin
  3.   SQLQueryMaster.ApplyUpdates();
  4.   SQLTransaction1.Commit;
  5. end;
  6.  
  7. procedure TdmData.SQLQueryDetailAfterPost(DataSet: TDataSet);
  8. begin
  9.   SQLQueryDetail.ApplyUpdates();
  10.   SQLTransaction1.Commit;
  11.  end;
  12.  
Quote
  SQLQueryMaster.Open; // need to open Master query so that it is visible to
                       // detail query
That's not true. Your detail record is a part of your master record (same id in master / detail). The master table ia already open. This procedure doesn't do anything.

I think you save problems with the transactions. Use separate transactions or use option 'read committed'

Where did you get that information of posting a record?

Thanks mangakissa for your reply. Unfortunately, stripping the code down as you suggested produces an error - "Operation cannot be performed on an inactive dataset". I do not understand this as both queries are activated on creation of the data module.  I also tried creating a separate transaction but the program does not even start, producing an error - "Cannot start a transaction within a transaction". I tried changing Commit to CommitRetaining, but this again on posting makes the data disappear and reappear upon restart.

Surely it should not be so difficult to code a simple query in Lazarus? In answer to your other question, I have seen the method I used for activating the queries in several of the tutorials, one example being http://www.pp4s.co.uk/main/tu-db-sqlite.html (http://www.pp4s.co.uk/main/tu-db-sqlite.html)
Title: Re: DB Edits and DBGrid blank after posting record
Post by: lindaandjon on June 29, 2016, 07:23:03 pm
I'm having the same problem on a master / detail table using FB2.5.  My records all delete fine when I delete a record but the grids all close. 
Using the same ApplyUpdates and Commit on a single dataset works fine and the dataset remains open, however on the M /D the dataset remains closed.  You can reopen them easily enough but why does it do this?  What am I missing?
Title: Re: DB Edits and DBGrid blank after posting record
Post by: rvk on June 29, 2016, 08:01:31 pm
Quote
Code: [Select]
SQLTransaction1.Commit;
Don't do a commit. Do a commitretaining.
The commit leaves your dataset closed.
If you do a commitretaining your dataset stays open.

So this should work:
Code: Pascal  [Select][+][-]
  1. procedure TdmData.SQLQueryMasterAfterPost(DataSet: TDataSet);
  2. begin
  3.   SQLQueryMaster.ApplyUpdates();
  4.   SQLTransaction1.CommitRetaining;
  5. end;
  6.  
  7. procedure TdmData.SQLQueryDetailAfterPost(DataSet: TDataSet);
  8. begin
  9.   SQLQueryDetail.ApplyUpdates();
  10.   SQLTransaction1.CommitRetaining;
  11. end;


You can see it with this piece of sourcecode (not line 5 of .Commit):
Code: Pascal  [Select][+][-]
  1. procedure TSQLTransaction.Commit;
  2. begin
  3.   if Active  then
  4.     begin
  5.     CloseDataSets;
  6.     if (stoUseImplicit in Options) or SQLConnection.AttemptCommit(FTrans) then
  7.       begin
  8.       CloseTrans;
  9.       FreeAndNil(FTrans);
  10.       end;
  11.     end;
  12. end;
  13.  
  14. procedure TSQLTransaction.CommitRetaining;
  15. begin
  16.   if Active then
  17.   begin
  18.     SQLConnection.CommitRetaining(FTrans);
  19.   end;
  20. end;
(stripped some logevent lines for clarity)
Title: Re: DB Edits and DBGrid blank after posting record
Post by: lindaandjon on June 29, 2016, 09:36:15 pm
Thanks, that's what's wrong.  I was just trying to figure out why my combo lookup table closed as well.
Title: Re: DB Edits and DBGrid blank after posting record
Post by: eshjim on June 30, 2016, 12:28:50 am
Don't do a commit. Do a commitretaining.
The commit leaves your dataset closed.
If you do a commitretaining your dataset stays open.

So this should work:
Code: Pascal  [Select][+][-]
  1. procedure TdmData.SQLQueryMasterAfterPost(DataSet: TDataSet);
  2. begin
  3.   SQLQueryMaster.ApplyUpdates();
  4.   SQLTransaction1.CommitRetaining;
  5. end;
  6.  
  7. procedure TdmData.SQLQueryDetailAfterPost(DataSet: TDataSet);
  8. begin
  9.   SQLQueryDetail.ApplyUpdates();
  10.   SQLTransaction1.CommitRetaining;
  11. end;

I stripped my original code down as you and mangakissa suggested and changed Commit to CommitRetaining.  Not reopening the queries after the CommitRetaining produces the error "Operation cannot be performed on an inactive dataset".  Reopening or reactivating the queries still blanks the master and detail records and the new or edited post reappears upon restarting the program.  I have spent days now on this without success but I feel it is something very basic.  Have I got the code in the right place - the SQLQuery AfterPost event?
 

Title: Re: DB Edits and DBGrid blank after posting record
Post by: rvk on June 30, 2016, 12:39:01 am
I have spent days now on this without success but I feel it is something very basic.  Have I got the code in the right place - the SQLQuery AfterPost event?
Yes, you have it in the right place. So there must be a problem elsewhere in your code. The reason for a stripped down version is that you can place the complete test project here. (With a project > publish project and zip the resulting directory)

It could be a problem with your sql-statement or even with the way you connected master and detail.

Also the exact place of the error (Operation cannot be performed on an inactive dataset) is important because it can't be in afterpost itself.
Title: Re: DB Edits and DBGrid blank after posting record
Post by: rvk on June 30, 2016, 10:05:49 am
@eshjim, I've created a very simple master/details example.
It uses SQLite with foreign keys.
(it was an example I had for testing that if you remove a master all the detail-records would be deleted too)

It's a selfcontained example. It will create a test-database on its own.
(just drop in the sqlite.dll if you don't have it in your search path.)

You can see that when you add a master-record and post it (with the dbnavigator) the datasets stay open.

If you still have trouble with your own code you need to show us how you created the table and some code as to how you add records etc.
Title: Re: DB Edits and DBGrid blank after posting record
Post by: eshjim on June 30, 2016, 02:12:32 pm
Thank you so much @rvk for your effort. I was in the process of replying to your previous message with the architecture of my program, but I will download your example a little later today then see if I can apply the same principles to my example.  Once again, many thanks for your valued help.
Title: Re: DB Edits and DBGrid blank after posting record
Post by: eshjim on July 02, 2016, 12:27:59 am
@rvk  Well, I looked at your project, totally understood it and amended mine in the places where we differed.  Now, when I perform an edit, I get the "Operation cannot be performed on an inactive dataset" error but, when the program re-opens, the edits have been posted!!  I have taken the liberty of attaching a zipped copy of the project (hope I've done it right)  :-[.  Obviously, I omitted your database creation code as the tables were already constructed and in operation.  The tables were created thus:

Master -
Code: Pascal  [Select][+][-]
  1. CREATE TABLE `Recartst` (
  2.         `Reference`     VARCHAR(5) NOT NULL,
  3.         `Title` VARCHAR(50) NOT NULL,
  4.         `Artist`        VARCHAR(80) NOT NULL,
  5.         `Christmas`     VARCHAR(1),
  6.         `Status`        INTEGER,
  7.         PRIMARY KEY(Reference)
  8. );

Detail -
Code: Pascal  [Select][+][-]
  1. CREATE TABLE `Rectrack` (
  2.         `Reference`     VARCHAR(5) NOT NULL,
  3.         `TrackOrder`    INTEGER NOT NULL,
  4.         `Track` VARCHAR(100) NOT NULL,
  5.         `Artist`        VARCHAR(80) NOT NULL,
  6.         FOREIGN KEY(`Reference`) REFERENCES `Recartst`(`Reference`) ON DELETE CASCADE
  7. );

You will note that the project has several units but at the moment only the data module has any coding.  This is a working program which I did in Delphi 5 ten or so years ago.  To familiarise myself with Lazarus/FPC, I decided to construct it from scratch in Lazarus rather than attempt a conversion.  I knew that the database side of things would be the most problematic as, in Delphi, of course, the trusty old BDE took care of most of the donkey work!!  I'm confident that the rest of the coding will not be as difficult.

I would be grateful if you would cast your expert eye over this when you have some time and see if you can spot where I'm going wrong.

Many thanks.
Title: Re: DB Edits and DBGrid blank after posting record
Post by: rvk on July 02, 2016, 12:17:51 pm
I've looked at your code and I see you have set the options of SQLQueryMaster and SQLQueryDetail to AutoCommit, AutoApply and CancelUpdatesOnRefresh. A default TSQLQuery has non of these. The Options is empty.

So you are trying to do two things at the same time. The component does an autocommit (and not a commitretaining) and the AfterPost does an commitretaining.

If you remove all options from SQLQueryMaster and SQLQueryDetail, your code should run fine.


(one sidenote: you have created a very large form. I couldn't even fit it on my screen. So if you're targeting multiple systems you might want to create a more flexible screen which can be resized for multiple screensizes)
Title: Re: DB Edits and DBGrid blank after posting record
Post by: eshjim on July 02, 2016, 01:00:27 pm
Hallelujah!!  Thank you so much for that.  After over a week of utter frustration, I knew it had to be something simple like that.  I can vaguely remember setting these options at the recommendation of one of the many tutorials I consulted.  Of course, all tutorials have a difficult job to do because every single program's requirements are different.

Thanks also for your comments on the size of the form.  To be honest, although this program is only for my personal use, I do like to make them look as professional as possible.  There's a lot more coding to do on this one then the fine tuning can take place!

Many thanks for your assistance.

Title: Re: [SOLVED} DB Edits and DBGrid blank after posting record
Post by: goodname on July 04, 2016, 03:25:05 pm
I'm a little late to this discussion but may I suggest setting the TSQLQuery sqoKeepOpenOnCommit option with Commit instead of using CommitRetaining. This is especially useful for read only queries as long running idle transactions will not happen on the database server and the client will keep the dataset open for usage.

Default behaviour:  Commit closes dataset and transaction. CommitRetaining commits and reopens a new transaction automatically which for read only select query is pointless and wastes server resources.

sqoKeepOpenOnCommit: Commit retains the dataset and closes the transaction. CommitRetaining commits and reopens a new transaction automatically which for read only select query is pointless and wastes server resources.
Title: Re: [SOLVED} DB Edits and DBGrid blank after posting record
Post by: rvk on July 04, 2016, 03:47:18 pm
CommitRetaining commits and reopens a new transaction automatically which for read only select query is pointless and wastes server resources.
If you want to commit something you're not dealing with "read only" select queries  :D
(otherwise you wouldn't have to commit anything)

But you're correct. The sqoKeepOpenOnCommit  options was added around 1.4+ (I think) and it will keep the dataset open.

About the transaction being open... The transaction always become active after the dataset opened. Also when using sqoKeepOpenOnCommit and doing a commit... the transaction is directly activated again when you browse the dataset. So other then the convenience of using Commit, without needing to think about it being closed, I don't think there is any other advantage. It's not possible to browse a dataset without the transaction being active (at least in the normal transactional databases).

Title: Re: [SOLVED} DB Edits and DBGrid blank after posting record
Post by: goodname on July 04, 2016, 04:04:39 pm
sqoKeepOpenOnCommit was added in FPC 3.0.  May have focused a little too much on read-only queries but anything that reduces or removes long running idle transactions is good and commitRetaining usually creates these types of transactions.

the transaction is directly activated again when you browse the dataset.

Are you saying that if for example a onScroll event happens that the transaction is opened again? I hope this is not the case.
Title: Re: [SOLVED} DB Edits and DBGrid blank after posting record
Post by: rvk on July 04, 2016, 04:15:30 pm
Are you saying that if for example a onScroll event happens that the transaction is opened again? I hope this is not the case.
Yes, I just tested it with a small project (see attached).
After adding a Contact, when you start to browse the transaction is activated again. I think this is correct because every reading needs to have it's own transactional-context. I think that's just how it works. So you would always have a transaction open, even for just reading.

Quote
Any SQL Statement starts a transaction in Oracle.
http://stackoverflow.com/a/4488944/1037511

(when you set stoExplicitStart and don't activate the transaction before opening the dataset you'll get an error)
Code: [Select]
Error: attempt to implicitly start a transaction on Connection "SQLite3Connection1", transaction "SQLTransaction1".
(my test-project even crashes with an error when exiting)
Code: [Select]
Operation cannot be performed on an active dataset
Title: Re: [SOLVED} DB Edits and DBGrid blank after posting record
Post by: goodname on July 04, 2016, 04:46:09 pm
So you would always have a transaction open, even for just reading.
Yes you must open a transaction for each query or set of queries. It is best practice to close it when your finished using it as open idle transactions do use database server resources. Databases engines usually consider long running idle transactions to be a client side bug.  I'm guessing that a onScroll event triggers a database refresh which means that I'm going to have to put Commit's into the afterScroll events to prevent these idle transactions.
Title: Re: [SOLVED} DB Edits and DBGrid blank after posting record
Post by: rvk on July 04, 2016, 05:07:38 pm
... which means that I'm going to have to put Commit's into the afterScroll events to prevent these idle transactions.
Or .Rollback. No need to commit if you haven't changed anything.

Also please note that this ONLY a problem when you use a master/details dataset.
The master dataset internally closes and opens the details dataset which in turn seem to activate the transaction.

Also the transaction is always automatically activated when opening the dataset so you could (for a master/details dataset) set the Rollback of the transaction in the AfterOpen-event. That seems to work ok.

Code: Pascal  [Select][+][-]
  1. procedure TForm1.MasterAfterOpen(DataSet: TDataSet);
  2. begin
  3.   if SQLTransaction1.Active then
  4.     SQLTransaction1.Rollback;
  5. end;
  6.  
  7. procedure TForm1.DetailAfterOpen(DataSet: TDataSet);
  8. begin
  9.   if SQLTransaction1.Active then
  10.     SQLTransaction1.Rollback;
  11. end;

Although you need to be careful with Rollback. In a Master/details dataset the details could be closed/opened while you're editing a master in which case you can loose the edits. But if you're sure it's just a read-only dataset Rollback is better.

Title: Re: [SOLVED} DB Edits and DBGrid blank after posting record
Post by: goodname on July 04, 2016, 06:33:55 pm
For a moment there I thought this issue applied to all queries. It makes sense that it happens in master/client queries as client queries get new parameter data from the master query every time the master scrolls.

My understanding is that rollback can trigger a warning message if called outside of a transaction while commit does not. Don't think it is a big issue which is used as they will both work the same for read only queries. For write queries the number of possibilities is greatly increased so greater planning and testing are required. Previous to fpc 3.0 and sqoKeepOpenOnCommit there was no way to close a transaction without closing the dataset as well which meant long running idle transaction were unavoidable.
TinyPortal © 2005-2018