Solved

How Save Query result to DBF?

Posted on 1997-11-06
4
708 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Objective: - This article will help user in how to convert their numeric value become words. How to use 1. You can copy this code in your Unit as function 2. than you can perform your function by type this code The Code   (CODE) The Im…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

717 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