when working with local data, it is very common to use grids and other visual controls, edit the data and send it back to the database. We've already seen that using a DBGrid might cause problems when working with an RDBMS, as moving on the grid might send numerous data request to the server, creating a huge amount of network traffic.

Cached updates, solve the live queries issue, reduce network traffic, define a standard way to solve updates conflicts, and reduce the server load, but they require more memory on the client computer.

UpdateSQL

the role of the UpdateSQL component is to provide a query with the update statements required to make its result set editable. Its key properties are DeleteSQL, InsertSQL and ModifySQL but the most important element is the UpdateObject property of the related Query component.

Here is the textual description of the UpdateSQL component of the example:

object EmpUpdate: TUpdateSQL

        ModifySQL.strings = (  'update employee'   'set'

        ' First_name = :First_name,'

        ' Last_name = :Last_name,'

        ' salary = :salary,'

        ' dept_no = :dept_no,'

        ' job_code = :job_code,'

        ' job_grade = :job_grade,'        

        ' job_country = :job_country'

        'where'

        ' emp_no = :old_emp_no'

)

        InsertSQL.strings = (

        'insert into Employee'

        ' (first_name, last_name, salary,'

        ' dept_no, job_code,'

        ' job_grade, job_country)'

        'values'

        ' (:first_name, :last_name,'

        ' :salary, :dept_no,'

        ' :job_code, :job_grade,'

        ' :job_country)'

)

        DeleteSQL.strings = (

        'delete from Employee'

        'where'

        ' emp_no = :old_emp_no'

)

end

The sample code's below show's the ALTERNATIVE way of updating with Cached Updates and as follows;

To delete the employee records, the program uses a stored procedure, which is already available in the sample database and is connected to the following component:

object spDelEmployee: TStoredProc

        DatabaseName = 'AppDB'

        StoredProcName = 'Delete_Employee'

        ParamData = <

                item

                DataType = ftInteger

                Name = 'EMP_NUM'

                ParamType = ptInput

        end >

end

The OnUpdateRecord event of the Query component uses the stored procedure instead of the default UpdateSQL component for deleting records. Here is the code of the event handler:

procedure TdmData.qryEmployeeUpdateRecord (DataSet: TDataSet;  UpdateKind: TUpdateKind; var UpdateAction: TUpdateAction);

begin

// when deleting the record,

// use the stored procedure

if UpdateKind = ukDelete then

begin

// assign emp_no value

// (1st parameter)

with dmData do

        spDelEmployee.Params[0].Value := qryEmployeeEMP_No.OldValue;

try

        // invoke stored procedure that

        // tries to delete employee

        dmData.spDelEmployee.ExecProc;

        // success

        UpdateAction := uaApplied;                

except

        // process failed

        UpdateAction := uaFail;

end;        

end

else

        try

                // apply updates

                // if Delete process

                // was not started

                dmData.EmpUpdate.Apply(UpdateKind);

                UpdateAction := uaApplied;

        except

                UpdateAction := uaFail;

        end;        

end;

Notice that because we perform the update operation directly, we must indicate in the UpdateAction parameter whether it succeeds or not. If the user closes the form with any updates pending, the OnCloseQuery event of the form displays a warning message, allowing the user to apply the updates or skip them:

procedure TMainForm.FormCloseQuery (Sender: TObject; var CanClose: Boolean);

var Res: Integer;

begin

        with dmData do

        if qryEmployee.UpdatesPending then

        begin        

                Res := MessageDlg(CloseMsg, mtInformation, mbYesNoCancel, 0);

                if Res = mrYes then

                  AppDB.ApplyUpdates([qryEmployee]);                

                  CanClose := Res <> mrCancel;

        end;

end;

At the DBGrid control, with the OnEditButtonClick event a Partial code is now shown below which demonstrate the Locate function.

procedure TMainForm.DBGrid1EditButtonClick (Sender: TObject);

begin

        // check whether this is the department field or not note that the, field name was

        // not used, instead the Label display name was used

        if DBGrid1.selectedfield = dmData.qryEmployeeDEPARTMENT then

           with TfrmDepartments.create(self) do

             try

                dmData.qryDepartment.Locate

                ('Dept_No',

                dmData.qryEmployeeDEPT_NO.value,[ ]);

                if ShowModal = mrOk then

                with dmData do

                begin

                if not (qryEmployee.State in

                 [dsEdit, dsInsert]) then

                        qryEmployee.Edit;

                qryEmployeeDEPT_NO.value := qryDepartment.Fields[0].Value;

                qryEmployeeDEPARTMENT.Value := qryDepartment.Fields[1].Value;

                end;

                finally

                        Free;

                end

        else

        ......

        ...

        ..

Finally, the Apply button simply calls the ApplyUpdates method if there are pending updates and then refreshes the data of the Query:

procedure TMainForm.btnApplyClick

(Sender: TObject);

begin

        with dmData do

        if qryEmployee.UpdatesPending then

        begin

         // apply the changes via

         // database level, so that

         // all other pending updates

         // from other related Tables

         // or Query will be included

         // w/ so

                AppDB.ApplyUpdates([qryEmployee]);

         // refresh the data

                qryEmployee.Close;

                qryEmployee.Open;

                btnApply.Enabled := False;

        end;

end;