Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: ADO .Locate with # sign and single quote fail.



Permlink Replies: 0
Tom Field

Posts: 43
Registered: 3/6/14
ADO .Locate with # sign and single quote fail.
Click to report abuse...   Click to reply to this thread Reply
  Posted: Sep 25, 2015 2:37 PM
Using SQL Server 2014 and XE5, a TADOQuery.Locate with single or multiple fields (samples below) when called with a string that contains both pound sign (#) and single quote ('). fails with the exception:

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

Single arg:

ADOQuery.Locate('FieldName1', 'one#two''three', []); // This string has a single quote embedded in it.

Multiple args:

var
SearchArray: Variant;
begin
SearchArray := VarArrayCreate([0,1], VarVariant);
SearchArray[0] := 'one#two''three'; // This string has a single quote embedded in it.
SearchArray[1] := 'One';
ADOQuery.Locate('FieldName1;FieldName2', SearchArray, []);
end;

In the latter example, the exception occurs because GetFilterStr (in ADODB.pas) tries to handle the embedded single quote by changing the quote character (that it will delimit the string with) to #, ignoring the fact that the string already contains a #. The result is an invalid filter string that looks in part like this:

FieldName1 = #one#two'three#

We're using XE5. DX Seattle has the identical GetFilterStr in ADODB.pas, so I expect it would fail there. (I didn't try it.)

We're also concerned that this exception is "swallowed" by the ADO code, which means that although the the above .Locate returns false with the exception, the exception is only seen when in the IDE. We got lucky that a programmer happened to be working with sample data that contained this sample test case.

Although I'm not very familiar with it, I have searched QC for this bug and found nothing. Is this a known bug?

The availability of this function is critical to us now that we are migrating from D7 to DX.

Any suggested work-around???

We could replace the above code so that each time it's called it opens a new query with a WHERE statement that we construct ourselves. However, the above code is being called in a tight loop over tens of thousands of records, so we're concerned about performance hit we'd take when opening a query to the server in a tight loop.

ADODB.pas is using the # sign as a delimiter. Is that standard?

We need advice and suggestions of any sort.

Tom
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02