SQLQuery result ordering.
lazarus / 16-Databases

----------------------------------------------------------------------
iwilson · 2012-08-01 17:56

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 · 2012-08-01 18:09

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 · 2012-08-02 13:09

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 · 2012-08-02 13:16

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 · 2012-08-02 13:51

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 · 2012-08-02 13:55

QuoteNo 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 · 2012-08-02 14:15

Quote from: KpjComp on August 02, 2012, 03:55:18 pmQuoteNo 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 · 2012-08-02 14:24

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 · 2012-08-02 18:11

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 · 2012-08-02 18:31

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 · 2012-08-02 20:05

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

----------------------------------------------------------------------
Knipfty · 2012-08-02 20:08

Yes it does.  Thanks for the update.

----------------------------------------------------------------------
Elmug · 2012-08-03 01:27

Quote from: iwilson on August 01, 2012, 07:56:24 pmJust 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.