绝对数据库批处理移动

问题描述

我想使用批处理组件在表中归档一些旧记录。我在Ace组件站点上查看了该示例，但不确定如何使用它。该命令是：

I would like to use the batch component to archive some old records within a table. I looked at the example on the Ace components site but I am unsure how to use it. The command is :

DestinationTable.BatchMove(SourceTable,TABSBatchMoveType(bmtAppend));

对于该任务，我打算使用两个datetimepicker。因此，查询将带有参数：

For the task I intended to use two datetimepickers. So a query would go something like with parameters:

SELECT * from MYTABLE where DATE BETWEEN :a1 and :a2
ABSTQuery1.Parameters.ParamByName ('a1').AsDate := DateTimePicker1.Date;
ABSTQuery1.Parameters.ParamByName ('a2').AsDate := DateTimePicker2.Date;
ABSQuery.ExecSql;

如何将查询与batchmove命令合并？我希望所有检索到的记录从源表移至目标表。

How do I incorporate the query with the batchmove command? I want all the retrieved records to move from my source table to destination table.

推荐答案

绝对数据库的 BatchMove 似乎是按照旧的BDE TBatchMove 建模的，它需要两个 TTable 组件； IIRC，它不适用于 TQuery ，但我可能会记错了。 （BDE已弃用十多年了，自Delphi 1起我就没有使用过。）

Absolute Database's BatchMove appears to be modeled after the old BDE TBatchMove, which required two TTable components; IIRC, it didn't work with TQuery, but I could be remembering wrong. (The BDE has been deprecated for more than a decade, and I haven't used it since Delphi 1.)

您不需要不过，BatchMove 。您可以使用单个查询完成所有操作（为简便起见，省略了异常处理）：

You don't need BatchMove, though. You can do it all with your single query (exception handling omitted for brevity):

// Copy rows into destination
ABSTQuery1.SQL.Text := 'INSERT INTO DestTable'#32 +
  '(SELECT * from MYTABLE where DATE BETWEEN :a1 and :a2)';
ABSTQuery1.Parameters.ParamByName ('a1').AsDate := DateTimePicker1.Date;
ABSTQuery1.Parameters.ParamByName ('a2').AsDate := DateTimePicker2.Date;
ABSTQuery1.ExecSql;
ABSTQuery1.Close;

// Remove them from source (you said "move", after all)
ABSTQuery1.SQL.Text := 'DELETE FROM MyTable'#32 +
  `WHERE Date BETWEEN :a1 and :a2';
ABSTQuery1.Parameters.ParamByName ('a1').AsDate := DateTimePicker1.Date;
ABSTQuery1.Parameters.ParamByName ('a2').AsDate := DateTimePicker2.Date;
ABSTQuery1.ExecSql;
ABSTQuery1.Close;

用目标名称替换 DestTable

绝对数据库中的更多信息

More info in the Absolute Database on-line manual

我没有使用Absolute Database，但是如果他们的SQL支持包括脚本（我会离开可以根据您的需求进行研究-上面的docs链接）和多个语句，您可以一次通过：

I haven't used Absolute Database, but if their SQL support includes scripting (I'll leave that research up to you - docs link above) and multiple statements, you can do it in one pass:

// Note addition of `;` at end of each SQL statement
// and change in param names for second statement.
// Some DBs will allow you to just use one pair, and
// set the value for each once. Some require setting
// each twice, and some require unique param names.
// Check the documentation for Absolute DB.
//
ABSTQuery1.SQL.Text := 'INSERT INTO DestTable'#32 +
  '(SELECT * from MYTABLE where DATE BETWEEN :a1 and :a2);'
  'DELETE FROM MyTable WHERE Date BETWEEN :d1 and :d2;';
ABSTQuery1.Parameters.ParamByName ('a1').AsDate := DateTimePicker1.Date;
ABSTQuery1.Parameters.ParamByName ('a2').AsDate := DateTimePicker2.Date;

// New param names for second pass
ABSTQuery1.Parameters.ParamByName ('d1').AsDate := DateTimePicker1.Date;
ABSTQuery1.Parameters.ParamByName ('d2').AsDate := DateTimePicker2.Date;
ABSTQuery1.ExecSQL;
ABSTQuery1.Close;

这篇关于绝对数据库批处理移动的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！