Title: XML and Database through Delphi
Question: This article will explain about generating xml files by reading data from Paradox tables and inserting the same data back in to the same table by reading data from the previously generated xml file(or any database which can be accessed through Delphi).This won't work for tables with memo fields.I have used MS XML Parser for parsing the XML document.So you must need IE 5.0 or later installed in your system.
Answer:
This article will explain about generating xml files by reading data from Paradox tables and inserting the same data back in to the
same table by reading data from the previously generated xml file(or any database which can be accessed through Delphi).This
won't work for tables with memo fields.I have used MS XML Parser for parsing the XML document.So you must need IE 5.0
or later installed in your system.
Generating XML file
I have used the following convention for the xml file
I . The root name of the xml file is same as that of the table name(In this case country).
II. Each record from the table comes in between the tags and
III. Each data from the table comes in between the tags and
-
-
Argentina
Buenos Aires
South America
2777815
32300003
.
.
.
Start a new application and place a Button and a Table component on the main form.Set the properties of the table component
as follows.
DatabaseName : DBDEMOS
Name : Table1
TableName : country (Remove the extention ".db")
Active : True
Select Project/Import Type library.This will display the "Import Type Library" dialog. Select "Microsoft XML,Version
2.0(version 2.0)" from the list box and then click "Create Unit" Button.This will add MSXML_TLB unit to your project.
Add MSXML_TLB to the uses clause in the interface portion of your unit.
Declare the following variables in the var Section
DataList : TStringlist;
doc : IXMLDOMDocument;
root,child,child1 : IXMLDomElement;
text1,text2 : IXMLDOMText;
nlist : IXMLDOMNodelist;
dataRecord : String;
add the following function(makeXml) to your unit.This will generate an XML file by reading data from country
table(DBDEMOS).
function TForm1.makeXml(table:TTable):Integer;
var
i : Integer;
xml,temp : String;
begin
try
table.close;
table.open;
xml := table.TableName;
doc := CreateOleObject('Microsoft.XMLDOM') as IXMLDomDocument;
//Set the root name of the xml file as that of the table name.
//In this case "country"
root := doc.createElement(xml);
doc.appendchild(root);
//This while loop will go through the entaire table to generate the xml file
while not table.eof do
begin
//adds the first level children , Records
child:= doc.createElement('Records');
root.appendchild(child);
for i:=0 to table.FieldCount-1 do
begin
//adds second level children
child1:=doc.createElement(table.Fields[i].FieldName);
child.appendchild(child1);
//Check field types
case TFieldType(Ord(table.Fields[i].DataType)) of
ftString:
begin
if Table.Fields[i].AsString ='' then
temp :='null' //Put a default string
else
temp := table.Fields[i].AsString;
end;
ftInteger, ftWord, ftSmallint:
begin
if Table.Fields[i].AsInteger 0 then
temp := IntToStr(table.Fields[i].AsInteger)
else
temp := '0';
end;
ftFloat, ftCurrency, ftBCD:
begin
if table.Fields[i].AsFloat 0 then
temp := FloatToStr(table.Fields[i].AsFloat)
else
temp := '0';
end;
ftBoolean:
begin
if table.Fields[i].Value then
temp:= 'True'
else
temp:= 'False';
end;
ftDate:
begin
if (not table.Fields[i].IsNull) or
(Length(Trim(table.Fields[i].AsString)) 0) then
temp := FormatDateTime('MM/DD/YYYY',
table.Fields[i].AsDateTime)
else
temp:= '01/01/2000'; //put a valid default date
end;
ftDateTime:
begin
if (not table.Fields[i].IsNull) or
(Length(Trim(table.Fields[i].AsString)) 0) then
temp := FormatDateTime('MM/DD/YYYY hh:nn:ss',
Table.Fields[i].AsDateTime)
else
temp := '01/01/2000 00:00:00'; //Put a valid default date and time
end;
ftTime:
begin
if (not table.Fields[i].IsNull) or
(Length(Trim(table.Fields[i].AsString)) 0) then
temp := FormatDateTime('hh:nn:ss',
table.Fields[i].AsDateTime)
else
temp := '00:00:00'; //Put a valid default time
end;
end;
//
child1.appendChild(doc.createTextNode(temp));
end;
table.Next;
end;
doc.save(xml+'.xml');
memo1.lines.Append(doc.xml);
Result:=1;
except
on e:Exception do
Result:=-1;
end;
end;
Call the above function in Button1's onclick event
procedure TForm1.Button1Click(Sender: TObject);
begin
if makeXml(table1)=1 then
showmessage('XML Generated')
else
showmessage('Error while generating XML File');
end;
If you open the generated xml file(country.xml) in IE 5.0 or later, it will look like as follows-
-
-
Argentina
Buenos Aires
South America
2777815
32300003
-
Bolivia
La Paz
South America
1098575
7300000
.
.
.
-
Venezuela
Caracas
South America
912047
19700000
Inserting data
You have created the XML file from the existing data in the country table.So the data in the generated xml file and the country
table are same.If you try to insert the data into country table from the generated xml file without deleting the existing data in the
country table it will show the error "Primary key violation" .So you must delete the existing data from the country table
before doing the following.
Add another button(button2) and a memo component to the main form.Add the following code to the onclick event of
button2.The memo is for displaying the status of insertion(sussess/failure)
procedure TForm1.Button2Click(Sender: TObject);
var
i,ret_val,count:Integer;
strData:String;
begin
//Before inserting data in to the country table,make sure that the data in
//the generated xml file(country.xml) and country table(DBDEMOS) are
//different.
try
count:=1;
DataList:=TStringList.Create;
memo1.Clear;
doc := CreateOleObject('Microsoft.XMLDOM') as IXMLDomDocument;
//Load country.xml file
doc.load('country.xml');
nlist:=doc.getElementsByTagName('Records');
memo1.lines.append('Table Name :country');
memo1.lines.append('---------------------');
for i:=0 to nlist.Get_length-1 do
begin
travelChildren(nlist.Get_item(i).Get_childNodes);
//Removes the first character(,) from dataRecord
strData:=copy(dataRecord,2,length(dataRecord));
memo1.lines.append(strData);
dataRecord:='';
ret_val:=insertintotable(Datalist);
if ret_val=1 then
memo1.lines.append('Data inserted successfully.............!')
else if ret_val=-1 then
memo1.lines.append('Error while updating.....Try again.....!');
memo1.lines.append('============================================='
+'==(Record no. :'+inttostr(count)+')');
DataList.Clear;
count:=count+1;
end;
except
on e:Exception do
Showmessage(e.message);
end;
end;
nlist(refer above program) contains a list of nodes.In our case the first node list is...
Argentina
Buenos Aires
South America
2777815
32300003
We are passing this node list to a recursive function,travelchildren.This will recursively travel through the node list until it finds a
text node(data).This text data will be added to a TStringlist(Datalist) variable.When the travelchildren completes the first travel
through the node list(nlist),Datalist will contain the strings Argentina,Buenos Aires,South America,2777815,32300003.Finally
we pass this stringlist(Datalist) to the function insertintotable, which will insert one record in to the country table.This will be
repeated for the whole XML file.
procedure TForm1.travelChildren(nlist1:IXMLDOMNodeList);
var
j:Integer;
temp:String;
begin
for j:=0 to nlist1.Get_length-1 do
begin
//node type 1 means an entity and node type 5 means EntityRef
if((nlist1.Get_item(j).Get_nodeType= 1) or (nlist1.Get_item(j).Get_nodeType=5)) then
travelChildren(nlist1.Get_item(j).Get_childNodes)
//node Type 3 means a text node,ie you find the data
else if(nlist1.Get_item(j).Get_nodeType=3) then
begin
temp:= trim(nlist1.Get_item(j).Get_nodeValue);
dataRecord:=dataRecord+','+temp; //this is for displaying a single record on the memo
DataList.Add(temp); //Datalist will contain one record after completing one full travel through the node list
end
end;
end;
function TForm1.insertintotable(stpt:TStringList):Integer;
var
i:Integer;
begin
table1.close;
table1.open;
table1.Insert;
for i := 0 to stpt.Count - 1 do
begin
table1.Fields[i].AsVariant:= stpt[i];
end;
try
table1.post;
result:=1;
except
on E:Exception do
result:=-1;
end;
end;
Conclusion
You can generalize this program for any database,so that data can be transfered through xml files in a network(or over the
internet) and update a database at the other end.I have not consider the special characters like &,,','',etc. while generating
the xml files from tables.You can change this program in such a way to replace this special characters with corresponding xml
equivilents while generating xml file.