Solved

Export DBF using SQL script

Posted on 2004-09-15
6
964 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
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…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
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: …

757 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

23 Experts available now in Live!

Get 1:1 Help Now