ADO中的＃标志定位（Delphi XE5） [英] # signs in ADO locates (Delphi XE5)

问题描述

在使用TADOQuery.Locate的情况下，该TADOQuery.Locate使用字段列表和值的VarArray，如果其中一个值包含＃号，则会出现此异常：

With an TADOQuery.Locate that uses a list of fields and a VarArray of values, if one of the values contains a # sign, we get this exception:

'Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.'

我已经将其追溯到ADODB，它本身似乎使用＃号作为分隔符。

I've traced this down to ADODB which itself seems to be using # signs as delimiters.

有没有办法逃脱＃号，以便查询不会失败？

Is there a way to escape #-signs so that the query doesn't fail?

*编辑1 *

我错了。导致此失败的原因是一个字符串，该字符串带有井号和单引号。下面显示的代码会失败，并出现上面提到的错误消息。

I was wrong. What causes this failure is a string that has a pound sign and a single quote. The code shown below fails with error message noted above.

真正让我们担心的是，当它无法在IDE外部作为.exe运行时， ，没有运行时例外。我们只有在IDE中时才会看到异常。如果我们的程序员不是偶然使用触发该数据的数据，我们永远不会知道.Locate是由于运行时错误而返回FALSE的，不是因为找不到匹配的记录。

What really worries us is that when it fails running as an .exe outside the IDE, there's no runtime exception. We only see the exception when we're in the IDE. If our programmers hadn't happened to be using data that triggers this we never would have known that the .Locate returned FALSE because of a runtime error, not because a matching record was not found.

代码：

var 
  SearchArray: Variant;
begin
  SearchArray := VarArrayCreate([0,1], VarVariant);
  SearchArray[0] := 'T#more''wo';
  SearchArray[1] := 'One';

  ADOQuery.Locate('FieldName1;FieldName2', SearchArray, []);

推荐答案

请参阅更新下面;我找到了至少值得测试的解决方法。

Please see Updates below; I've found a work-around that's at least worth testing.

即使使用Sql Server表，＃

Even with Sql Server tables, the # shouldn't need to be escaped.

以下代码在D7中可以正常工作。.XE8

The following code works correctly in D7..XE8

procedure TForm1.Button1Click(Sender: TObject);
begin
  AdoQuery1.Locate('country;class', VarArrayOf(['GB', Edit1.Text]), []);
end;

当 Edit1.Text 包含'D＃ E'，所以我认为您的问题必须出在其他地方。重新启动计算机后，尝试仅使用该代码的极简项目。

when Edit1.Text contains 'D#E', so I think your problem must lie elsewhere. Try a minimalist project with just that code, after rebooting your machine.

更新：如注释中所述， .Locate ，其中传递给 GetFilterStr （在ADODB.Pas中）的表达式
包含＃，后接单引号。为了尝试和
一起解决这个问题，我将 GetFilterStr 移植到了我的代码中，并让
尝试使用它来构造AdoQuery上的一个记录集过滤器，因为我注意到
这就是 .Locate 在语句中所做的

Update: As noted in a comment, there is a problem with .Locate where the expression passed to GetFilterStr (in ADODB.Pas) contains a # followed by a single quote. To try and work out a work-around for this, I've transplanted GetFilterStr into my code and have been experimenting with using it to construct a recordset filter on my AdoQuery, as I noticed that this is what .Locate does in the statement

FLookupCursor.Filter := LocateFilter;

我为此使用的代码，包括我的 GetFilterStr ，在下面。

The code I'm using for this, including my "corrected" version of GetFilterStr, is below.

我还没弄清楚的是如何避免在

What I haven't managed to figure out yet is how to avoid getting an exception on

    AdoQuery1.Recordset.Filter := S;

当过滤器表达式不产生任何记录时。

when the filter expression yields no records.

（顺便说一句，为方便起见，我在D7中这样做，但是使用XE8的 GetFilterStr ，这就是为什么我不得不注释掉对<$ c的引用的原因$ c> ftFixedWideChar ）

(Btw, for convenience, I'm doing this in D7, but using XE8's GetFilterStr, which is why I've had to comment out the reference to ftFixedWideChar)

function GetFilterStr(Field: TField; Value: Variant; Partial: Boolean = False): WideString;
// From XE8 Data.Win.ADODB
var
  Operator,
  FieldName,
  QuoteCh: WideString;
begin
  QuoteCh := '';
  Operator := '=';
  FieldName := Field.FieldName;
  if Pos(' ', FieldName) > 0 then
    FieldName := WideFormat('[%s]', [FieldName]);
  if VarIsNull(Value) or VarIsClear(Value) then
    Value := 'Null'
  else
    case Field.DataType of
      ftDate, ftTime, ftDateTime:
        QuoteCh := '#';
      ftString, ftFixedChar, ftWideString://, ftFixedWideChar:
        begin
          if Partial and (Value <> '') then
          begin
            Value := Value + '*';
            Operator := ' like ';     { Do not localize }
          end;
{.$define UseOriginal}
{$ifdef UseOriginal}
          if Pos('''', Value) > 0 then
            QuoteCh := '#' else
            QuoteCh := '''';
{$else}
          QuoteCh := '''';
          if Pos('''', Value) > 0 then begin
            QuoteCh := '';
            Value := QuotedStr(Value);
          end;
{$endif}
        end;
    end;
  Result := WideFormat('(%s%s%s%s%2:s)', [FieldName, Operator, QuoteCh, VarToWideStr(Value)]);
end;

procedure TForm1.CreateFilterExpr;
var
  S : String;
begin
  // clear any existing filter
  AdoQuery1.Recordset.Filter := adFilterNone;
  AdoQuery1.Refresh;

  if edFilter.Text = '' then Exit;

  S := GetFilterStr(AdoQuery1.FieldByName('Applicant'), edFilter.Text, cbPartialKey.Checked);
  //  Add the filter expr to Memo1 so we can inspect it
  Memo1.Lines.Add(S);
  try
    AdoQuery1.Recordset.Filter := S;
    AdoQuery1.Refresh;
  except
  end;
end;

procedure TForm1.FilterClick(Sender: TObject);
begin
  CreateFilterExpr;
end;

更新2：尝试以下操作：

• 将Data.Win.ADODB.Pas复制到您的项目目录

• Copy Data.Win.ADODB.Pas to your project directory

在其中，请用上述版本替换GetFilterExpr，确保未定义UseOriginal
，并在Case语句中恢复了ftFixedWideChar。

In it, replace GetFilterExpr by the version above, making sure that UseOriginal isn't DEFINEd, and that ftFixedWideChar is reinstated in the Case statement.

构建并运行您的项目

在XE8中无论如何，我的测试床现在都可以正确地定位到以<$结尾的字段c $ c>'或＃'
（如果 loPartialKey 已指定。（我无法在XE4 / 5
中进行测试，因为我的XE4现在说自从上周我升级到Win10以来，它是未经许可的，谢谢EMBA！）

In XE8 at any rate, my testbed now correctly Locate()s a field ending with ' or #' (or containing either of them if loPartialKey is specified. (I can't test in XE4/5 because my XE4 now says it's unlicenced since I upgraded to Win10 last week, thanks EMBA!)

我犹豫是否称其为解决方案，甚至还可以解决，但至少值得测试
我不确定是否要使用<$ c $的原始版本c> GetFilterExpr 错误，因为我不确定
的用例是什么包含引号的值旨在处理。

I hestitate to call this a solution or even a work-around as yet, but it is at least worth testing. I'm not sure whether I'd call the original version of GetFilterExpr bugged, because I'm not sure what use-case its treatment of values containing quotes was intended to handle.

这篇关于ADO中的＃标志定位（Delphi XE5）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！