?
Solved

Export DBF using SQL script

Posted on 2004-09-15
6
Medium Priority
?
990 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
[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
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 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…
Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Suggested Courses
Course of the Month10 days, 14 hours left to enroll

770 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