Back


{*************************************************************************}
{*  PART OF THE CODE FOR TABLE REPLICATION - FOR RECORD UPDATE           *}
{*                                                                       *}
{*  Can be used if, and only if the Source and Destination Table have    *}
{*  the same structure, or same field types for all fields for           *}
{*  replication. After many tests of the Replication Engine on poor      *}
{*  dial-up connections, with different InterBase Database Engines       *}
{*  (BDE, IBX, IBO), we came to a conclusion that for best performance,  *}
{*  low level functions, from the IB API, have to be used, and first of  *}
{*  all: ExecuteImmediate() and ExecImmed2(). We measured even up to 10x *}
{*  better performance with this functions used, instead of standard     *}
{*     Query1.SQL.Add('update ....');                                    *}
{*     Query1.Prepare;                                                   *}
{*     ... fill params                                                   *}
{*     Query1.Execute;                                                   *}
{*  Only IBO gives us access to these functions on the low level. The    *}
{*  use of these functions is not as complicated as it seems at a first  *}
{*  look. You have to learn about the XSQLDA and XSQLVAR structures      *}
{*  first (see API Reference Guide)                                      *}
{*  -------------------------------------------------------------------  *}
{*  okey: String; // Old key values, all fields in one string structure  *}
{*  nkey: String; // New key values, all fields in one string structure  *}
{*  cusel: TIB_Cursor;                                                   *}
{*  dch_dest: TIB_DSQL;                                                  *}
{*                                                                       *}
{*  TDBTables = class(TObject)                                           *}
{*  public                                                               *}
{*    property table_name: String;                                       *}
{*    property target_tbl_name: String;                                  *}
{*    property tbl_key: TStringList; // Table PK Fields                  *}
{*    property tbl_fields: TStringList; // Other Table Fields            *}
{*  end;                                                                 *}
{*                                                                       *}
{*  function SelectSQL(tab: TDBTables; key: String): String;             *}
{*  // Create Select SQL from Source Table                               *}
{*                                                                       *}
{*  function UpdateSQL(tab: TDBTables; okey, nkey: String): String;      *}
{*  // Create Update SQL for Target Table                                *}
{** **********************************************************************}


procedure TDM.UpdateRow(tab: TDBTables; okey, nkey: String);
var i: Integer;
    PIn_DA: PXSQLDA;
    xvar: PXSQLVAR;
begin
  if tab.tbl_fields.Count > 0 then
  begin
    GetMem( PIn_DA, XSQLDA_LENGTH( tab.tbl_fields.Count ));
    try
      cusel.SQL.Clear;
      cusel.SQL.Add( SelectSQL(tab, nkey) );
      cusel.First;
      if cusel.EOF then raise Exception.Create('Error cusel UpdateRow');

      with PIn_DA^ do
      begin
        version := SQLDA_VERSION1;
        sqln := tab.tbl_fields.Count;
        sqld := sqln;
        for i:=0 to sqln-1 do
        begin
          with sqlvar[ i ] do
          begin
            xvar:=cusel.Fields[i].PSQLVAR;
            sqltype  := xvar.sqltype;
            sqllen   := xvar.sqllen;
            sqldata  := xvar.sqldata;
            sqlscale := xvar.sqlscale;
            sqlind   := xvar.sqlind;
          end;
        end;
      end;
      dch_dest.RetrieveDomainNames := false;
      dch_dest.ExecuteImmediate( UpdateSQL(tab, okey, nkey), PIn_DA);
    finally
      FreeMem( PIn_DA );
      cusel.Close;
    end;
  end else begin
    dch_dest.RetrieveDomainNames := false;
    dch_dest.ExecuteImmediate( UpdateSQL(tab, okey, nkey), nil);
  end;
end;


 Back 


info@halkyon.com