Solved

How Save Query result to DBF?

Posted on 1997-11-06
4
669 Views
Last Modified: 2010-04-04
In my application, my users are constantly asking for new information and reports faster than I can generate them.  So I set up a simple SQL-interface.  I just write the SQL template for them, and have a module that loads and executes them (executes the query, not the user).  So far, I
have a dataset pointing to the TQuery, so they can SEE the result set.

But now they need to save the results off somewhere else, and it needs to be in the DBF format so they can use Excel to get at the data and do whatever they want.

QUESTION:  After a Query is opened, how do I copy the result set off to a DBF table?  The structure of the query result my vary, so I can't pre-create a table to have the answers coppied into them via UpdateSQL.  I imagine there must be some simple call that I'm missing.  

0
Comment
Question by:cashew
  • 2
4 Comments
 
LVL 3

Expert Comment

by:d4jaj1
ID: 1349682
Cashew,

You can use the SQL CreateTable statement (I know this works for Paradox, not sure with DBase) or you can use the Batchmove method.  If your using the batch move method, simply save the results of the SQL as myfile.sql and use the Batchmove component to move the records from one table to another.  This means you don't have to create a 'Answer' table before hand or modify its structure every time you make a change to the source table.

An even easier way is to use a component that runs the SQL string, and based on what whether you set a property as true or false at runtime, automatically does the BatchMove for you - to either Text, Paradox or dBase.  I have included code below from Brendan Delumpa of the Delphi Corner.  Simply copy the code below and save it as a PAS file (using Notepad). Compile it into Delphi and presto!  A new component that runs the query and automatically move the data appears on your palette.  Any questions, let me know.

***************************************************************
Properties: DestinationTable - Name of destination table. Defaults to 'INIT.DB'

DestDatabaseName - Name destination database. If a component is dropped into a form, you can set this interactively with a property editor I created for it.
DestBatchMoveMode - This is a property of type TBatchMode.

Defaults
                                    to batCopy.
DoBatchMove       - Determines if a batch move should take place at all. If it should (value = True),  the SQL result set will be moved to a persistent data store. Otherwise, a regular Open will occur.

Methods: Execute (virtual)   This is what you will call when using this component. However, since this is a descendant of TQuery, you can always use Open or ExecSQL to go around this function. Notice that this is virtual, which means that you can add more functionality if you wish.
DoEnhQueryOpen:      This takes the place of the Open method, but
(virtual) since it's private, it can only be called by Execute. It too is virtual, so you can override its functionality. I suggest you keep it private to avoid people inadvertently using it.

 Notes:

 You may get a couple of compiler warnings stating that the vars "btc" and "tbl" may
 not have been initialized. Ignore them. The reason for the warning is because the
 vars are declared but only initialized if the Open succeeded. No use in creating
 them if they aren't needed.
 ==================================================================================}
unit enhquery;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  DB, DBTables, DSGNINTF{, alnames};


type
  TDBStringProperty = class(TStringProperty)
  public
    function GetAttributes: TPropertyAttributes; override;
    procedure GetValueList(List: TStrings); virtual; abstract;
    procedure GetValues(Proc: TGetStrProc); override;
  end;

  TDestDBProperty = class(TDBStringProperty)
  public
    procedure GetValueList(List: TStrings); override;
  end;

  {Main type information for TEnhQuery}
  TEnhQuery = class(TQuery)
  private
    FDestTblName : String;
    FDestDBName  : String;
    FBatchMode   : TBatchMode;
    FDoBatchMove : Boolean;
    procedure SetDestTblName(Value : String);
    procedure DoEnhQueryOpen; virtual;
  public
    constructor Create(AOwner : TComponent); override;
    procedure   Execute; virtual; {Let people override this}
  published
    property DestinationTable : String      read FDestTblName write SetDestTblName;
    property DestDatabaseName : String      read FDestDBName  write FDestDBName;
    property DestBatchMoveMode: TBatchMode  read FBatchMode   write FBatchMode;
    property DoBatchMove      : Boolean     read FDoBatchMove write FDoBatchMove;
  end;

procedure Register;

implementation

constructor TEnhQuery.Create(AOwner : TComponent);
begin
  inherited Create(AOwner);
  FDestTblName  := 'INIT.DB'; {Set initial value of Destination Table on Create}
  FDestDBName   := Session.PrivateDir;
  FBatchMode    := batCopy;
  FDoBatchMove  := True;
end;

procedure TEnhQuery.SetDestTblName(Value : String);
begin
  if (FDestTblName <> Value) then
    FDestTblName := Value;
end;

{=========================================================================
 This is a very simple routine that will determine which route to take with
 respect to executing the SQL query. It gives the component a bit of
 intelligence, so the user need only use one call. Essentially, it looks
 at the first line of the query; if it finds the word SELECT, then it
 knows to call OpenProc, which will open the query and perform a batch move.
 =========================================================================}
procedure TEnhQuery.Execute;
begin
  if (SQL.Count > 0) then
    if DoBatchMove then {Check to see if a batch move is desired}
      if (Pos('SELECT', SQL[0]) > 0) then
        if (DestinationTable <> '') AND (DestDatabaseName <> '') then
          try
            DoEnhQueryOpen;
          except
            raise Exception.Create('Enhanced Query DoEnhQueryOpen procedure did not execute properly. Aborting');
            Abort;
          end
        else
          MessageDlg('You must supply a Destination Table and DatabaseName', mtError, [mbOK], 0)
      else
        Open
    else
      try
        ExecSQL;
      except
        raise Exception.Create('ExecSQL did not execute properly. Aborting');
        Abort;
      end
  else
    MessageDlg('You have not provided any SQL to execute' + #13 +
               'so there is nothing to process. Load the' + #13 +
               'SQL property with a query', mtError, [mbOk], 0);
end;

procedure TEnhQuery.DoEnhQueryOpen;
var
  btc : TBatchMove;
  tbl : TTable;
begin
  try
    Open;
    try
      tbl := TTable.Create(Application);
      btc := TBatchMove.Create(Application);

      with tbl do begin
        Active        := False;
        DatabaseName  := DestDatabaseName;
        TableName     := DestinationTable;
      end;

      with btc do begin
        Source      := Self;
        Destination := tbl;
        Mode        := DestBatchMoveMode;
        Execute;
      end;
    finally
      btc.Free;
      tbl.Free;
    end;

  except
    Abort;
  end;
end;


{=============================================================================
 TDestDBProperty property editor override functions. Since the property editor
 is derived from TStringProperty, we only need to override the functions
 associated with displaying our dialog box.
 =============================================================================}

function TDBStringProperty.GetAttributes: TPropertyAttributes;
begin
  Result := [paValueList, paSortList, paMultiSelect];
end;

procedure TDBStringProperty.GetValues(Proc: TGetStrProc);
var
  I: Integer;
  Values: TStringList;
begin
  Values := TStringList.Create;
  try
    GetValueList(Values);
    for I := 0 to Values.Count - 1 do Proc(Values[I]);
  finally
    Values.Free;
  end;
end;

procedure TDestDBProperty.GetValueList(List: TStrings);
begin
  (GetComponent(0) as TDBDataSet).DBSession.GetDatabaseNames(List);
end;


procedure Register;
begin
  RegisterPropertyEditor(TypeInfo(String), TEnhQuery, 'DestDatabaseName', TDestDBProperty);
  RegisterComponents('BD', [TEnhQuery]);
end;

end.



{With this component, here's all you do to perform a basic extract query:



1.Create an instance of the component 2.Set the SQL property 3.Set the Destination TableName (it defaults to 'INIT.DB') 4.Set the Destination DatabaseName (it defaults to Session.PrivateDir)



As you can see, it's all a matter of setting properties. You'll notice in the properties section of the code, I've got a property called DoBatchMove. This is a Boolean property that defaults to True. If you set it to false, the batch move will not occur, but the query will be opened. This ensures that you can use the component like a regular TQuery. You'd set this to False when you are using the component in conjunction with a TDataSource and TDBGrid.

As mentioned in the code comments, we have a custom property editor. For those of you who have wanted to learn how to do custom drop-down list property editors, study the code above. You'll be amazed at how incredibly easy it is to do.
}
0
 

Author Comment

by:cashew
ID: 1349683
Thank you for your response.  However, I think the Batchmove still copies the table to a paradox table.  Also, that much code was a bit of overkill for the task at hand.  To get around the problem, I used the following instead:

// SaveAnswer is a SaveDialog on the form.

procedure TFM_SQL.btnSaveAnsClick(Sender: TObject);
Var
  OutFile : TextFile;
  Col : integer;
begin
  SaveAnswer.FileName := 'Answer.CSV';
  if SaveAnswer.Execute then
  With Query1 do begin
     assignFile(OutFile,SaveAnswer.FileName);
     Rewrite(OutFile);

     // write out the header
     for Col := 0 to FieldCount-1 do
         If Col = 0 then
            Write(OutFile,'"',Fields[Col].FieldName,'"')
         else
            Write(OutFile,',"',Fields[Col].FieldName,'"');
     Writeln(OutFile);

     // write out the data
     First;
     While not EOF do Begin
         for Col := 0 to FieldCount-1 do
             If Col = 0 then
                Write(OutFile,'"',Fields[Col].AsString,'"')
             else
                Write(OutFile,',"',Fields[Col].AsString,'"');
         writeln(OutFile);
         Next;
     End;
     CloseFile(OutFile);
  End;
end;
0
 
LVL 3

Expert Comment

by:d4jaj1
ID: 1349684
Actually, my answer was in the first paragraph.  The rest of the answer was (at least I thought) a nice to have.  Simply copying the text as a pas file meant you didn't have to write code to do batchmove & queries.  Simply tell the compoenent to do a batchmove or not - two compoenents in one.  Also, BatchMove will handle dBASE.  Sorry for the confusion, I thought I'd give you a little more information to possibly solve future problems.
0
 
LVL 3

Accepted Solution

by:
Pegasus100397 earned 170 total points
ID: 1349685
Cashew,

I have EXACTLY the same problem (with user requests) that you do! I made up an ad-hoc screen where the users could perform the querys and then click an EXPORT button which would send it off to MS Excel in a no-questions-asked .CVS format. To do this I had to make sure that the numeric and date fields WERE NOT quoted, while the strings and other text were. The following is the actual export routine from my dlgExport dialog box. This dialog will take the name of ANY table or query, ask for a filename and export only those fields that are set to "visible". It is very fast and my users love it. Excel recognises the output without the users having to specify what fields are numeric and which ones are dates. This has saved me a TON of work since the users can now do thier own thing and leave me alone to do what I'm supposed to be doing... PROGRAMMING! : )

Note: Since the dialog box immediately calls a Save dialog I only  use the dialog for displaying the progress of the Export I placed the code in the FormActivate event.

procedure TdlgExport.FormActivate(Sender: TObject);
var F: TextFile;
       sRowText: String;  // Collection of fields built into .CSV format
       iRow, iCol: Integer;
       slFieldTypes: TStrings; // String list of field types (date, number,etc)
       sFieldType: String;
begin
  slFieldTypes := TStringList.Create;
  if sdExport.Execute then //ask for a filename
  begin
  If sdExport.FileName <> '' then
     With tblTableToExport DO  // tblTableToExport is type TTable
          Begin
          DisableControls;
          dlgExport.Caption := 'Writing "' + sdExport.FileName + '"';
          dlgExport.Update;
          AssignFile(F, sdExport.FileName);
          Rewrite(F);                      //Open & Init the file
          sRowText := '';
          {First we write out the column headers and determine the field types}
          For iCol := 0 to FieldCount -1 DO   // step through columns
            Begin
              If Fields[iCol].Visible then
               Begin
                 Case Fields[iCol].DataType OF
                  ftUnknown:     sFieldType := 'S';
                  ftString:      sFieldType := 'S';
                  ftSmallInt:    sFieldType := 'N';
                  ftInteger:     sFieldType := 'N';
                  ftWord:        sFieldType := 'N';
                  ftBoolean:     sFieldType := 'S';
                  ftFloat:       sFieldType := 'N';
                  ftCurrency:    sFieldType := 'N';
                  ftBCD:         sFieldType := 'S';
                  ftDate:        sFieldType := 'D';
                  ftTime:        sFieldType := 'D';
                  ftDateTime:    sFieldType := 'D';
                  ftBytes:       sFieldType := 'S';
                  ftVarBytes:    sFieldType := 'S';
                  ftAutoInc:     sFieldType := 'N';
                  ftBlob:        sFieldType := 'S';
                  ftMemo:        sFieldType := 'S';
                  ftGraphic:     sFieldType := 'S';
                  ftFmtMemo:     sFieldType := 'S';
                  ftParadoxOle:  sFieldType := 'S';
                  ftDBaseOLE:    sFieldType := 'S';
                  ftTypedBinary: sFieldType := 'S';
                 end;
              slFieldTypes.Add(sFieldType); //build list of field types
              sRowText := sRowText + ',"' + Fields[iCol].DisplayLabel + '"';
             end;
            end;
           Writeln(F, Copy(sRowText,2,Length(sRowText)));  // Write column headers
           // here we start exporting the actual data
           First;     // Go to first Record
          For iRow := 0 to RecordCount-1 DO     // step through rows
            Begin
            sRowText := '';
            For iCol := 0 to FieldCount -1 DO   // step through columns
              If Fields[iCol].Visible then
              Begin
                 If slFieldTypes[iCol] = 'S' then
                       Begin
                       sRowText := sRowText + ',"' + Fields[iCol].AsString + '"';
                       end;
                 If slFieldTypes[iCol] = 'N' then
                       Begin
                       sRowText := sRowText + ','+ Fields[iCol].AsString;
                       end;
                 If slFieldTypes[iCol] = 'D' then
                       Begin
                       sRowText := sRowText + ','+
                       FormatDateTime('mm/dd/yy', Fields[iCol].AsDateTime);
                       end;

                 end;
              lblRows.Caption := IntToStr(iRow);
              dlgExport.Update;
              Next;           // Next Record
              Writeln(F, Copy(sRowText,2,Length(sRowText)));  // Remove 1st Comma
              end;
      CloseFile(F);
      First;
      EnableControls;
          end;
  end;
 slFieldTypes.Free;      // Free the String List
dlgExport.Close;
end;

0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now