Export DBF using SQL script

How can I get a sql script ( create Table and export records ) for my dbf table ?
LVL 9
ginsonicAsked:
Who is Participating?
 
Ivanov_GConnect With a Mentor Commented:
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
 
JaccoCommented:
What database server is the script for?
What do you use to access the DBF files?

Regards Jacco
0
 
Ivanov_GCommented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
kretzschmarCommented:
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
 
ginsonicAuthor Commented:
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
 
kretzschmarCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.