CODEWITHSUNDEEP
sql-serverdelphidelphi-xe2
levi Clouser
levi Clouser

Reputation: 358

Eof not triggering

I have a function where I get data from a DB, my test data set returns 6500 rows (I extracted the formatted SQL statement from the SQLText Variable and ran it as a test), but then when I run the following code Eof never triggers and I have seen over 100k rows imported.

ADOQuery := TADOQuery.Create(nil);
ADOQuery.ConnectionString := CONNECT_STRING;

// Build SQL Query 
SQLText :=   Format( 'Select  Temp.Serial, Temp.QCSample , Temp.Scrap , Temp.StationID , Temp.Defect , Temp.AddData , Temp2.Serial as Parent_Serial ' +
      'from TAB_ELEMENT as Temp ' + 

      'left join TAB_ELEMENT as Temp2 on  Temp.Parent_Id = Temp2.Element_Id ' + 
      'where Temp.Batch_ID = %d    and Temp.StationID = 0 ',[iSearchID]);

ADOQuery.SQL.Clear;   // Clear query of garbage values
ADOQuery.SQL.Text := SQLText; // Add query text to query module
ADOQuery.Open; 

// Handle Results
iIndexPos := 0; 
tDataImport.BeginUpdate;  

while not ADOQuery.Eof do
begin
    tDataImport.Items[iIndexPos].Serial := ADOQuery.FieldByName('Serial').AsString;
    tDataImport.Items[iIndexPos].QCStatus := ADOQuery.FieldByName('QCSample').AsBoolean;
    tDataImport.Items[iIndexPos].Scrap := ADOQuery.FieldByName('Scrap').AsInteger;
    tDataImport.Items[iIndexPos].StationID := ADOQuery.FieldByName('StationID').AsInteger;
    tDataImport.Items[iIndexPos].Defect := ADOQuery.FieldByName('Defect').AsBoolean;
    tDataImport.Items[iIndexPos].AddData := ADOQuery.FieldByName('AddData').AsString;
    tDataImport.Items[iIndexPos].ParentSerial := ADOQuery.FieldByName('Parent_Serial').AsString;

    inc(iIndexPos);
end;

So in summery running this query with these parameters I expect 6500 rows, when I run this it never ends even after 100k+ rows are processed.

Upvotes: 0

Views: 638

Answers (1)

Remy Lebeau
Remy Lebeau

Reputation: 596377

Open() places the cursor on the first record and sets Eof accordingly. You are not calling Next() to advance the cursor to the next record and update Eof, so you are processing the same record over and over:

ADOQuery.Open; 
while not ADOQuery.Eof do
begin
  //...
  ADOQuery.Next; // <-- add this!
end;

On a side note, you should be using a parameterized query instead of a formatted SQL query string. It is safer, faster, and more efficient on the DB:

ADOQuery := TADOQuery.Create(nil);
ADOQuery.ConnectionString := CONNECT_STRING;

ADOQuery.SQL.Text := 'Select  Temp.Serial, Temp.QCSample , Temp.Scrap , Temp.StationID , Temp.Defect , Temp.AddData , Temp2.Serial as Parent_Serial ' +
  'from TAB_ELEMENT as Temp ' + 
  'left join TAB_ELEMENT as Temp2 on  Temp.Parent_Id = Temp2.Element_Id ' + 
  'where Temp.Batch_ID = :iSearchID and Temp.StationID = 0 ';

with ADOQuery.Parameters.ParamByName('iSearchID') do
begin
  DataType := ftInteger;
  Value := iSearchID;
end;

ADOQuery.Open; 
try
  iIndexPos := 0; 
  tDataImport.BeginUpdate;  
  try
    while not ADOQuery.Eof do
    begin
      tDataImport.Items[iIndexPos].Serial := ADOQuery.FieldByName('Serial').AsString;
      tDataImport.Items[iIndexPos].QCStatus := ADOQuery.FieldByName('QCSample').AsBoolean;
      tDataImport.Items[iIndexPos].Scrap := ADOQuery.FieldByName('Scrap').AsInteger;
      tDataImport.Items[iIndexPos].StationID := ADOQuery.FieldByName('StationID').AsInteger;
      tDataImport.Items[iIndexPos].Defect := ADOQuery.FieldByName('Defect').AsBoolean;
      tDataImport.Items[iIndexPos].AddData := ADOQuery.FieldByName('AddData').AsString;
      tDataImport.Items[iIndexPos].ParentSerial := ADOQuery.FieldByName('Parent_Serial').AsString;
      inc(iIndexPos);
      ADOQuery.Next; 
    finally
      tDataImport.EndUpdate;  
    end;
  end;
finally
  ADOQuery.Close; 
end;

Upvotes: 8

Related Questions