Reputation: 575
We're migrating almost 2 million lines of code from BDE to SQL Server in Delphi XE5 (soon to be DX).
We've hit a big problem.
We've been using ADO, but have just been stung by Microsoft's failure to implement .Locate on a string that has both single quote signs and pound signs at the same time. Example:
TADOQuery1.Locate('FieldName', '2x4'' 10#', [])
fails with:
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Microsoft's ADO documentation states that this will fail. And we can see it happening in ADODB.pas. For both simple one variable locates and locates that contain more than one variable.
We do not have the option of doing these locates in standard queries with WHERE strings due to their being in tight loops.
Questions: Does FireDAC have this problem? Can someone do us a favor and actually try the above .Locate in FireDac?
What unpleasant "surprises" might we encounter migrating from ADO to Firedac?
Thanks.
Upvotes: 2
Views: 2163
Reputation: 30735
I created a minimalist FireDAC application in XE8 comprising a TFDConnection
, TFDQuery
, and TFDGUIxWaitCursor
, with the TFDquery
connected to a TDataSource
, TDBGrid
and TDBNavigator
. I connected the TFDConnection
to a MS SqlServer 2014 database, and edited a data row to include your test value
2x4' 10#
in a VARCHAR(80) column.
Calling .First
, then .Locate
of the FDQuery
successfully located the row, when I called .Locate
on only the column that contains your test value and when it was part of a two-field call.
So, it's at least worth testing yourself. You mentioned you had XE8 in that similar Locate
query last week.
As for other unpleasant surprises, I can't think of any offhand. All I can remember is that it was a blessed relief to be free of the BDE when we ditched it circa 2002 in favour of Sql Server 2000 via the OLEDB driver + Ado. I was glad we chose the collation we did, Latin1_General_CI_AI, where CI = Case Insensitive and AI = Accent Insensitive.
My main reservation about FireDAC is that although it seems to handle these things rather better than 'native' objects like the TAdoxxx ones, it seems so far abstracted from them that I suspect you might have a hard time getting anything official done about it in the unlucky event that you do trip over some problem with it. And of course, it's in EMBA's hands now which might say something about wringing bug fixes out of them (especially as they now seem to restricting bug-fix updates to those on an update subscription), though the author does seem very active in supporting it on-line.
Btw, I'm not sure what observation you're basing "Microsoft's failure" on. I tested AdoQuery.Locate
with the modification to ADODB.Pas' GetFilterExpr
I posted in my answer to your other q and it works fine, so maybe you're basing it on something else.
As a matter of interest, I decided to see if the ADOInt.Pas Recordset
object could be used to do similar to Locate
, and it can and also works fine with your search value 2x4' 10#
and with the other test patterns I used:
procedure TForm1.TestRecordSetFind;
var
Expr : String;
begin
Expr := 'applicant = ' + QuotedStr(edLocate.Text);
if cbMultiField.Checked then begin
Expr := '(' + Expr + ') and (country = ''EP'')';
end;
Memo1.Lines.Add(Expr);
AdoQuery1.RecordSet.Find(Expr, 0, adSearchForward, adBookmarkFirst);
AdoQuery1.Resync([]);
end;
A couple of obvious limitations of doing it that way instead, of course, are that RecordSet.Find
is a procedure rather than a function returning a boolean, and that it's case-insensitive (though whether that's because my server Collation is, I don't know).
Upvotes: 5