[Solved] [SQLite] Print a Query into a Tmemo (or export to Libroffice)
lazarus / 16-Databases

----------------------------------------------------------------------
jeanchristophe · 2014-09-01 06:07

Hello

I have a query:
Code: [Select]procedure TForm1.Button1Click(Sender: TObject);
var temp: string;
begin
temp:= 'SELECT afatablefrim.Land, afatablefrim.Post,
afatablefrim.Nummer, ';
temp:= temp + 'afatablefrim.Type, afatablefrim.Postfrisk,
objekt.Samling, objekt.Kartotek, objekt.AlbumNavn, ';
temp:= temp + 'objekt.AlbumNummer, objekt.Side, objekt.Linie,
objekt.Placering, ';
temp:= temp + 'objekt.Antal FROM afatablefrim INNER JOIN objekt ON ';
temp:= temp + 'afatablefrim.Nogle =  objekt.AFANogle ';
temp:= temp + 'GROUP BY afatablefrim.Land, afatablefrim.Post, ';
temp:= temp + 'afatablefrim.Nummer, afatablefrim.Type,
afatablefrim.Postfrisk;';
If SQLQuery1.Active = true then SQLQuery1.Active:= false;
SQLQuery1.SQL.Clear;
SQLQuery1.SQL.Add(temp);
SQLQuery1.ExecSQL;
If SQLQuery1.Active = false then SQLQuery1.Active:= true;
temp:= '';
While Not(SQLQuery1.Eof) do
      begin
      temp:= temp + SQLQuery1.Fields[0].asstring;
      SQLQuery1.Next;
      end;
Showmessage (temp);  
Which is obviously working.
I would like to print it on a TMemo or something else, that I can
export to Libroffice afterwards.
I would like it to be printed (almost) like this:
Code: [Select]Land
  Post
    Nummer or Nummer Type
      Postfrisk
        Samling   Kartotek   AlbumNavn   AlbumNummer   Side   Linie 
 Placering
The 4 firste lines are from the master table.
The last line is coming from the slave.

Can you please tell me, how I can solve this with some links or
explanations
In the meantime, I keep digging! 

Best Regards
Jean-Christophe

----------------------------------------------------------------------
mangakissa · 2014-09-01 08:02

use function format() and memo1.lines.add().
But better use TStrings first and after all your data is found copy
TStrings to your memo.

----------------------------------------------------------------------
jeanchristophe · 2014-09-01 08:10

Thank for your answer mangakissa

My first problem is that all the rows are populated, even if I used
"GROUP BY"
When I access the first: SQLQuery1.Fields[0].asstring;
I am expecting that the second one, to be empty, unless there is a new
value, but not. The field (and the others) are always populated, even
if there is no change!
I try to not add the "GROUP BY" fields in my "SELECT", but they are
not showned at all then!

Best Regards
Jean-Christophe

----------------------------------------------------------------------
LacaK · 2014-09-01 12:00

Quote from: jeanchristophe on September 01, 2014, 10:10:36 amMy first
problem is that all the rows are populated, even if I used "GROUP BY"
It is expected behavior

Quote from: jeanchristophe on September 01, 2014, 10:10:36 amI am
expecting that the second one, to be empty, unless there is a new
value, but not.
It is wrong expectation 

You must use local variable store in them
group1:=SQLQuery1.Fields[0].asstring and in loop check
  if group1 <> SQLQuery1.Fields[0].asstring then begin
    group1:=SQLQuery1.Fields[0].asstring;
    // print group header: group1
  end;

----------------------------------------------------------------------
jeanchristophe · 2014-09-01 16:18

OK, thank you LacaK!
I try that and get back.

Best Regards
Jean-Christophe