如何在运行时使用TFDQuery的RecsSkip和RecsMax属性

问题描述

我一直在寻找TFDQuery中的跳过选择。
我找到的属性是.FetchOptions.RecsSkip和.FetchOptions.RecsMax。我使用 Tokyo 10.2.3 和数据库 Firebird 3

I was looking for a skip and take selection in the TFDQuery.The properties I found are .FetchOptions.RecsSkip and .FetchOptions.RecsMax. I use Tokyo 10.2.3 and database Firebird 3

我在运行时进行查询，我想获取在5处开始记录并获得8条下一条记录。

I make the query at runtime and I want to get the start record at 5 and get the 8 next records.

I类似于：

结果不会跳过第5条记录

var
  qryTest: TFDQuery;
begin
 qryTest:= TFDQuery.Create(self);
 qryTest.Connection := self.FDConnection;

 qryTest.sql.Text:= ' select * from salutationdescriptions order by ID';
 qryTest.Disconnect();
 qryTest.FetchOptions.RecsSkip:= 5;
 qryTest.FetchOptions.RecsMax:= 8;
 qryTest.Open();

但是这给出了前8条记录。不会跳过前5条记录。

But this give as result the first 8 records. The 5 first records are not skipped.

好，我也一样，但是现在我在设计时设置了TFQQuery（窗体上的组件）并添加了选择 select * from

Ok, I to the same but now I set TFQQuery at designtime (component on the form) and add the selection 'select * from salutationdescriptions order by ID' in the component.

运行代码：

这跳过了拳头5条记录

  qryItem.Close;
  qryItem.Disconnect();
  qryItem.FetchOptions.RecsSkip:= 5;
  qryItem.FetchOptions.RecsMax:= 8;
  qryItem.Open();

我得到的结果还可以。这将跳过前5条记录。
当我添加qryItem.sql.text时，它不会跳过前5条记录

The result I get is ok. This skip the first 5 records.When I add the qryItem.sql.text then it doesn't skip the first 5 records

这不会跳过第一笔5条记录

  qryItem.Close;
  qryItem.sql.Text:= ' select * from salutationdescriptions order by ID';
  qryItem.Disconnect();
  qryItem.FetchOptions.RecsSkip:= 5;
  qryItem.FetchOptions.RecsMax:= 8;
  qryItem.Open();

不会跳过前5条记录。

我必须在属性中设置一些东西吗？

Must I set something in the properties?

我想在运行时使用RecsSkip和RecsMax。有建议吗？

I want to use the RecsSkip and RecsMax at runtime. Any suggestions?

发现了问题

在SQL中。我必须以选择...
之间没有空格开头，然后选择

in the SQL.Text I must begin with 'Select ...no space between ' and select

推荐答案

您已经发现，问题出在SQL命令的前导空格中。这是因为命令生成器没有考虑前导命令空间。有这样一个代码（经过修改并经过简化，以供我解释）：

As you've already found out, the problem is in a leading space in the SQL command. It is because the command generator does not take leading command spaces into account. There is a code like this (modified and extensively simplified for explanation by me):

function TFDPhysCommandGenerator.GenerateLimitSelect(ASkip, ARows: Integer;
  AOneMore: Boolean; var AOptions: TFDPhysLimitOptions): string;
begin
  if (True) and (CompareText(Copy(FCommandText, 1, 6), 'SELECT') = 0) then
    Result := GetLimitSelect(FCommandText, ASkip, ARows, AOptions)
  else
    Result := FCommandText;
end;

您可能会看到问题所在。仅当从查询命令文本中分配的 FCommandText 字段以 SELECT 词开头时，命令生成器才返回DBMS的本机 LIMIT 命令。这不是您的情况（由于前导空格），因此生成器将按原样返回命令。

There you might see the problem. The command generator returns DBMS' native LIMIT command only when the FCommandText field, which is assigned from the query command text starts by the SELECT word. Which is not your case (due to the leading space), hence the generator returns the command as is.

这是一个FireDAC错误，我已经报告为。由于还有更多这样的检查，我认为最简单的解决方法是直接在其构造函数中修剪SQL命令，例如：

This is a FireDAC bug I've reported as RSP-20403. Since there is more checks like this, I think the simplest fix for this would be trimming SQL command directly in its constructor like:

constructor TFDPhysCommandGenerator.Create(const ACommand: IFDPhysCommand);
begin
  ...
  FCommandText := Trim(ACommand.SQLText);
  ...
end;

所以现在要依靠EMBT来解决这个问题。

So now it's upon EMBT how to fix this.

这篇关于如何在运行时使用TFDQuery的RecsSkip和RecsMax属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！