Solved

Export DBF using SQL script

Posted on 2004-09-15
6
968 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Using idhttp to login to instagram 2 76
Working with hours 3 46
tidtcpserver connection lost handle 2 71
SUM 2 INTEGER ARRAYS INTO 1 10 93
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
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…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

895 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

14 Experts available now in Live!

Get 1:1 Help Now