Updating of tables, views, and query result sets is accomplished through several methods of the TEDBTable, TEDBQuery, TEDBScript, and TEDBStoredProc components. The basic update methods include the Append, Insert, Edit, Delete, FieldByName, Post, and Cancel methods. The State property indicates whether the current table, view, 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, view, or query result set. Depending upon your needs, you may require additional methods to update BLOB columns within a given table, view, or query result set, and information on how to use these methods are discussed at the end of this topic.


Adding a New Row

---

The Append and Insert methods allow you to begin the process of adding a row to the dataset. The only difference between these two methods is the Insert method will insert a blank row buffer at the current position in the dataset, and the Append method will add a blank row buffer at the end of the dataset. This row buffer does not exist in the physical datset until the row buffer is posted to the actual dataset using the Post method. If the Cancel method is called, then the row buffer and any updates to it will be discarded. Also, once the row 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 column for updating and accepts one parameter, the name of the column to reference. This method returns a TField object if the column name exists or an error if the column name does not exists. This TField object can be used to update the data for that column in the row buffer via properties such as AsString, AsInteger, etc.

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

Column #    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      Columns In Index      Options
----------------------------------------------
Primary_Key     CustomerID           ixPrimary

begin
   with MyEDBDataSet 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 row that is being posted violates a table constraint for the dataset then an EEDBError exception will be raised with the error code 1004 (EDB_ERROR_CONSTRAINT). Please see the Exception Handling and Errors and Appendix A - Error Codes and Messages topics for general information on exception handling in ElevateDB.

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 constraint error:

procedure TMyForm.MyTablePostError(DataSet: TDataSet;
   E: EDatabaseError; var Action: TDataAction);
begin
   Action:=daAbort;
   if (E is EEDBError) then
      begin
      if (EEDBError(E).ErrorCode=EDB_ERROR_CONSTRAINT) then
         ShowMessage('This row violates a table or column constraint ('+
                     E.Message+')')
      else
         ShowMessage(E.Message);
      end
   else
      ShowMessage(E.Message);
end;

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

begin
   try
      with MyEDBDataSet 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 EEDBError) then
            begin
            if (EEDBError(E).ErrorCode=EDB_ERROR_CONSTRAINT) then
               ShowMessage('This row violates a table or column constraint ('+
                           E.Message+')')
            else
               ShowMessage(E.Message);
            end
         else
            ShowMessage(E.Message);
         end;
   end;
end;

Editing an Existing Row

---

The Edit method allows you to begin the process of editing an existing row in the dataset. ElevateDB offers the choice of a pessimistic or optimistic locking protocol, which is configurable via the RecordLockProtocol property for the TEDBSession 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 row lock is obtained when the Edit method is called. As long as the row is being edited ElevateDB will hold a row lock on that row, and will not release this lock until either the Post or Cancel methods is called. With the optimistic locking protocol a row 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 row and posting the changes to the row before the Post method is called, thus potentially causing an EEDBError exception to be raised with the error code 1007 (EDB_ERROR_ROWDELETED), or even error code 1008 (EDB_ERROR_ROWMODIFIED) if row change detection is turned on for the current session via the TEDBSession RecordChangeDetection property. In such cases you must discard the edited row by calling the Cancel method and begin again with a fresh copy of the row using the Edit method.


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

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

If the row that you are attempting to edit (or post, if using the optimistic locking protocol) is already locked by another session, then an EEDBError exception will be raised with the error code 1005 (EDB_ERROR_LOCKROW).

It is also possible that the row that you are attempting to edit (or post) has been deleted by another session since it was last cached by ElevateDB. If this is the case then a ElevateDB exception will be raised with the error code 1007 (EDB_ERROR_ROWDELETED). If row change detection is enabled, then it is also possible that the row that you are attempting to edit (or post) has been changed by another session since it was last cached by ElevateDB. If this is the case then a ElevateDB exception will be raised with the error code 1008 (EDB_ERROR_ROWMODIFIED).

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 EEDBError) then
      begin
      if (EEDBError(E).ErrorCode=EDB_ERROR_LOCKROW) then
         begin
         if MessageDlg('The row you are trying to edit '+
                       'is currently locked, do you want to '+
                       'try to edit this row again?',
                        mtWarning,[mbYes,mbNo],0)=mrYes then
            Action:=daRetry;
         end
      else if (EEDBError(E).ErrorCode=EDB_ERROR_ROWDELETED) then
         begin
         MessageDlg('The row you are trying to edit '+
                    'has been deleted since it was last '+
                    'retrieved',mtError,[mbOk],0);
         DataSet.Refresh;
         end
      else if (EEDBError(E).ErrorCode=EDB_ERROR_ROWMODIFIED) then
         begin
         MessageDlg('The row you are trying to edit '+
                    'has been modified since it was last '+
                    'retrieved, the row 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 MyEDBDataSet do
            begin
            Edit;  { State property will now reflect dsEdit }
            { Set LastSaleDate column 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 EEDBError) then
               begin
               if (EEDBError(E).ErrorCode=EDB_ERROR_LOCKROW) then
                  begin
                  if MessageDlg('The row you are trying '+
                                'to edit is currently locked, '+
                                'do you want to try to edit '+
                                'this row again?,mtWarning,
                                [mbYes,mbNo],0)=mrYes then
                     Continue;
                  end
               else if (EEDBError(E).ErrorCode=EDB_ERROR_ROWDELETED) then
                  begin
                  MessageDlg('The row you are trying '+
                             'to edit has been deleted '+
                             'since it was last retrieved',
                             mtError,[mbOk],0);
                  MyTable.Refresh;
                  Break;
                  end
               else if (EEDBError(E).ErrorCode=EDB_ERROR_ROWMODIFIED) then
                  begin
                  MessageDlg('The row you are trying '+
                             'to edit has been modified '+
                             'since it was last retrieved, '+
                             'the row 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 Row

---

The Delete method allows you to delete an existing row 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 row lock is obtained when the Delete method is called and is released as soon as the method completes. After the row is deleted the current position in the dataset will be the next closest row based upon the active index order.

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

begin
   with MyEDBDataSet do
      Delete;
end;

If the row that you are attempting to delete is already locked by another user or session, then an EEDBError exception will be raised with the error code 1005 (EDB_ERROR_LOCKROW).

It is also possible that the row that you are attempting to delete has been deleted by another session since it was last cached by ElevateDB. If this is the case then a ElevateDB exception will be raised with the error code 1007 (EDB_ERROR_ROWDELETED). If row change detection is enabled, then it is also possible that the row that you are attempting to delete has been changed by another session since it was last cached by ElevateDB. If this is the case then a ElevateDB exception will be raised with the error code 1008 (EDB_ERROR_ROWMODIFIED).

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 row if you are editing, or will completely discard a new row if you are inserting or appending. The following example shows how to cancel an edit operation on an existing row:

begin
   with MyEDBDataSet do
      begin
      Edit;  { State property will now reflect dsEdit }
      { Set LastSaleDate column 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, OnNewRow, BeforeEdit, AfterEdit, BeforeDelete, AfterDelete, BeforePost, AfterPost, BeforeCancel, and AfterCancel events. All of these events are fairly self-explanatory, however the OnNewRow is special in that it can be used to assign values to columns in a newly-inserted or appended row without having the dataset mark the row as modified. If a row has not been modified in any manner, then the dataset will not perform an implicit Post operation when navigating off of the row. Instead, the Cancel method will be called and the row discarded.

Updating BLOB and CLOB Columns

---

Most of the time you can simply use the general TField AsString and AsVariant properties to update a BLOB or CLOB column in the same fashion as you would any other column. Both of these properties allow very large strings or binary data to be stored in a BLOB or CLOB column. 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 TEDBBlobStream object that provides a stream interface to a BLOB or CLOB column. 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 and CLOB columns.


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

begin
   with MyEDBDataSet 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 or CLOB column like any other stream by using the TEDBBlobStream object. The following is an example of using a TEDBBlobStream component along with the TEDBTable or TEDBQuery SaveToStream method for storing ElevateDB tables themselves in the BLOB column of another table:

var
   BlobStream: TEDBBlobStream;
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 }
   FirstEDBDataSet.Append;
   try
      BlobStream:=TEDBBlobStream.Create(TBlobField(
          FirstEDBDataSet.FieldByName('TableStream')),bmWrite);
      try
         { Now save the table to the BLOB stream }
         SecondEDBDataSet.SaveToStream(BlobStream);
      finally
         { Be sure to free the BLOB stream *before* the Post }
         BlobStream.Free;
      end;
      FirstEDBDataSet.Post;
   except
      { Cancel on an exception }
      FirstEDBDataSet.Cancel;
   end;
end;