TQuery (SQL) date based data retrieval problem

[ChrisGSchultz]

I'm trying to resolve an application lockup problem in a [tt]TQuery[/tt] data extraction from a DBF (FoxPro CDX indexes, FPT memos - a large DBF written to by a Clipper legacy app and a Delphi 6 BDE based app) data extraction.
The TQuery is in a Delphi 7 app.

The TQuery SQL is:
[tt]
SELECT ExamDate,ExamCode,Scanner,BillCode
FROM "c:\kestral\k_upload.dbf" K_Upload
WHERE (((ExamCode NOT LIKE 'n125' + '%') OR (ExamCode IS NULL)) AND(ExamDate BETWEEN :startdate AND :enddate))
ORDER BY ExamDate
[/tt]

[tt]:startdate[/tt] and [tt]:enddate[/tt] are parameters, which are [tt]encodedate[/tt]d - viz:
[tt]
with Query_KUpload do begin
disablecontrols;
Close;
ParamByName('startdate').Value :=
encodedate(yearof(YearStartdate),7,1);
ParamByName('enddate').value :=
encodedate(yearof(MonthEnddate),
monthof(MonthEndDate),
dayof(MonthEndDate));
Prepared := TRUE;
Open;
enablecontrols;
end; //with
[/tt]

All is fine if I extract from 1.07.2003 to 31.12.2003 (or 31.08.2003, or 30.09.2003, or 30.11.2003, or 31.01.2004), but if the dates are 31.07.2003 or 31.10.2003, the extraction locks up (note: I'm using European date format!). These last two dates seem to cause all the problems!
The problem dates ARE present in the DBF, and the same query works in a similar ADO query on a CSV file (same parameters, just a CSV file from another app)

BTW, I've tried the same query using the Database Desktop, and get the same fault.
If I change the :enddate to 1.08.2003 or 1.11.2003 the query works.

Is there something amiss with LocalSQL, my query, or is it more likely the old DBF is broken at some point?

Chris ;-)

 

[ChrisGSchultz]

Hi folks!

Seems that the problem lies in the CDX index - it had been corrupted, but not fatally!
Packing and reindexing fixes it - almost
The 31.10.2003 date still causes a problem.
Any suggestions?




Chris ;-)