Is it timing of a dataset?

[egsuh]

I haven't looked into the codes here in depth, but I'd like to mention basic structure of SQL databases, which I had difficulty when I first entered the SQL world.

What are called as TDataSet and its decendants like TSQLQuery, etc. are separate entities in the of SQL DB world. If you are operating with local tables, e.g. csv files, then the TDataSets (TCSVDataSet, TBufDataSet, etc.) are directly linked to the physical DB on HDD.

But in SQL, once you open a dataset, like

  SQLQuery.Transaction.Active := True;
  SQLQuery.SQL.Text := 'select * from table1';
  SQLQuery.Open;
  (SQLQuery.Transaction as TSQLTransaction).RollbackRetaining; // with this, Server DB file is disconnected.
 

You'd better think that this makes a copy of the subset of the table (the whole table in this case) from physical table on HDD.

And following operations on this dataset, like

       SQLQuery.Edit;
       SQLQuery.FieldByName('f1').AsInteger := 199999;
       SQLQuery.Post;

operate only on the TSQLQuery, which is "COPY" of original table. Here, you may insert a record and then cancel it.


If all options are manual (nothing is done automatically), then you should start transaction yourself again, because this is related with ServerDB, not local TDataSet.


  SQLQuery.Transaction.Active := True;
  SQLQuery.ApplyUpdates;
  (SQLQuery.Transaction as TSQLTransaction).Commit; // with this, Server DB file is disconnected.
 


If you rollback transaction after ApplyUpdates, then the changes will not be saved to the permanent DB.

And "commiting" after ApplyUpdates will close SQLQuery. If you call CommitRetaining, then the updates will be applied (changes will be saved to the permanent DB), but the SQLQuery DataSet will still remain open (as dsBrowse).

And there are options like AutoApplyUpdates, AutoCommit, etc. autocommit, etc. Possibly AutoApplyUpdates will carry out ApplyUpdate as soon as "post" is done. With AutoCommit, any updates to the permanent DB will be committed automatically, I guess. Using these options you can  alleviate starting and stopping transactions.

You may already know on these. Sorry if you do. If not, please check your codes step by step. 

========================

I made mistake. I thank rvk for correcting them (below).

[rvk]

But in SQL, once you open a dataset, like

  SQLQuery.Transaction.Active := True;
  SQLQuery.SQL.Text := 'select * from table1';
  SQLQuery.Open;
  (SQLQuery.Transaction as TSQLTransaction).RollbackRetaining; // with this, Server DB file is disconnected.
 

You'd better think that this makes a copy of the subset of the table (the whole table in this case) from physical table on HDD.

And following operations on this dataset, like

       SQLQuery.Edit;
       SQLQuery.FieldByName('f1').AsInteger := 199999;
       SQLQuery.Post;

And you think that when you do SQLQuery.Edit that the transaction doesn't automatically start again? If it doesn't, you get an error that you can't edit a record without a transaction.

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

So just above ApplyUpdates the transaction is already active and setting it to true there is useless.

Transactions are always from the opening/reading of a table until you close the transaction. You can't read and edit records without being in a transactional state (that's the whole point of transactions).

Above is the case with normal databases (like MySQL, SQLite, mssql, firebird, etc).

Are you saying that with TBufDataSet you can edit a record without a transaction active??
That might be possible because TBufDataSet doesn't need transaction because it operates completly locally. But when dealing with databases, you do need them.

[egsuh]

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

So just above ApplyUpdates the transaction is already active and setting it to true there is useless.

Transactions are always from the opening/reading of a table until you close the transaction. You can't read and edit records without being in a transactional state (that's the whole point of transactions).

You are right. Transaction remains Active after RollbackRetaining or CommitRetaining is called.  So SQLQuery.Transaction.Active := True above ApplyUpdates is not necessary.  I make a copy of TSQLQuery to TBufDataSet because the SQLQuery is from over the Internet, so on my local application there are no transactions.

[CraigC]

I put together a sample app and database.  Events for dataset change do not jive. As soon as I edit a field it should fire and it does not.

 Can't refresh due to pending updates. etc. Look at the screenshots. Will upload the sample soon.

Craig

Procedure TForm1.dsDataChange(Sender: TObject; Field: TField);
begin
  if (field = nil) then exit;
  sb.Panels.Items[0].Text := 'Field: '+ Field.FieldName;
  if qry.UpdateStatus = usInserted then
    sb.Panels.Items[1].Text := 'Data in Insert Mode'
  else if qry.UpdateStatus = usModified then
    sb.Panels.Items[1].Text := 'Data in Edit Mode'
  else if qry.UpdateStatus = usUnModified then
    sb.Panels.Items[1].Text := 'Data in Browse Mode';
end;

procedure TForm1.dsStateChange(Sender: TObject);
begin
  if qry.UpdateStatus = usInserted then
  Caption := 'Data in Insert Mode'
  else if qry.UpdateStatus = usModified then
    Caption := 'Data in Edit Mode'
  else if qry.UpdateStatus = usUnModified then
    Caption := 'Data in Browse Mode';
end;                                             

[rvk]

procedure TForm1.dsStateChange(Sender: TObject);
begin
  if qry.UpdateStatus = usInserted then
  Caption := 'Data in Insert Mode'
  else if qry.UpdateStatus = usModified then
    Caption := 'Data in Edit Mode'
  else if qry.UpdateStatus = usUnModified then
    Caption := 'Data in Browse Mode';
end;                                           

Can you use the code-tags in the future for code? It makes it a lot more readable. You can use the # above the editor.

Anyway... why are you using TSQLQuery.UpdateStatus for the mode? The dsStateChange is connected to a TDatasource. To avoid confusion you might want to use the actual TDataSource for showing its TDataSource.State. Then you are sure you are showing the correct TDatasource with its state.

Can you change it in this (so really using the State of the TDataset connected to the TDataSource).

(Sender should be a TDatasource automatically because you attached dsStateChange to a TDataSource)

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

1. procedure TForm1.dsStateChange(Sender: TObject);
2. begin
3.   if TDataSource(Sender).State = dsInsert then
4.     Caption := 'Data in Insert Mode'
5.   else if TDataSource(Sender).State = dsEdit then
6.     Caption := 'Data in Edit Mode'
7.   else if TDataSource(Sender).State = dsBrowse then
8.     Caption := 'Data in Browse Mode'
9.   else
10.     Caption := 'Data is in Unknown Mode';
11. end;

[CraigC]

 TDataSource(Sender).State >>>

this is more accurate. Thank you.  The qry.updatestatus was something I found on Google and thought I'd try it.   I'll do more testing.  I want to be able to do an update and be able to do a refresh. Up till now, it would not.

Not sure what you are referring to in regards to tags?

Craig

[egsuh]

Not sure what you are referring to in regards to tags?

Please mark the "codes" part --- your pascal program by Shift + Key_down or Key_up.
And then press "#" button that is above the white box into which you are typing in text.

[rvk]

Not sure what you are referring to in regards to tags?

Look at the code in your post and then look at the code in my post.
In my post the code is in a code-block neatly formatted.
Your code is proportional and not formatted.

The problem with using code outside a code-block, like you did, is that for one the text is proportional (and jumps all over the place).
But what is worse is that it can corrupt your code on screen.
For example if you use Array[ i ] for referencing an index i in an array (without the spaces around the i) the forum software will change it into "switch to italic" and the [ i ] is not visible.

if array = 0 then // <--- this goes wrong and corrupts your code (there is a [ i ] after array which you don't see) !!

Code: [Select]

if array[i] = 0 then // <-- (this is correct)
You can type [ code ] and [/ code ] around your code yourself (without the spaces) or select your code and press the # button at the top of the editor when creating a post.

[CraigC]

Thanks, I will give that a try next time.

Code: [Select]

You can type [ code ] and [/ code ] around your code yourself (without the spaces) or select your code and press the # button at the top of the editor when creating a post.

[CraigC]

After a fairly long discussion with Google AI.   I tried what I had already done in the sample app.  Reduce the number of fields in the table and to do a POST before APPLYUPDATES.  I had 24 fields or so and I reduced it to 12 I think.  (I'll have to create another table for the other fields)  It works.  Don't know if this is an issue with Lazarus or SQLite3.  But it works and I can move on after fighting this for too long.  Honestlt, I don't know if doing a POST does help, I'll have to do more testing.

thought this might help someone else.

Craig

[rvk]

I had 24 fields or so and I reduced it to 12 I think.  (I'll have to create another table for the other fields)  It works.  Don't know if this is an issue with Lazarus or SQLite3.

It's not a problem with Lazarus or SQLite itself.
But it could be that you have some fields named incorrectly.
Reserved names or the SQL for UPDATE/INSERT is constructed incorrectly.

But 24 fields or more shouldn't be a problem onto itself.
(I think SQLITE_MAX_COLUMN is 2000 so 24 shouldn't be a problem)

(Maybe you find this out when adding the other 12 fields if they are named incorrectly)

[CraigC]

Code: [Select]

    MfgProductNo       Char(20),
    VendorUID          Integer,
    SalesAccount       Char(20),
    COSAccount         Char(20),
    InventAccount      Char(20),
    Allocated          Integer,
    OnHand             Integer,
    OnOrder            Integer,
    MonthlySales       Integer,
    YearlySales        Integer,
    MinQty             Integer,
    MaxQty             Integer,
    ReorderQty         Integer,

these are the fields removed.

[rvk]

Code: [Select]

    MfgProductNo       Char(20),
    VendorUID          Integer,
    SalesAccount       Char(20),
    COSAccount         Char(20),
    InventAccount      Char(20),
    Allocated          Integer,
    OnHand             Integer,
    OnOrder            Integer,
    MonthlySales       Integer,
    YearlySales        Integer,
    MinQty             Integer,
    MaxQty             Integer,
    ReorderQty         Integer,

these are the fields removed.

Allocate or Allocated (not sure which) is a reserved word in some of the SQL variants. But I don't think it is one in SQLite. The others are definitely permitted.

[CraigC]

I checked Allocated on SQLite's website, it's not listed.  I may just to be on the safe side, rename that field.

[rvk]

I checked Allocated on SQLite's website, it's not listed.  I may just to be on the safe side, rename that field.

BTW. Did the caption in that latest change (with the code I gave) still show the dataset was in Edit mode?

Further... from your original problem... the insert worked correctly ?? (then it's not a field problem).
If only UPDATE and refresh don't work correctly... it could also just be an update problem with the UPDATE SQL.

Did you create your own UPDATE statements or are you using the automatically generated ones?
Maybe showing the complete SQLs would help.