[SOLVED} DB Edits and DBGrid blank after posting record

[eshjim]

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.

[mangakissa]

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.  

  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?

[eshjim]

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.  

  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

[lindaandjon]

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?

[rvk]

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)

[lindaandjon]

Thanks, that's what's wrong.  I was just trying to figure out why my combo lookup table closed as well.

[eshjim]

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?
 

[rvk]

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.

[rvk]

@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.

[eshjim]

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.

[eshjim]

@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.

[rvk]

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)

[eshjim]

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.

[goodname]

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.

[rvk]

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 
(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).