ex1u.pas

TxQuery is an SQL engine implemented in a TDataSet descendant component, that can parse SQL syntax,

  ViewerHandle: HWND;
begin
  if not savedialog2.execute then exit;
  with THTMLExport.Create(nil) do begin
    try
      // add your header here
      Header.Add('First test exporting a TxQuery dataset');
      Header.Add('Date : '+FormatDateTime('dd/mmm/yyyy',Now));
      // add your footer here
      Footer.Add('Author: Alfonso Moreno');
      Footer.Add('<A HREF="http://www.sigmap.com/txquery.htm">http://www.sigmap.com/txquery.htm</A>');
      Footer.Add('mailto: <A HREF="mailto:[e-mail]">[e-mail]</A>');
      Title.Add('Test exporting a TxQuery dataset');
      DataSet:= XQuery1;
      SaveToFile(SaveDialog2.FileName);
    finally
      free;
    end;
  end;
  ViewerHandle := ShellExecute(0, 'open', PChar(SaveDialog2.FileName), nil, nil, SW_SHOWNORMAL);
  SetForegroundWindow(ViewerHandle);
end;

procedure TfrmTest.XQuery1AlterTable(Sender: TObject;
  CreateTable: TCreateTableItem);
Var
  S, BlobType: String;
  I: Integer;
Begin
  S := 'SQL statement issued:' + CRLF + XQuery1.Sql.Text + CRLF;
  ShowMessage(S);

  S := 'Analisis of ALTER TABLE statement:' + CRLF +
      Format('ALTER TABLE requested on table "%s"', [CreateTable.TableName]) + CRLF +
         Format('Number of fields to alter/drop : %d', [CreateTable.FieldCount]) + CRLF;

  For I := 0 To CreateTable.FieldCount - 1 Do Begin
    if CreateTable.Fields[I].MustDrop then
       S := S + Format('DROP FIELD "%s" ', [CreateTable.Fields[I].FieldName]) +CRLF
    else
       S := S + Format('ADD FIELD "%s" ', [CreateTable.Fields[I].FieldName]) +CRLF;
    Case CreateTable.Fields[I].FieldType Of // list of possible types accepted in TxQuery parser
      RW_CHAR: S := S + Format('type CHAR of Length %d', [CreateTable.Fields[I].Size]) + CRLF; // use Size property here
      RW_INTEGER: S := S + 'type INTEGER' + CRLF;
      RW_SMALLINT: S := S + 'type SMALLINT' + CRLF;
      RW_BOOLEAN: S := S + 'type BOOLEAN' + CRLF;
      RW_DATE: S := S + 'type DATE' + CRLF;
      RW_TIME: S := S + 'type TIME' + CRLF;
      RW_DATETIME: S := S + 'type DATETIME' + CRLF;
      RW_MONEY: S := S + 'type MONEY' + CRLF;
      RW_FLOAT: S := S + Format('type FLOAT Scale %d Precision %d', [CreateTable.Fields[I].Scale, CreateTable.Fields[I].Precision]) + CRLF; // use Scale and Precision properties here
      RW_AUTOINC: S := S + 'type AUTOINC' + CRLF;
      RW_BLOB: Begin // use BlobType property here
          Case CreateTable.Fields[I].BlobType Of
            1: BlobType := 'Memo';
            2: BlobType := 'Binary';
            3: BlobType := 'Formatted Memo';
            4: BlobType := 'OLE';
            5: BlobType := 'Graphic/Binary';
          End;
          S := S + Format('is a BLOB of type %s', [BlobType]) + CRLF;
        End;
    End;
  End;

  ShowMessage(S);

  // the implementation in specific database is cumbersome :-)

end;

procedure TfrmTest.XQuery1ResolveDataset(Sender: TObject;
  const Filename: String; var ATableName: String; var Dataset: TDataSet);
begin
   dataset := TTable.Create(nil);
   with TTable(dataset) do
   begin
      DatabaseName := ExtractFilePath(Filename);
      TableName := ExtractFileName(Filename);
      // this will be the table name in the result set (return value)
      if Length(ATableName)=0 then
         ATableName := ChangeFileExt(ExtractFileName(Filename),'');
      Open;
   end;
end;

procedure TfrmTest.TreeView1Click(Sender: TObject);
begin
   SelectionChange;
end;

procedure TfrmTest.SelectionChange;
var
  Selected: TTreeNode;
begin
  Selected := TreeView1.Selected;
  if (Selected=nil) or (Longint(Selected.Data)=0) then begin
     RichEdit1.Lines.Clear;
     exit;
  end;
  RichEdit1.Lines.Clear;
  ButtonRunSQL.Caption := 'Run SQL';
  SyntaxHighlighter1.Editor := Nil;
  //Table1.First;
  Case Longint(Selected.Data)-1 Of
    00: Begin //BETWEEN
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('SELECT customer.* FROM customer '
                          + 'WHERE LastInvoiceDate '
                          + 'BETWEEN #01/01/1990# '
                          + 'AND #12/31/1994# ORDER BY '
                          + 'Customer.LastInvoiceDate');
        RichEdit1.Lines.EndUpdate;
      End;
    01: Begin //DISTINCT Aggregate
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('Select Count(DISTINCT Country) '
                          + 'FROM Customer;');
        RichEdit1.Lines.EndUpdate;
      End;
    02: Begin //DISTINCT Record
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('Select DISTINCT Country '
                          + 'FROM Customer ORDER BY Country;');
        RichEdit1.Lines.EndUpdate;
      End;
    03: Begin //GROUP BY and Aggregates: Sample 1
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('SELECT c.CustNo, CAST(Sum(AmountPaid) '
                          + 'AS MONEY) As SumAmount, '
                          + 'CAST(Avg(AmountPaid) AS MONEY), '
                          + 'CAST(Min(AmountPaid) AS MONEY), '
                          + 'Count(*) FROM Customer c, Orders o '
                          + 'WHERE (c.CustNo = o.CustNo) '
                          + 'GROUP BY c.CustNo HAVING Sum(AmountPaid) > 80000;');
        RichEdit1.Lines.EndUpdate;
      End;
    04: Begin //GROUP BY and Aggregates: Sample 2
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('SELECT CUSTNO, SUM(AMOUNTPAID) / SUM(ITEMSTOTAL) '
                          + 'As Rate '
                          + 'FROM ORDERS GROUP BY CUSTNO;');
        RichEdit1.Lines.EndUpdate;
      End;
    05: Begin //GROUP BY and Aggregates: Sample 3
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('SELECT CAST(SUM(ItemsTotal) / COUNT(CustNo) AS MONEY) '
                          + 'FROM ORDERS;');
        RichEdit1.Lines.EndUpdate;
      End;
    06: Begin //IN
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('SELECT * FROM customer WHERE City IN ('
                          + '"Bogota", "Sarasota", "Freeport", "Tampa", '
                          + '"Somerset", "Honolulu");');
        RichEdit1.Lines.EndUpdate;
      End;
    07: Begin //JOIN
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('SELECT * FROM Customer c, Orders o, Items i, ');
        RichEdit1.Lines.Add('Parts p WHERE (c.CustNo = o.CustNo) And ');
        RichEdit1.Lines.Add('(o.OrderNo = i.OrderNo) And (i.PartNo = p.PartNo) ');
        RichEdit1.Lines.Add('And c.CustNo > 1300 AND c.CustNo < 2000;');
        RichEdit1.Lines.EndUpdate;
      End;
    08: Begin //JOIN In SELECT
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('SELECT * FROM Customer INNER JOIN Orders ON ');
        RichEdit1.Lines.Add('(Customer.CustNo = Orders.CustNo)');
        RichEdit1.Lines.Add(' INNER JOIN Items ON (Orders.OrderNo = Items.OrderNo)');
        RichEdit1.Lines.Add(' INNER JOIN Parts ON (Items.PartNo = Parts.PartNo);');
        RichEdit1.Lines.EndUpdate;
      End;
    09: Begin //JOIN In WHERE
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('SELECT * FROM Customer c, Orders o, Items i, ');
        RichEdit1.Lines.Add('Parts p ');
        RichEdit1.Lines.Add('WHERE c.CustNo = o.CustNo And o.OrderNo = i.OrderNo ');
        RichEdit1.Lines.Add('And i.PartNo = p.PartNo;');
        RichEdit1.Lines.EndUpdate;
      End;
    10: Begin //JOIN With Multiple Fields
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('/* This is only to illustrate multiple fields joining */ ');
        RichEdit1.Lines.Add('SELECT * FROM Customer c INNER JOIN Orders o ON ');
        RichEdit1.Lines.Add('c.CustNo = o.CustNo ');
        RichEdit1.Lines.Add('INNER JOIN Items i ');
        RichEdit1.Lines.Add('ON o.OrderNo = i.OrderNo;');
        RichEdit1.Lines.EndUpdate;
      End;
    11: Begin //JOIN With Table Alias
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('SELECT * FROM Customer c ');
        RichEdit1.Lines.Add('INNER JOIN Orders o ON (c.CustNo = o.CustNo)');
        RichEdit1.Lines.Add(' INNER JOIN Items i ON (o.OrderNo = i.OrderNo)');
        RichEdit1.Lines.Add(' INNER JOIN Parts p ON (i.PartNo = p.PartNo);');
        RichEdit1.Lines.EndUpdate;
      End;
    12: Begin //LIKE: Sample 1
        RichEdit1.Text := 'SELECT * FROM Customer WHERE Company LIKE "%Under%";';
      End;
    13: Begin //LIKE: Sample 2
        RichEdit1.Text := 'SELECT * FROM Customer WHERE Company LIKE "%C_ub";';
      End;
    14: Begin //LIKE: Sample 3
        RichEdit1.Text := 'SELECT * FROM Customer WHERE company LIKE ''A%Under%'';';
      End;
    15: Begin //UNION
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('/* Syntax is: ');
        RichEdit1.Lines.Add('select_statement_1 UNION select_statement_2 ');
        RichEdit1.Lines.Add('You can also combine different tables ');
        RichEdit1.Lines.Add('but they must have compatible column types */');
        RichEdit1.Lines.Add('SELECT * FROM customer WHERE custno ');
        RichEdit1.Lines.Add('BETWEEN 1000 AND 2500; ');
        RichEdit1.Lines.Add('UNION SELECT * FROM customer WHERE custno ');
        RichEdit1.Lines.Add('BETWEEN 2000 AND 3000;');
        RichEdit1.Lines.EndUpdate;
      End;
    16: Begin //ORDER BY: Sample 1
        RichEdit1.Text := 'SELECT CustNo, Company, Addr1, Addr2 FROM customer ORDER BY custno DESC;';
      End;
    17: Begin //ORDER BY: Sample 2
        RichEdit1.Text := 'SELECT CustNo, Company, Addr1, Addr2 FROM customer ORDER BY 2 DESC;';
      End;
    18: Begin //ORDER BY: Sample 3
        RichEdit1.Text := 'SELECT CustNo, Company, City FROM Customer ORDER BY City, 2 DESC;';
      End;
    19: Begin //SELECT
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('/* TXQuery dataset demo');
        RichEdit1.Lines.Add('Version 1.80 */');
        RichEdit1.Lines.Add('SELECT * FROM Customer;');
        RichEdit1.Lines.EndUpdate;
      End;
    20: Begin //Some Functions
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('SELECT LOWER(TRIM(TRAILING "a" FROM company)) As TrimmedAndLowered, ');
        RichEdit1.Lines.Add(' EXTRACT(YEAR FROM LastInvoiceDate) As TheYear, ');
        RichEdit1.Lines.Add(' EXTRACT(MONTH FROM LastInvoiceDate) As TheMonth, ');
        RichEdit1.Lines.Add(' CAST(MINOF(LastInvoiceDate, NOW) AS DATETIME) As MinDate, ');
        RichEdit1.Lines.Add(' CAST(FORMATDATETIME("dd/mm/yy",MAXOF(LastInvoiceDate, NOW)) AS CHAR(20)) ');
        RichEdit1.Lines.Add(' As MaxDate, ');
        RichEdit1.Lines.Add(' SUBSTRING(country FROM 2 FOR 6) As SubCountry, ');
        //RichEdit1.Lines.Add(' /* same as */');
        //RichEdit1.Lines.Add(' COPY(country, 2, 6) As UsingCopy,');
        RichEdit1.Lines.Add(' addr2,');
        RichEdit1.Lines.Add(' LENGTH(addr2) > 0 As LenAddr2 ');
        RichEdit1.Lines.Add('FROM customer ');
        RichEdit1.Lines.Add('ORDER BY 2 DESC;');
        RichEdit1.Lines.EndUpdate;
      End;
    21: Begin //Functions Solved In Events
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('SELECT itemstotal, amountpaid, freight, ');
        RichEdit1.Lines.Add(' /* AVGOF() is a function solved in events');
        RichEdit1.Lines.Add(' OnUDFCheck and OnUDFSolve */');
        RichEdit1.Lines.Add(' AVGOF(itemstotal, amountpaid, freight) As Average ');
        RichEdit1.Lines.Add('FROM orders; ');
        RichEdit1.Lines.EndUpdate;
      End;
    22: Begin //Subqueries: Sample 1
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('SELECT * ');
        RichEdit1.Lines.Add('FROM Customer ');
        RichEdit1.Lines.Add('WHERE CustNo > (SELECT AVG(CustNo) FROM Customer);');
        RichEdit1.Lines.EndUpdate;
      End;
    23: Begin //Subqueries: Sample 2
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('SELECT * FROM Customer WHERE custno >= ');
        RichEdit1.Lines.Add('ALL (SELECT CustNo FROM customer WHERE ');
        RichEdit1.Lines.Add('/* This is a comment inside SQL statement*/ City ');
        RichEdit1.Lines.Add('IN ("Freeport", "Christiansted", "Kailua-Kona", ');
        RichEdit1.Lines.Add('"Giribaldi", "Kitchener", "Negril"));');
        RichEdit1.Lines.EndUpdate;
      End;
    24: Begin //Subqueries: Sample 3
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('SELECT (SELECT SUM(amountpaid) FROM Orders WHERE ');
        RichEdit1.Lines.Add('OrderNo Between 1100 and 1200) / ');
        RichEdit1.Lines.Add('(SELECT Count(*) FROM orders) As Average_From_1100_To_1200 ');
        RichEdit1.Lines.Add('FROM Orders GROUP BY 1; ');
        RichEdit1.Lines.EndUpdate;
      End;
    25: Begin //TRANSFORM...PIVOT: Sample 1
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('/* This is a TRANSFORM...PIVOT demo ala Microsoft Access */');
        RichEdit1.Lines.Add('TRANSFORM CAST(SUM(AmountPaid) AS MONEY) SELECT CUSTNO FROM ');
        RichEdit1.Lines.Add('ORDERS GROUP BY CUSTNO ');
        RichEdit1.Lines.Add('PIVOT FormatDateTime("yyyy", SALEDATE);');
        RichEdit1.Lines.EndUpdate;
      End;
    26: Begin //TRANSFORM...PIVOT: Sample 2
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('/* Multiple aggregate in the result set */ ');
        RichEdit1.Lines.Add('TRANSFORM SUM(AmountPaid), COUNT(*), AVG(AMOUNTPAID) SELECT CUSTNO ');
        RichEdit1.Lines.Add('FROM ORDERS GROUP BY CUSTNO ');
        RichEdit1.Lines.Add('PIVOT FormatDateTime("yyyy", SALEDATE) IN ');
        RichEdit1.Lines.Add('(SELECT DISTINCT EXTRACT(YEAR FROM LASTINVOICEDATE) FROM');
        RichEdit1.Lines.Add('CUSTOMER ORDER BY 1);');
        RichEdit1.Lines.EndUpdate;
      End;
    27: Begin //TRANSFORM...PIVOT: Sample 3
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('TRANSFORM CAST(SUM(AmountPaid) AS MONEY) ');
        RichEdit1.Lines.Add('SELECT CUSTNO, ROUNDDEC(SUM(AmountPaid),2) FROM ORDERS ');
        RichEdit1.Lines.Add('GROUP BY CUSTNO ');
        RichEdit1.Lines.Add('PIVOT FormatDateTime("mmm", SALEDATE) IN ');
        RichEdit1.Lines.Add('("Jan", "Feb", "Mar", "Apr", "May", "Jun", ');
        RichEdit1.Lines.Add('"Jul", "Aug", "Sep", "Oct", "Nov", "Dec");');
        RichEdit1.Lines.EndUpdate;
      End;
    28: Begin //CREATE TABLE
        ButtonRunSQL.Caption := 'Exec SQL';
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('CREATE TABLE "MyDatabase.Dbf"');
        RichEdit1.Lines.Add('(');
        RichEdit1.Lines.Add(' last_name  CHAR(30),');
        RichEdit1.Lines.Add(' first_name CHAR(40),');
        RichEdit1.Lines.Add(' salary     FLOAT(20,2),');
        RichEdit1.Lines.Add(' zip_code   CHAR(15),');
        RichEdit1.Lines.Add(' work_phone CHAR(30),');
        RichEdit1.Lines.Add(' home_phone CHAR(30),');
        RichEdit1.Lines.Add(' f1         FLOAT,');
        RichEdit1.Lines.Add(' f2         FLOAT(15),');
        RichEdit1.Lines.Add(' i1         SMALLINT,');
        RichEdit1.Lines.Add(' i2         INTEGER,');
        RichEdit1.Lines.Add(' B1         BOOLEAN,');
        RichEdit1.Lines.Add(' D1         DATE,');
        RichEdit1.Lines.Add(' D2         TIME,');
        RichEdit1.Lines.Add(' D3         DATETIME,');
        RichEdit1.Lines.Add(' CUSTNO     AUTOINC,');
        RichEdit1.Lines.Add(' M          MONEY,');
        RichEdit1.Lines.Add(' photo      BLOB(5)');
        RichEdit1.Lines.Add(' PRIMARY KEY (last_name, first_name)');
        RichEdit1.Lines.Add(')');
        RichEdit1.Lines.Add('CREATE TABLE "Table2.Dbf"');
        RichEdit1.Lines.Add('(');
        RichEdit1.Lines.Add(' last_name   CHAR(30),');
        RichEdit1.Lines.Add(' first_name  CHAR(40),');
        RichEdit1.Lines.Add(' salary      FLOAT(20,2)');
        RichEdit1.Lines.Add(');');
        RichEdit1.Lines.EndUpdate;
      End;
    29: Begin //ALTER TABLE
        ButtonRunSQL.Caption := 'Exec SQL';
        RichEdit1.Lines.BeginUpdate;
        RichEdit1.Lines.Add('ALTER TABLE "MyDatabase.Dbf"');
        RichEdit1.Lines.Add(' DROP COLUMN zip_code,');
        RichEdit1.Lines.Add(' DROP salary,');
        RichEdit1.Lines.Add(' ADD COLUMN home_phone CHAR(30),');
        RichEdit1.Lines.Add(' ADD photo BLOB(5);');
        RichEdit1.Lines.EndUpdate;
      End;
    30: Begin //CREATE INDEX
        ButtonRunSQL.Caption := 'Exec SQL';
        RichEdit1.Text := 'CREATE UNIQUE DESC INDEX custdate ON "Table1.DBF" (first_name, last_name); ';
      End;
    31: Begin //DROP TABLE
        ButtonRunSQL.Caption := 'Exec SQL';
        RichEdit1.Text := 'DROP TABLE "table1.dbf" ;';
      End;
    32: Begin //DROP INDEX
        ButtonRunSQL.Caption := 'Exec SQL';
        RichEdit1.Text := 'DROP INDEX "table1.dbf" primaryindex ;';
      End;
    33: Begin //CASE IN SELECT CLAUSE
        ButtonRunSQL.Caption := 'Run SQL';
        RichEdit1.Lines.Add('/* a specially complex one :-) */');
        RichEdit1.Lines.Add('SELECT c.CustNo, City, c.state,');
        RichEdit1.Lines.Add('   CASE');
        RichEdit1.Lines.Add('     WHEN c.state = "FL" THEN "Florida"');
        RichEdit1.Lines.Add('     WHEN c.state = "CA" THEN "California"');
        RichEdit1.Lines.Add('     WHEN c.state = "AL" THEN "Alabama"');
        RichEdit1.Lines.Add('     WHEN c.state = "OR" THEN "Oregon"');
        RichEdit1.Lines.Add('     ELSE "Unknown state"');
        RichEdit1.Lines.Add('   END');
        RichEdit1.Lines.Add('   As StateName, c.ZIP');
        RichEdit1.Lines.Add('FROM (SELECT * FROM customer WHERE Custno BETWEEN 1000 AND 3000) c;');
      End;
    34: Begin //DATASETS DEFINED
        ButtonRunSQL.Caption := 'Run SQL';
        RichEdit1.Text := 'SELECT * FROM "c:\d3\demos\data\biolife.db" bio';
      End;
    35: Begin
        ButtonRunSQL.Caption := 'Run SQL';
        RichEdit1.Text := 'SELECT c.CustNo, Company, c.Addr1, c.City FROM (SELECT * FROM Customer WHERE CustNo BETWEEN 2000 AND 3000) c;';
      End;
  End;