Introduction

---

Updating of tables and query result sets is accomplished through several methods of the TDBISAMTable and TDBISAMQuery components. The basic update methods include the Append, Insert, Edit, Delete, FieldByName, Post, and Cancel methods. The State property indicates whether the current table or query result set is in Append/Insert mode (dsInsert), Edit mode (dsEdit), or Browse mode (dsBrowse). These methods and properties are used together in order to update a table or query result set. Depending upon your needs, you may require additional methods to update BLOB fields within a given table or query result set, and information on how to use these methods are discussed at the end of this topic.


Adding a New Record

---

The Append and Insert methods allow you to begin the process of adding a record to the dataset. The only difference between these two methods is the Insert method will insert a blank record buffer at the current position in the dataset, and the Append method will add a blank record buffer at the end of the dataset. This record buffer does not exist in the physical datset until the record buffer is posted to the actual dataset using the Post method. If the Cancel method is called, then the record buffer and any updates to it will be discarded. Also, once the record buffer is posted using the Post method it will be positioned in the dataset according to the active index order, not according to where it was positioned due to the Insert or Append methods.

The FieldByName method can be used to reference a specific field for updating and accepts one parameter, the name of the field to reference. This method returns a TField object if the field name exists or an error if the field name does not exists. This TField object can be used to update the data for that field in the record buffer via properties such as
AsString, AsInteger, etc.

The following example shows how to use the Append method to add a record to a table with the following structure:

Field #    Name              DataType     Size
----------------------------------------------
1          CustomerID        ftString     10
2          CustomerName      ftString     30
3          ContactName       ftString     30
4          Phone             ftString     10
5          Fax               ftString     10
6          EMail             ftString     30
7          LastSaleDate      ftDate       0       
8          Notes             ftMemo       0
    
Index Name      Fields In Index      Options
----------------------------------------------
(none)          CustomerID           ixPrimary

begin
   with MyDBISAMDataSet do
      begin
      Append;  { State property will now reflect dsInsert }
      FieldByName('CustomerID').AsString:='100';
      FieldByName('CustomerName').AsString:='The Hardware Store';
      FieldByName('ContactName').AsString:='Bob Smith';
      FieldByName('Phone').AsString:='5551212';
      FieldByName('Fax').AsString:='5551616';
      FieldByName('Email').AsString:='bobs@thehardwarestore.com';
      Post;  { State property will now return to dsBrowse }
      end;
end;

If the record that is being posted violates a min/max or required constraint for the dataset then an EDBISAMEngineError exception will be raised with the appropriate error code. This will also occur if the record being posted will cause a key violation in either the primary index or a secondary index defined as unique. The error codes for a min/max constraint exception are 9730 (min) and 9731 (max) and are defined as DBISAM_MINVALERR and DBISAM_MAXVALERR in the dbisamcn unit (Delphi) or dbisamcn header file (C++). The error code for a required constraint exception is 9732 and is defined as DBISAM_REQDERR in the dbisamcn unit (Delphi) or dbisamcn header file (C++). The error code for a key violation exception is 9729 and is defined as DBISAM_KEYVIOL in the dbisamcn unit (Delphi) or dbisamcn header file (C++). Please see the Exception Handling and Errors and Appendix B - Error Codes and Messages topics for general information on exception handling in DBISAM.

You may use the OnPostError event to trap for any of these error conditions and display a message to the user. You can also use a try..except block to do the same, and the approach is very similar. The following shows how to use an OnPostError event handler to trap for a key violation error:

procedure TMyForm.MyTablePostError(DataSet: TDataSet;
   E: EDatabaseError; var Action: TDataAction);
begin
   Action:=daAbort;
   if (E is EDBISAMEngineError) then
      begin
      if (EDBISAMEngineError(E).ErrorCode=DBISAM_KEYVIOL) then
         ShowMessage('A record with the same key value(s) '+
                     'already exists, please change the '+
                     'record to make the value(s) unique '+
                     'and re-post the record')
      else
         ShowMessage(E.Message);
      end
   else
      ShowMessage(E.Message);
end;

The following shows how to use a try..except block to trap for a key violation error:

begin
   try
      with MyDBISAMDataSet do
         begin
         Append;  { State property will now reflect dsInsert }
         FieldByName('CustomerID').AsString:='100';
         FieldByName('CustomerName').AsString:='The Hardware Store';
         FieldByName('ContactName').AsString:='Bob Smith';
         FieldByName('Phone').AsString:='5551212';
         FieldByName('Fax').AsString:='5551616';
         FieldByName('Email').AsString:='bobs@thehardwarestore.com';
         Post;  { State property will now return to dsBrowse }
         end;
   except
      on E: Exception do
         begin
         if (E is EDBISAMEngineError) then
            begin
            if (EDBISAMEngineError(E).ErrorCode=DBISAM_KEYVIOL) then
               ShowMessage('A record with the same key value(s) '+
                           'already exists, please change the '+
                           'record to make the value(s) unique '+
                           'and re-post the record')
            else
               ShowMessage(E.Message);
            end
         else
            ShowMessage(E.Message);
         end;
   end;
end;

Editing an Existing Record

---

The Edit method allows you to begin the process of editing an existing record in the dataset. DBISAM offers the choice of a pessimistic or optimistic locking protocol, which is configurable via the LockProtocol property for the TDBISAMSession assigned to the current dataset (see the SessionName property for more information on setting the session for a dataset). With the pessimistic locking protocol a record lock is obtained when the Edit method is called. As long as the record is being edited DBISAM will hold a record lock on that record, and will not release this lock until either the Post or Cancel methods is called. With the optimistic locking protocol a record lock is not obtained until the Post method is called, and never obtained if the Cancel method is called. This means that another user or session is capable of editing the record and posting the changes to the record before the Post method is called, thus potentially causing an EDBISAMEngineError exception to be triggered with the error code 8708, which indicates that the record has been changed since the Edit method was called and cannot be overwritten. In such a case you must discard the edited record by calling the Cancel method and begin again with a fresh copy of the record using the Edit method.


The following example shows how to use the Edit method to update a record in a dataset:

begin
   with MyDBISAMDataSet do
      begin
      Edit;  { State property will now reflect dsEdit }
      { Set LastSaleDate field to today's date }
      FieldByName('LastSaleDate').AsDateTime:=Date;
      Post;  { State property will now return to dsBrowse }
      end;
end;

If the record that you are attempting to edit (or post, if using the optimistic locking protocol) is already locked by another user or session, then an EDBISAMEngineError exception will be triggered with the appropriate error code. The error code for a record lock error is 10258 and is defined as DBISAM_RECLOCKFAILED in the dbisamcn unit (Delphi) or dbisamcn header file (C++).

It is also possible that the record that you are attempting to edit (or post) has been changed or deleted by another user or session since it was last cached by DBISAM. If this is the case then a DBISAM exception will be triggered with the error code 8708 which is defined as DBISAM_KEYORRECDELETED in the dbisamcn unit (Delphi) or dbisamcn header file (C++).

You may use the OnEditError (or OnPostError, depending upon the locking protocol) event to trap for these error conditions and display a message to the user. You can also use a try..except block to do the same, and the approach is very similar. The following shows how to use an OnEditError event handler to trap for several errors:

procedure TMyForm.MyTableEditError(DataSet: TDataSet;
   E: EDatabaseError; var Action: TDataAction);
begin
   Action:=daAbort;
   if (E is EDBISAMEngineError) then
      begin
      if (EDBISAMEngineError(E).ErrorCode=DBISAM_RECLOCKFAILED) then
         begin
         if MessageDlg('The record you are trying to edit '+
                       'is currently locked, do you want to '+
                       'try to edit this record again?',
                        mtWarning,[mbYes,mbNo],0)=mrYes then
            Action:=daRetry;
         end
      else if (EDBISAMEngineError(E).ErrorCode=DBISAM_KEYORRECDELETED) then
         begin
         MessageDlg('The record you are trying to edit '+
                    'has been modified since it was last '+
                    'retrieved, the record will now be '+
                    'refreshed',mtWarning,[mbOk],0);
         DataSet.Refresh;
         Action:=daRetry;
         end
      else
         MessageDlg(E.Message,mtError,[mbOK],0);
      end
   else
      MessageDlg(E.Message,mtError,[mbOK],0);
end;

The following shows how to use a try..except block to trap for several errors:

begin
   while True do
      begin
      try
         with MyDBISAMDataSet do
            begin
            Edit;  { State property will now reflect dsEdit }
            { Set LastSaleDate field to today's date }
            FieldByName('LastSaleDate').AsDateTime:=Date;
            Post;  { State property will now return to dsBrowse }
            end;
         Break; { Break out of retry loop }
      except
         on E: Exception do
            begin
            if (E is EDBISAMEngineError) then
               begin
               if (EDBISAMEngineError(E).ErrorCode=
                   DBISAM_RECLOCKFAILED) then
                  begin
                  if MessageDlg('The record you are trying '+
                                'to edit is currently locked, '+
                                'do you want to try to edit '+
                                'this record again?,mtWarning,
                                [mbYes,mbNo],0)=mrYes then
                     Continue;
                  end
               else if (EDBISAMEngineError(E).ErrorCode=
                        DBISAM_KEYORRECDELETED) then
                  begin
                  MessageDlg('The record you are trying '+
                             'to edit has been modified '+
                             'since it was last retrieved, '+
                             'the record will now be '+
                             'refreshed',mtWarning,[mbOk],0);
                  MyTable.Refresh;
                  Continue;
                  end
               else
                  begin
                  MessageDlg(E.Message,mtError,[mbOK],0);
                  Break;
                  end;
                end
            else
               begin
               MessageDlg(E.Message,mtError,[mbOK],0);
               Break;
               end;
            end;
      end;
      end;
end;

Deleting an Existing Record

---

The Delete method allows you to delete an existing record in a dataset. Unlike the Append, Insert, and Edit methods, the Delete method is a one-step process and does not require a call to the Post method to complete its operation. A record lock is obtained when the Delete method is called and is released as soon as the method completes. After the record is deleted the current position in the dataset will be the next closest record based upon the active index order.

The following example shows how to use the Delete method to delete a record in a dataset:

begin
   with MyDBISAMDataSet do
      Delete;
end;

If the record that you are attempting to delete is already locked by another user or session, then an EDBISAMEngineError exception will be triggered with the appropriate error code. The error code for a record lock error is 10258 and is defined as DBISAM_RECLOCKFAILED in the dbisamcn unit (Delphi) or dbisamcn header file (C++).

It is also possible that the record that you are attempting to delete has been changed or deleted by another user since it was last cached by DBISAM. If this is the case then an EDBISAMEngineError exception will be triggered with the error code 8708 which is defined as DBISAM_KEYORRECDELETED in the dbisamcn unit (Delphi) or dbisamcn header file (C++).

You may use the OnDeleteError event to trap for these error conditions and display a message to the user. You can also use a try..except block to do the same, and the approach is very similar. The code for an handling Delete errors is the same as that of an Edit, so please refer to the above code samples for handling Edit errors.

Cancelling an Insert/Append or Edit Operation

---

You may cancel an existing Insert/Append or Edit operation by calling the Cancel method. Doing this will discard any updates to an existing record if you are editing, or will completely discard a new record if you are inserting or appending. The following example shows how to cancel an edit operation on an existing record:

begin
   with MyDBISAMDataSet do
      begin
      Edit;  { State property will now reflect dsEdit }
      { Set LastSaleDate field to today's date }
      FieldByName('LastSaleDate').AsDateTime:=Date;
      Cancel;  { State property will now return to dsBrowse }
      end;
end;

Additional Events

---

There are several additional events that can be used to hook into the updating process for a dataset. They include the BeforeInsert, AfterInsert, OnNewRecord, BeforeEdit, AfterEdit, BeforeDelete, AfterDelete, BeforePost, AfterPost, BeforeCancel, and AfterCancel events. All of these events are fairly self-explanatory, however the OnNewRecord is special in that it can be used to assign values to fields in a newly-inserted or appended record without having the dataset mark the record as modified. If a record has not been modified in any manner, then the dataset will not perform an implicit Post operation when navigating off of the record. Instead, the Cancel method will be called and the record discarded.

Updating BLOB Fields

---

Most of the time you can simply use the general TField AsString and AsVariant properties to update a BLOB field in the same fashion as you would any other field. Both of these properties allow very large strings or binary data to be stored in a BLOB field. However, in certain cases you may want to take advantage of additional methods and functionality that are available through the TBlobField object that descends from TField or the TDBISAMBlobStream object that provides a stream interface to a BLOB field. The most interesting methods of the TBlobField object are the LoadFromFile, LoadFromStream, SaveToFile, and SaveToStream methods. These methods allow you to very easily load and save the data to and from BLOB fields.


The following is an example of using the LoadFromFile method of the TBlobField object to load the contents of a text file into a memo field:

begin
   with MyDBISAMDataSet do
      begin
      Edit;  { State property will now reflect dsEdit }
      { Load a text file from disk }
      TBlobField(FieldByName('Notes')).LoadFromFile('c:\temp\test.txt');
      Post;  { State property will now return to dsBrowse }
      end;
end;

In addition to these very useful methods, you can also directly manipulate a BLOB field like any other stream by using the TDBISAMBlobStream object. The following is an example of using a TDBISAMBlobStream component along with the TDBISAMTable or TDBISAMQuery SaveToStream method for storing DBISAM tables themselves in the BLOB field of another table:

var
   MyBlobStream: TDBISAMBlobStream;
begin
   { First create the BLOB stream - be sure to make sure that
     we put the table into dsEdit or dsInsert mode first since
     we're writing to the BLOB stream }
   MyFirstDBISAMDataSet.Append;
   try
      MyBlobStream:=TDBISAMBlobStream.Create(TBlobField(
          MyFirstDBISAMDataSet.FieldByName('TableStream')),bmWrite);
      try
         { Now save the table to the BLOB stream }
         MySecondDBISAMDataSet.SaveToStream(MyBlobStream);
      finally
         { Be sure to free the BLOB stream *before* the Post }
         MyBlobStream.Free;
      end;
      MyFirstDBISAMDataSet.Post;
   except
      { Cancel on an exception }
      MyFirstDBISAMDataSet.Cancel;
   end;
end;