SQLQuery result ordering.

[iwilson]

Just over a year ago I made a program that would load a SQLQuery into a DBGrid and then using code I found here, I would export it to CSV.

Code: [Select]

procedure TForm1.ExportLogs(filename : string);
var
  I, J: Integer;
  SL: TStringList;
begin
  SL := TStringList.Create;
  SQLQuery.First;
  SL.Add('');
  for I := 0 to DBGrid1.Columns.Count -1 do
  begin
       SL[0] := SL[0] + (DBGrid1.Columns[I].Title.Caption) + ',';
  end;
  for I := 1 to  SQLQuery.RecordCount do
  begin
    SL.Add('');
    SQLQuery.RecNo := I;
    for J := 0 to SQLQuery.Fields.Count - 1 do
      SL[SL.Count - 1] := SL[SL.Count - 1] + SQLQuery.Fields[J].AsString + ',';
  end;
  SL.SaveToFile(filename);
  SL.Free;
  ShowMessage('Log results exported to /' + filename);
end;           
I swear this used to work perfectly, it would export just as it looked in the DBGrid.

Now, when I export, a few of the columns don't match the fields, it's not like it is in the DBGrid...They are re-ordered in the SQLQuery.

Problem is, I'm not sure how to go about fixing this or re-ordering a query's results. Any ideas?

[Knipfty]

hi iwilson,

This doesn'r help with columns, but you should not rely on RecordCount.  Some DBs do not load all records into memory at once.  Further, you may want to use the method Next instead of using the actual RecNo field.  Finally, Use the methos EOF to check for the end of the file instead of relying on recordcount.

The only thing I can see is that every line is ending with a comma.  It is possible that this is being misenterpted somewhere though not likely.

Knipfty

[iwilson]

Nah, extra comma isn't doing any bad, it looks out of order even in notepad.

No one has any ideas how to solve this?

For others, does DBGrid arrange your SQLQuery fields order?

[BigChimp]

No idea, sorry, but the usual advice: try to make a minimal compilable test program that exhibits the problem and post it on the bugtracker, describing your issue, operating system, Lazarus and FPC versions and perhaps a link to this thread.

Additionally, you haven't told us your actual Lazarus version, operating system, or database for that matter.
Perhaps a newer Lazarus (and/or FPC) has fixes that deal with this.
Hint: 1.0RC1 was released today, you might give that a try.

[iwilson]

I have witnessed this on 9.28.2 through 9.30.4, on Windows XP, Windows 7 and Ubuntu 10.04.

Moreso, I'm just curious if this has ever worked for others;
Dumping an SQLQuery into a DBGrid and then having the SQLQuery fields actually ordered as you see them in the DBGrid.

I'm nearly positive it was working like this over a year ago, but my memory might have failed me... I don't want to be chasing something if it isn't even technically an issue.

Thanks for the replies guys.

[KpjComp]

No one has any ideas how to solve this?

Looking at the Lazarus source, when you change the column order it doesn't change it's position in the Column array, but just changes it's index.

But I think you can use RealIndex function to map the the Grid Column to the underlining column array.

Could you try this ->

Code: [Select]

..
for I := 0 to DBGrid1.Columns.Count -1 do
  begin
       SL[0] := SL[0] + (DBGrid1.Columns[DBGrid1.Columns.RealIndex(I)].Title.Caption) + ',';
  end;


edit:  I think I got that the wrong way round, it does change the Column order.  But the RealIndex will keep track of the original index so you can map it back to the original field.

edit2:  I believe this will make the order of the export the same as the SQL, if you wanted the order based on the grid, I think you could just use the RealIndex on the QueryFields bit instead.

[iwilson]

No one has any ideas how to solve this?

Looking at the Lazarus source, when you change the column order it doesn't change it's position in the Column array, but just changes it's index.

But I think you can use RealIndex function to map the the Grid Column to the underlining column array.

Could you try this ->

Code: [Select]

..
for I := 0 to DBGrid1.Columns.Count -1 do
  begin
       SL[0] := SL[0] + (DBGrid1.Columns[DBGrid1.Columns.RealIndex(I)].Title.Caption) + ',';
  end;


edit:  I think I got that the wrong way round, it does change the Column order.  But the RealIndex will keep track of the original index so you can map it back to the original field.

edit2:  I believe this will make the order of the export the same as the SQL, if you wanted the order based on the grid, I think you could just use the RealIndex on the QueryFields bit instead.

Unfortunately, nothing was changed using the line you gave :/

Columns are still in the same order as displayed, mismatching SQLQuery order.

I guess I'll start a bug report

[Knipfty]

iWilson,

A question:  Do you need to use the DBGrid?  Why not simply extract the data from the TQuery object directly?

If you need to, you can reorder the columns in the query, or specify the order in your loop using FieldByName.

Knipfty

[iwilson]

I need DBGrid, this is to display logs from a SQL table and I need the ability to export them.

I completely forgot about FieldByName, I dislike SQL and stay away from it when possible... That was something I came here looking for

I have 'manually' set it up to export now, not as clean or short but works like a charm... Thanks all!

[Knipfty]

iwilson,

Maybe I'm missing something, but the DBGrid connects to a datasource which connects to a table or query object.

If all the data in the DBGrid is coming from the table or query object, then you too can go directly to the table or query object and export the data.  You would still be 100% inside Lazarus with no need to touch SQL.  I don't understand what the DBGrid is buying your except to display the same information on the form.

I'm glad you got it working.

[iwilson]

Well, the DBGrid gives the user the option of rearranging columns

[Knipfty]

Yes it does.  Thanks for the update.

[Elmug]

Just over a year ago I made a program that would load a SQLQuery into a DBGrid and then using code I found here, I would export it to CSV.

Code: [Select]

procedure TForm1.ExportLogs(filename : string);
var
  I, J: Integer;
  SL: TStringList;
begin
  SL := TStringList.Create;
  SQLQuery.First;
  SL.Add('');
  for I := 0 to DBGrid1.Columns.Count -1 do
  begin
       SL[0] := SL[0] + (DBGrid1.Columns[I].Title.Caption) + ',';
  end;
  for I := 1 to  SQLQuery.RecordCount do
  begin
    SL.Add('');
    SQLQuery.RecNo := I;
    for J := 0 to SQLQuery.Fields.Count - 1 do
      SL[SL.Count - 1] := SL[SL.Count - 1] + SQLQuery.Fields[J].AsString + ',';
  end;
  SL.SaveToFile(filename);
  SL.Free;
  ShowMessage('Log results exported to /' + filename);
end;           
I swear this used to work perfectly, it would export just as it looked in the DBGrid.

Now, when I export, a few of the columns don't match the fields, it's not like it is in the DBGrid...They are re-ordered in the SQLQuery.

Problem is, I'm not sure how to go about fixing this or re-ordering a query's results. Any ideas?

In this code you don't show the actual value of SQL for SQLQuery1 (the SQL property).

That's what determines the grid column order, and other selection criteria, if any.

So check what it is. If you have not specified columns selection and order by in the SQL property, then check the design order of the fields. They can be reordered there.

All that can be overridden by code of the application, setting with code the SQL requirements.