Is it timing of a dataset?

[CraigC]

Insert SQL seem to be working just fine.  UPDATE sql was the problem.  but it was the query error not being in dsEdit was the only error. SQL below is after the fields were removed.  Yes, your suggestion on how to check state worked perfectly.  Thank you.

Code: [Select]

   SQL.Add('SELECT UID, ProductNo, ProductDescription, UOM, ');
    SQL.Add('StdQty, UnitPrice, StdCost, ');
    SQL.Add('Suspended, ProductClass, ProductNote, ');
    SQL.Add('Taxable1, Taxable2 FROM Inventory ORDER BY ProductNo');
    {--------}
    UpdateSQL.Clear;
    UpdateSQL.Add('UPDATE INVENTORY SET ProductNo=:ProductNo, ProductDescription=:ProductDescription, ');
    UpdateSQL.Add('UOM=:UOM, StdQty=:StdQty, UnitPrice=:Unitprice, StdCost=:StdCost, Suspended=:Suspended, ');
    UpdateSQL.Add('ProductClass=:ProductClass,ProductNote=:ProductNote, ');
    UpdateSQL.Add('Taxable1=:Taxable1, Taxable2=:Taxable2 WHERE UID=:OLD_UID');
    {--------}
    DeleteSQL.Clear;
    DeleteSQL.Add('DELETE FROM Inventory WHERE (UID = :OLD_UID)');
    {--------}
    InsertSQL.Clear;
    InsertSQL.Add('INSERT into Inventory ');
    InsertSQL.Add('(ProductNo, ProductDescription, UOM, StdQty, UnitPrice, StdCost, ');
    InsertSQL.Add('Suspended, ProductClass, ProductNote, ');
    InsertSQL.Add('Taxable1, Taxable2) VALUES ');
    InsertSQL.Add('(:ProductNo, :ProductDescription, :UOM, :StdQty, :UnitPrice, :StdCost, ');
    InsertSQL.Add(':Suspended, :ProductClass, :ProductNote, :Taxable1, :Taxable2) ');
    RefreshSQL.Clear;
    RefreshSQL.Add('SELECT UID, ProductNo, ProductDescription, UOM, StdQty, UnitPrice, StdCost, ');
    RefreshSQL.Add('Suspended, ProductClass, ProductNote, ');
    RefreshSQL.Add('Taxable1, Taxable2 from Inventory WHERE (UID = :UID) ');     


[rvk]

Ok. That seems to be ok (but it works now).

BTW. Did this solve the problem from your original post???

Code: Pascal  [Select][+][-]

1. qry.Post;
2. qry.ApplyUpdates;
3. dmCommonData.transLedger.CommitRetaining;

If you do that (.Post before ApplyUpdates) does it work then?

[CraigC]

yes, qry.post before ApplyUpdates does work.  However, look at the screenshots.  the Lazarus grid doesn't show the two new items entered but the data from the server shows it correctly. In the Lazarus grid, check the bottom record.   The query will need to be refreshed or closed then reopened.

[rvk]

yes, qry.post before ApplyUpdates does work.  However, look at the screenshots.  the Lazarus grid doesn't show the two new items entered but the data from the server shows it correctly. In the Lazarus grid, check the bottom record.   The query will need to be refreshed or closed then reopened.

I'm starting to see what you mean.
Are you saying that when you post, update and commit your changes in a TSQLQuery, that the Grid doesn't show this until it closes and opens the dataset again. If that's the case then I know what you are missing.

In that case you need to learn more about transactions. Remember I said that a transaction is always automatically started when you open a dataset? That's because transaction is done in levels. When you open a dataset, a transaction is started, and depending on the sort of transaction, EVERYTHING you do is done in a sort of snapshot of the database at that point.

So, if in ANOTHER transaction someone adds a new record, that record is not yet visible in any of the other transaction anyone that has a table open. So if the TDBGrid/TSQLQuery has a different transaction than the one with which you added a record, that record is not visible until you close and open the table and start a new "viewing" transaction.

BTW. This includes dbgrids from other programs which might show the database (like a database manager).

So, yes, if you do a "Add record" in another transaction, that record is not visible in a TDBGrid/TSQLQuery which might already be open.
You do a "save position", close, open, "restore position" on the TDGrid. I even have some extra functions which saves the exact location of the selected record (even to the first record visible in the TDBgrid), to be able to restore it exactly to the same state as before the close/open.

https://www.sqlite.org/lang_transaction.html

While a read transaction is active, any changes to the database that are implemented by separate database connections will not be seen by the database connection that started the read transaction. If database connection X is holding a read transaction, it is possible that some other database connection Y might change the content of the database while X's transaction is still open, however X will not be able to see those changes until after the transaction ends. While its read transaction is active, X will continue to see an historic snapshot of the database prior to the changes implemented by Y.

[egsuh]

So, if in ANOTHER transaction someone adds a new record, that record is not yet visible in any of the other transaction anyone that has a table open. So if the TDBGrid/TSQLQuery has a different transaction than the one with which you added a record, that record is not visible until you close and open the table and start a new "viewing" transaction.

CraigC does not seem to mean this. With only a single DataSet is open (with single transaction), new records are entered and posted, and then ApplyUpdates is called. And then the new records disappear.

This should happen if we assume :

1)  dataset is open with transaction A.
2)  inserting and posting records are done with transaction B, or at least ApplyUpdates is done with transaction B
3)  After Applyupdates, it comes back to the state of transaction A.

Is what's happening and possible?
 

[egsuh]

@rvk,

With RollbackRetaining the transaction is rolled back, but the transaction and table remain active and open.

If this means transaction is still active after commitretaining/rollbackretaining, then should it be committed or rolledback afterwards explicitly? I'm afraid that the transaction might remain active even after the TSQLQuery / TSQLTransaction are destroyted, if (as I assume) transaction is maintained by database server?

[rvk]

With RollbackRetaining the transaction is rolled back, but the transaction and table remain active and open.

If this means transaction is still active after commitretaining/rollbackretaining, then should it be committed or rolledback afterwards explicitly? I'm afraid that the transaction might remain active even after the TSQLQuery / TSQLTransaction are destroyted, if (as I assume) transaction is maintained by database server?

When you do CommitRetaining, the transaction is committed to the database and other clients could then read your committed values.
Because of Retaining, the transaction remains active. Essentially you could see it as a Commit & Start transaction in one.

And yes, when you close the TSQLQuery, the transaction remains active. If you have uncommitted records, they are still not visible to others (or yourself in a different transaction) unless you did a CommitRetaining.

Only when you Commit or Rollback the transaction, the transaction is released.
Also note... that freeing a TSQLTransaction has a default action. You could set it to Commit or Rollback. That ensures that when you free the TSQLTransaction (or close the program) the transaction is freed on the DB server.

So if you have created a TSQLTransaction with the owner as TSQLQuery, just doing TSQLQuery.Free will also free the TSQLTransaction (because owners also free owned components) and the default action of that TSQLTransaction is automatically executed.

I often do sql.Transaction := TSQLTransaction.Create(sql) to make sql the owner (so it's automatically freed).

If you have a larger program where you have multiple tables and multiple TSQLQuery's, then you might want an independent TSQLTranaction so it oversees all those TSQLQuery's. In that case you don't want the TSQLTransaction to be closed when one of those queries close and you want to do that manually (or give the user a "Apply all" or "Cancel all" button or something).

[egsuh]

Hmmm ...
I'm running FastCGI, and am checking the transaction at the "AfterResponse".

Code: Pascal  [Select][+][-]

1. procedure TwmAQs.DataModuleAfterResponse(Sender: TObject; AResponse: TResponse);
2. begin
3.    if aqfb5.trAQ.Active then begin
4.       aqfb5.trAQ.Commit;  // This is Called.
5.       Log('Module s _ Transaction.commit called at DataModuleAfterResponse');
6.    end;
7. end;
8.  

And sometimes this log is executed.

With FastCGI, this webmodules are created / destroyed for every request, but there is another datamodule, which  contains database and all related methods. All DB operations are done within this datamodule. There are several commitretaining/rollbackretaining calls.

at https://www.freepascal.org/docs-html/fcl/sqldb/tsqltransaction.html, TSQLTransaction.Action does not do anything.

And https://www.freepascal.org/docs-html/fcl/sqldb/tsqltransaction.options.html says stoUseImplicit means that no explicit transaction start and stop commands will be sent to the server when the Commit or Rollback methods are called (effectively making them a no-op at the DB level).

And I experienced that while one transaction is staying active and other operations are done on DB, at some point the DB is slowed down very much.

In the previous case, I check with trAQ, but many transactions are created and destroyed temporarily. Not sure all those transactions are stopped at the destruction.

[rvk]

With FastCGI, this webmodules are created / destroyed for every request, but there is another datamodule, which  contains database and all related methods. All DB operations are done within this datamodule. There are several commitretaining/rollbackretaining calls.

With Retaining the transaction would be active so it would trigger that log.

at https://www.freepascal.org/docs-html/fcl/sqldb/tsqltransaction.html, TSQLTransaction.Action does not do anything.

O wow... property Action: TCommitRollbackAction; [rw] Currently unused in SQLDB
That really seems to be an error.
From the source:

Code: Pascal  [Select][+][-]

1. procedure TSQLTransaction.EndTransaction;
2. begin
3.   Case Action of
4.     caCommit, caCommitRetaining :
5.       Commit;
6.     caNone,
7.     caRollback, caRollbackRetaining :
8.       if not (stoUseImplicit in Options) then
9.         RollBack
10.       else
11.         CloseTrans;
12.   end;
13. end;

And EndTransaction is called on freeing TSQLTransaction. So that really is an old wiki.

And I experienced that while one transaction is staying active and other operations are done on DB, at some point the DB is slowed down very much.

In the previous case, I check with trAQ, but many transactions are created and destroyed temporarily. Not sure all those transactions are stopped at the destruction.

From the source you see that in TSQLTransaction is the transaction is freed when the component is destroyed. So as long as the CGI destroys the component you shouldn't have lingering transactions.

What database is used? In some databases you can view the current running transactions.

For example... I have this in Firebird (to see the transactions and statements):

Code: SQL  [Select][+][-]

1. SELECT
2.   ma.mon$attachment_id,
3.   ma.mon$server_pid,
4.   ma.mon$state,
5.   ma.mon$attachment_name,
6.   ma.mon$remote_protocol,
7.   ma.mon$remote_address,
8.   ma.mon$remote_process,
9.   ma.mon$timestamp,
10.   mt.mon$transaction_id,
11.   mt.mon$timestamp,
12.   CAST(ms.mon$sql_text AS CHAR(5000)) AS Sql_statement
13. FROM mon$attachments ma
14. LEFT JOIN mon$transactions mt ON mt.mon$attachment_id=ma.mon$attachment_id
15. LEFT  JOIN mon$statements ms ON ms.mon$transaction_id=mt.mon$transaction_id
16. ORDER BY 1

[egsuh]

I'm using Firebird. I don't know. All temporary transactions are destroyed. 

[rvk]

And I experienced that while one transaction is staying active and other operations are done on DB, at some point the DB is slowed down very much.

I'm using Firebird. I don't know. All temporary transactions are destroyed.

If all transactions are destroyed and there are no long running transaction, the slowness must be caused by something else.