[SOLVED] Firebird sqldb dbgrid: how to edit?
lazarus / 16-Databases

----------------------------------------------------------------------
BigChimp · 2011-08-29 15:43

Hi all,

I've been updating some Lazarus tutorials, but I'm having a problem
(due to my lack of knowledge):
I'm trying to extend this tutorial:
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1
to include editing a dbgrid using the Firebird SQLdb components.

2 things:
1. First:
If I use this query:
Code: [Select]select * from CUSTOMERthen you can edit a cell in the
the dbgrid, and e.g. move to another line, but the changes don't
persist after closing the application.
If I use a parametrised query like:
Code: [Select]select * from CUSTOMER where COUNTRY = :COUNTRYany edits
in a dbgrid cell disappear when leaving the cell.
I don't understand why the grid should react differently...

2. Any changes are not persisted to the database.

I'm afraid I might be doing elementary things wrong with transactions,
but there might also be problems with the connector itself.
This:
http://wiki.lazarus.freepascal.org/Firebird_in_action#Some_problems_wi
th_editing_data
mentions that InsertSQL, DeleteSQL and UpdateSQL are not correctly
generated for Firebird, but I can't find any bugtracker item for it.

Any Lazarus gurus with clues/hints, or even, if you're feeling very
enthusiastic, edits on
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1
?

Attached is the project I'm playing with. It deviates from the
tutorial a bit: has some extra transaction handling and shows all
records when you enter an empty string in the filter TEdit.

Thanks!

----------------------------------------------------------------------
Lacak2 · 2011-08-30 05:27

Some comments:
1. you must use SQLQuery1.ApplyUpdates to save local cached  updates
to sql server
2. you have wrong SQLQuery1.UpdateSQL.Text, but this does not affect
insert operations

QuoteInsertSQL, DeleteSQL and UpdateSQL are not correctly generated
for FirebirdI do not think so. Can you provide any error message or
some details which point to this problem

----------------------------------------------------------------------
BigChimp · 2011-08-30 07:21

Hi Lacak,

Thanks for the quick and accurate response!
1. SQLQuery1.ApplyUpdates: ok - I've shoved that in with the
transaction commit statements, see below.

2. Ok - I've stripped the Insert/Update SQLs out and let Lazarus
autocreate them.

3. No error message on InsertSQL, DeleteSQL, UpdateSQL.

Adapted it, seems to work now. Thanks!
I'll update the wiki and remove the section on problems with
InsertSQL,DeleteSQL,UpdateSQL...

Some additional questions:
4. The original tutorial had something like
Code: [Select]    SQLQuery1.SQL.Clear;
    SQLQuery1.SQL.Text := 'select * from CUSTOMER';
Seems to me the Clear statement is superfluous and can be removed,
right?

5. I've created this procedure:
Code: [Select]procedure Tform1.Savechanges;
// Saves edits done by user, if any.
begin
  try
    if SQLTransaction1.Active=true then
    // Only if we are within a started transaction
    // otherwise you get "Operation cannot be performed on an inactive
dataset"
    begin
      SQLQuery1.ApplyUpdates; //Pass user-generated changes back to
database...
      SQLTransaction1.Commit; //... and commit them using the
transaction.
      //SQLTransaction1.Active now is false
    end;
  except
  on E: EIBDatabaseError do
    begin
      MessageDlg('Error', 'A database error has occurred. Technical
error message: ' +
        E.Message, mtError, [mbOK], 0);
      Edit1.Text := '';
    end;
  end;
end;
and call that in the button that builds and runs the filtered query as
well as the form onclose event so any user changes are saved.
Would this be the most sensible way to go about it - and do the
comments explain well to the tutorial user what's going on?

6. I think I'm not going to keep the "blank Tedit shows all records"
as it's not essential to the tutorial: users will be figuring that out
anyway (and it's probably better UI design to provide a "clear filter
button" or something).
Any more suggestions on making it simpler/easier to understand/more
professional (yes, I know, conflicting requirements

Attached is the new code.
Thanks!

----------------------------------------------------------------------
BigChimp · 2011-08-30 09:41

I've updated the tutorial:
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial1

----------------------------------------------------------------------
Lacak2 · 2011-08-30 10:48

Quote from: BigChimp on August 30, 2011, 09:21:54 am4. The original
tutorial had something like
Code: [Select]    SQLQuery1.SQL.Clear;
    SQLQuery1.SQL.Text := 'select * from CUSTOMER';
Seems to me the Clear statement is superfluous and can be removed,
right?
Yes Clear can be removed.

Quote5. I've created this procedure:
Code: [Select]procedure Tform1.Savechanges;
// Saves edits done by user, if any.
begin
  try
    if SQLTransaction1.Active=true then
    // Only if we are within a started transaction
    // otherwise you get "Operation cannot be performed on an inactive
dataset"
    begin
      SQLQuery1.ApplyUpdates; //Pass user-generated changes back to
database...
      SQLTransaction1.Commit; //... and commit them using the
transaction.
      //SQLTransaction1.Active now is false
    end;
  except
  on E: EIBDatabaseError do
    begin
      MessageDlg('Error', 'A database error has occurred. Technical
error message: ' +
        E.Message, mtError, [mbOK], 0);
      Edit1.Text := '';
    end;
  end;
end;
and call that in the button that builds and runs the filtered query as
well as the form onclose event so any user changes are saved.
Would this be the most sensible way to go about it - and do the
comments explain well to the tutorial user what's going on?
If you want applyupdates and commit after each "row operation", then I
use in this situation SQLQuery1.AfterPost, AfterDelete events and do
it in it. But it is only my preference.
If you use CommitRetain instead of only Commit, then changes are
commited and transaction remains active.
(it is better in some situations)

----------------------------------------------------------------------
BigChimp · 2011-08-30 11:25

Thanks.

I understand about the .AfterPost/.AfterDelete. I think I'll leave it
as is: although there is a bigger risk for data change conflicts, that
hardly matters for this tutorial. And I suppose it depends on the
application type... But I'll keep it in mind; it could be added to
some documentation on the wiki somewhere.

About CommitRetaining: yes, I understand you can use that. However,
the Firebird people often get a bit... upset if CommitRetaining is
mentioned 
Of course, please feel free to update the wiki (as if you need my
permission) as you know a lot more about this than I do.

Anyway, thanks once again for your help, I think I'm going back to
troubleshooting my dbf export problems...