Client/Server UpdateSQL OnUpdateRecord OnCloseQuery

 UpdateSQL
 Query Components

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;