Delphi TADOQuery.next skipping a record

1.5k views Asked by At

Description of the application/processing

I'm maintaining some old code in Delphi XE. The application runs a query on a database on a remote server, and then creates a report on the PC. We've had a bug report that some data in one of the fields is missing. The field in question contains a huge amount of data (a few thousand characters) collected from the server. I have no control over what data is collected and stored in this field. I discovered that the error was being caused by nulls and other control characters (line feeds etc.) embedded in this data. When the TADOQuery's SaveToFile method was called, the nulls and other control characters were causing any data later on in the field to be ignored (the SaveToFile basically got to the control characters and stopped reading the record).

The solution to the bug was to pull the field in question from the result set, filter out any control characters and save this to a temporary file (I couldn't put it back into the resultset at this point as it was in a read-only state). The data is saved as XML so I also have to make some of the characters XML friendly (as you'll see in the code snippet). Once the SaveToFile method had been called, I'd read the corrected data from the temporary file and write it back into the saved report. It's long-winded but it works.

The problem

I now have a new problem. I notice that on random occasions, a single record would be missing at the end of the file (the field in question would be blank). I've spent ages digging into the issue and it comes down to the following code (which is used to extract the data from the field before the SaveToFile is called). The code is skipping a single line. Not every time - around one in every two or three query runs. I know this for a fact because I added a counter to the loop below while debugging and compared the number of times the loop was executed against the record count, and it came back one less when the error occurred (resulting in a blank field) and equal when all data appeared in the report. No exceptions are caught, so it isn't falling over on something - it's literally skipping a record.

assignfile(f, tempFilename);
rewrite(f);
adoqry.first;
repeat  
    try
        bytes := adoqry.fieldByName(fld).AsBytes;
        tmp := '';
        for i := 0 to length(bytes) - 1 do
            if bytes[i] < 32 then    // strip any control characters (nulls, line feeds etc.) from the string
                 tmp := tmp + ' '
            else
            begin
                 case char(bytes[i]) of
                      '&': tmp := tmp + '&amp;';
                      '''': tmp := tmp + '&apos;';
                      '"': tmp := tmp + '&quot;';
                      '>': tmp := tmp + '&gt;';
                      '<': tmp := tmp + '&lt;';
                 else
                      tmp := tmp + char(bytes[i]);
                 end;
            end;
        // when debugging, inc counter here to prove that the loop has been executed
        writeln(f, UTF8String(tmp));
        setLength(bytes, 0);
    except on e: exception do
        writeln(f, '');
    end;
    adoqry.next;
until adoqry.eof;
closefile(f);

Question

Is there any reason why the above code would skip a record (i.e. only execute the loop n - 1 times, where n is the record count)? Is there something that would cause the "adoquery.next" call to skip a record?

Edit to clarify issues raised in comments

Data is missing from the report. It's always a single record that isn't being processed. I have over 20,000 records in the report and the missing record is somewhere in the middle, but it's hard to narrow it down with there being so much data. As a single record is being skipped, everything after that record shifts up a record (meaning that a lot of the report is then wrong), with the final record containing a blank field.

1

There are 1 answers

2
Jeedee On BEST ANSWER

I seem to have stumbled across a solution to this problem. I'm still testing, but it seems to have fixed things at the moment.

Before I get into that, I tracked down the record that was being skipped. There are a number of records with exactly the same data in them. I deleted the record that was being skipped to see what would happen and the processing skipped a different record (that contained the same data) instead. As before, it was only skipping it intermittently (once in every two or three query runs).

Onto the "fix"...

I thought that I'd try to make a few tweaks to how the TADOQuery was configured to see if that had any effect. For years, it has been running fine with the following option configured:

adoqry.executeOptions := [eoAsyncFetch];

I commented this line out and suddenly the processing works fine. I've run several queries and they all come back with all rows. Nothing is being skipped. I'll continue to test but this seems (at the moment) to have fixed things.