ginsonic
asked on
Export DBF using SQL script
How can I get a sql script ( create Table and export records ) for my dbf table ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
and here is how I execute it:
exp_thread := TThreadExport.Create(DM.Qu ery, edtFileName.Text,
edtTableName.Text, cbDateFormat.Text, cbInsertCommit.Checked);
while not exp_thread.Finished do
Application.ProcessMessage s;
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
exp_thread := TThreadExport.Create(DM.Qu
edtTableName.Text, cbDateFormat.Text, cbInsertCommit.Checked);
while not exp_thread.Finished do
Application.ProcessMessage
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
why sql?
if u use the bde, then u can easily use the batchmove-method like
table1.batchmove(table2,ba tCopy);
or if u need only a prtial export use a query like
(an older sample)
Function CreateDBFTableFromQuery(AQ uery : 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(Qu ery1,'ATab le') then
Showmessage('table created')
else
Showmessage('table creation failed');
end;
meikl ;-)
if u use the bde, then u can easily use the batchmove-method like
table1.batchmove(table2,ba
or if u need only a prtial export use a query like
(an older sample)
Function CreateDBFTableFromQuery(AQ
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
begin
if CreateDBFTableFromQuery(Qu
Showmessage('table created')
else
Showmessage('table creation failed');
end;
meikl ;-)
ASKER
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.
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].fieldn ame+','
sr := sr +query1.fields[query1.fiel ds.count-1 ].fieldnam e+') values (';
while not query1.eof do
begin
s := sr;
for i := 0 to query1.fields.count-2 do
s := s +'"'+query1.fields[i].asst ring+'"'+' ,'
s := s +'"'+query1.fields[query1. fields.cou nt-1].AsSt ring+'"'+' );'+#13#10 ;
sc := sc+s;
query1.next;
end;
end;
sc holds now your script
meikl ;-)
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].fieldn
sr := sr +query1.fields[query1.fiel
while not query1.eof do
begin
s := sr;
for i := 0 to query1.fields.count-2 do
s := s +'"'+query1.fields[i].asst
s := s +'"'+query1.fields[query1.
sc := sc+s;
query1.next;
end;
end;
sc holds now your script
meikl ;-)
What do you use to access the DBF files?
Regards Jacco