We've got various situations where we need to access large lists of data from our database.
The FAQ states that for fast forward-only read-only access I would have to use these options :
CursorKind = ckDefault or ckForwardOnly
Mode = fmOnDemand
RowsetSize = 1000
Unidirectional = True
optionally exclude fiMeta from Items, if you does not need to edit dataset.
However I noticed that in Delphi 7 / MS SQL 2005 some queries were returning no results.
Example of this query :
| Code: |
FItems := TADQuery.Create(Nil);
// TO DO
//FItems.LockType := ltPessimistic;
FItems.Connection := TheDatabaseConnection.Anydac;
// FItems.Transaction:= FReadOnlyTransaction;
FItems.FetchOptions.Unidirectional := True;
FItems.FetchOptions.CursorKind := ckForwardOnly;
FItems.FetchOptions.Items := FItems.FetchOptions.Items - [fiMeta];
FItems.SQL.Add('SELECT OEE_TB.[TbnCode]');
FItems.SQL.Add(' , OEE_TBN.[TbnNaam]');
FItems.SQL.Add(', ISNULL((SELECT MessageText FROM MasterDataTranslations WHERE TextId=(''OEE_TBN_TBNName_'' + CONVERT(NVARCHAR(255), OEE_TBN.[TbnCode])) AND Id = ' + IntToStr(Usr.UserLanguage) + '), OEE_TBN.[TbnNaam]) AS TbnNaamTranslated ');
FItems.SQL.Add('FROM OEE_TB WITH(NOLOCK)');
FItems.SQL.Add('INNER JOIN OEE_TBN WITH(NOLOCK) ON OEE_TBN.[TbnCode] = OEE_TB.[TbnCode]');
FItems.SQL.Add('WHERE (OEE_TB.DevCode = :pDevCode)');
FItems.SQL.Add('ORDER BY OEE_TB.[Volgorde], OEE_TBN.[TbnNaam], OEE_TBN.[Volgorde]');
FItems.ParamByName('pDevCode').Value := FCycleInfo.DevCode //
FItems.Prepared := True;
FItems.Open;
FItems.FetchAll;
// ... checking FItems.RecordCount reveals 0 records
// executing this exact query in Management Studio does result in data !
|
Is there a problem with this example or is this a known bug in this version of Anydac ?
