Solved

Export DBF using SQL script

Posted on 2004-09-15
6
971 Views
Last Modified: 2010-04-05
How can I get a sql script ( create Table and export records ) for my dbf table ?
0
Comment
Question by:ginsonic
6 Comments
 
LVL 10

Expert Comment

by:Jacco
ID: 12072162
What database server is the script for?
What do you use to access the DBF files?

Regards Jacco
0
 
LVL 12

Accepted Solution

by:
Ivanov_G earned 500 total points
ID: 12072163
I made a thread, which export dataset to SQL inserts. All you have to do is to pass the dataset to this thread. But no CREATE TABLE will be done. Here is the code:

----------------------------------------

unit ClassThreadExport;

interface

uses
  Classes, SysUtils, ADODB, Dialogs, DB;

type
  TThreadExport = class(TThread)
  private
    Query       : TADOQuery;
    FName       : String;
    WithCommit  : Boolean;
    TblName     : String;
    DTFormat    : String;
    SQLLines    : TStringList;
    function GenerateInsert : String;
  public
    Finished : Boolean;
    Done     : Integer;
    constructor Create(ADOQuery : TADOQuery; FileName : String;
                       TableName : String; DateFormat : String; LastCommit: Boolean);
    destructor Destroy; override;
    procedure Execute; override;
  end;

implementation

constructor TThreadExport.Create(ADOQuery : TADOQuery; FileName : String;
                       TableName : String; DateFormat : String; LastCommit: Boolean);
begin
  // create
  inherited Create(False);
  // get params  
  Query      := ADOQuery;
  FName      := FileName;
  WithCommit := LastCommit;
  TblName    := TableName;
  DTFormat   := DateFormat;
  Finished   := False;
  Done       := 0;
  // create the TStringList
  SQLLines := TStringList.Create;
  // go to the first record
  Query.First;
end;

destructor TThreadExport.Destroy;
begin
  // destroy
  SQLLines.Free;
  inherited;
end;

procedure TThreadExport.Execute;
var
  line    : String;
begin
  // execute
  while not Query.Eof do
    begin
      line := GenerateInsert;
      SQLLines.Add(line);
      Inc(Done);
      Query.Next;
    end;
  // put commit
  SQLLines.Add('COMMIT;');
  // save to file
  if FileExists(FName) then
    DeleteFile(FName);
  SQLLines.SaveToFile(FName);
  Finished := True;
end;

function TThreadExport.GenerateInsert: String;
var
  counter     : Integer;
  str         : String;
begin
  // generate INSERT statement for the current record
  // of the Query dataset
  str := 'INSERT INTO ' + TblName + ' ( ';
  for counter := 0 to Query.FieldCount - 1 do
    begin
      str := str + Query.Fields[counter].FullName;
      if counter < Query.FieldCount - 1 then
        str := str + ', '
      else
        str := str + ' ) ';
    end;
  // insert the VALUES
  str := str + 'VALUES ( ';
  for counter := 0 to Query.FieldCount - 1 do
    begin
      // integer of float type
      if Query.Fields[counter].DataType in [ftSmallint, ftInteger, ftWord,
                 ftFloat, ftCurrency, ftBCD, ftLargeint, ftBoolean] then
        str := str + Query.Fields[counter].AsString;
      // string ot char type
      if Query.Fields[counter].DataType in [ftString, ftFixedChar,
                 ftWideString, ftMemo] then
        str := str + '''' + Query.Fields[counter].AsString + '''';
      // date and time type
      if Query.Fields[counter].DataType in [ftDate, ftTime, ftDateTime,
                 ftTimeStamp] then
        str := str + '''' + FormatDateTime(DTFormat, Query.Fields[counter].AsVariant) + '''';
      if counter < Query.FieldCount - 1 then
        str := str + ', '
      else
        str := str + ' ); ';
      end;
  // return the result
  Result := str;
end;

end.
0
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12072178
and here is how I execute it:

  exp_thread := TThreadExport.Create(DM.Query, edtFileName.Text,
                   edtTableName.Text, cbDateFormat.Text, cbInsertCommit.Checked);
  while not exp_thread.Finished do
    Application.ProcessMessages;

  Parameters:
    DM.Query - the dataset that is going to be exported
    edtFileName.Text - file name for the export SQL file
    edtTableName.Text - table name for INSERT INTO ....
    cbDateFormat.Text - datetime format for date, time and datetime fields
    cbInsertCommit.Checked - put COMMIT; after the last insert
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 27

Expert Comment

by:kretzschmar
ID: 12072223
why sql?

if u use the bde, then u can easily use the batchmove-method like

table1.batchmove(table2,batCopy);

or if u need only a prtial export use a query like
(an older sample)

Function CreateDBFTableFromQuery(AQuery : TQuery; ATableName : String) : Boolean;
var t : TTable;
begin
  result := true;
  try
    t := ttable.create(nil);
    try
      t.DatabaseName := AQuery.Databasename;
      t.TableName := ATableName;
      t.TableType := ttDBase;
      AQuery.Open;
      t.BatchMove(Aquery,batCopy);
      AQuery.Close;
    finally
      t.free;
    end;
  except
    result := False;
  end;
end;

//usage
procedure TForm1.Button1Click(Sender: TObject);
begin
  if CreateDBFTableFromQuery(Query1,'ATable') then
    Showmessage('table created')
  else
    Showmessage('table creation failed');

end;


meikl ;-)
0
 
LVL 9

Author Comment

by:ginsonic
ID: 12075107
I have a MySQL database on my  portal and intend to write a database application that let the user to export the own records to my portal. Because the application use Fox dbf and the portal MySql I wish to send a SQL script to portal.
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 12076668
well in this case you can build a text file in your script language

usual like

insert into tablename /fieldList/ values /valuelist/

you can all fields treat asstring except dates are a bit problematic

sample from head

var
  sr : string;  //first statementpart, tablename must be known
  s : String;  //workString
  sc : String;  //script
  i : integer;
begin
  s := '';
  sc := '';
  query1.close;  //you can also use a table for this
  query1.sql.text := 'YourSQLStatement';
  query1.open;
  sr := 'Insert Into YourKnownTableName ('
  for i := 0 to query1.fields.count-2 do
    sr := sr+query1.fields[i].fieldname+','
  sr := sr +query1.fields[query1.fields.count-1].fieldname+') values (';
  while not query1.eof do
  begin
    s := sr;
    for i := 0 to query1.fields.count-2 do
      s := s +'"'+query1.fields[i].asstring+'"'+','
    s := s +'"'+query1.fields[query1.fields.count-1].AsString+'"'+');'+#13#10;
    sc := sc+s;
    query1.next;
  end;
end;

sc holds now your script

meikl ;-)
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
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 Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

809 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