ADO and ClientDataSet
Two months ago, I've shown you how to convert BDE tables to XML (using a ClientDataSet component), where the resulting XML files could be moved to Linux and loaded by the ClientDataSet components inside Kylix.
The XML format of the TClientDataSet component is the same in Delphi and C++Builder (on Windows) and Kylix (on Linux).
Delphi and C++Builder have another set of data access components that are capable of saving their content as XML files: ADO Express.
A TADOTable component also has SaveToFile and LoadFromFile methods that can save and load to XML files.
It would be nice if these two file formats were compatible, and you could use ADO Express to connect an ADOQuery to perform a certain query on an SQL Server database for example, then save the result as XML, and use a ClientDataSet to load the XML data again (perhaps even in a different tier).
Alas, the XML formats are quite different.
ADO XML to ClientDataSet XML
In order to convert from ADO to ClientDataSet format, we need a DataSetProvider in between.
The ADO DataSet is used to feed the DataSetProvider component (using its DataSet property).
The DataSetProvider in its turn is used to feed the ClientDataSet component, so after we opened the ClientDataSet, we can call the SaveToFile method of the ClientDataSet.
This will produce a ClientDataSet XML file or binary file that can be used in Delphi, C++Builder and Kylix, as noted earlier.
The code below for a C++Builder console application will compile and work with C++Builder 5 Enterprise (and it shouldn't be difficult to write similar Delphi source code - let me know if you want some help):
int main(int argc, char* argv[])
{
if (argc <= 1)
{
printf("Usage: ADO2CDS ado.xml [-x]\n\n");
printf("Converts ADO.XML to ClientDataSet format\n");
}
else
try
{
CoInitialize(NULL);
try
{
TADOTable* ADOTable = new TADOTable(NULL);
try
{
ADOTable->LoadFromFile(argv[1]);
TDataSetProvider* DataSetProvider = new TDataSetProvider(NULL);
try
{
DataSetProvider->DataSet = ADOTable;
TClientDataSet* ClientDataSet = new TClientDataSet(NULL);
try
{
ClientDataSet->SetProvider(DataSetProvider);
ClientDataSet->Open();
ClientDataSet->SaveToFile(ChangeFileExt(argv[1],".xml"), dfXML);
}
__finally
{
delete ClientDataSet;
}
}
__finally
{
delete DataSetProvider;
}
}
__finally
{
delete ADOTable;
}
}
__finally
{
CoUninitialize();
}
}
catch(...)
{
printf("Something went wrong...\n");
}
return 0;
}
Note that the above listing can load either an ADO XML or an ADO ADTG (Advanced Data TableGram) file.
In this example, I simply load the file in an TADOTable component by calling LoadFromFile, but you can also use a TADOConnection component to talk to a real database, such as SQL Server, of course.
Also note that we write the result back in the same filename using an .xml extension (so potentially we overwrite the input .xml file).
If you want to save to a ClientDataSet CDS (binary) file, you just need to make sure to change the SaveToFile call:
ClientDataSet->SaveToFile(ChangeFileExt(argv[1],".cds"), dfBinary);
In Delphi, the second argument of the ClientDataSet.SaveToFile method is a default method (default value is dfBinary).
However, the file format can be influenced by the given extension: an .xml extension will generate an XML file, while any other extension will result in a binary CDS file.
ADO to Linux
The techniques used in this article open up a way to send data from - for example - an SQL Server database via ADO Express and a DataSetProvider to a ClientDataSet and then (indirectly) Linux.
Right now, the last step (sending the XML or binary data from Windows to Linux) needs to be made by hand.
However, when the Enterprise edition of Kylix ships, this last step can be automated as well using the upcoming TxxxConnection components.
In the meantime, you can use the example shown above, or the Delphi 5 code below (which uses an optional additional command-line parameter to determine whether or not to generate an .XML file):
program ADO2CDS;
uses
SysUtils, ActiveX, ADOdb, Provider, DBClient;
var
ADOTable: TADOTable;
DataSetProvider: TDataSetProvider;
ClientDataSet: TClientDataSet;
Flags: String;
begin
if ParamCount < 1 then
begin
writeln('Usage: ADO2CDS ado.xml [-x]');
writeln;
writeln('Converts ADO.XML to ClientDataSet format')
end
else
try
CoInitialize(nil);
try
ADOTable := TADOTable.Create(nil);
try
ADOTable.LoadFromFile(ParamStr(1));
DataSetProvider := TDataSetProvider.Create(nil);
try
DataSetProvider.DataSet := ADOTable;
ClientDataSet := TClientDataSet.Create(nil);
try
ClientDataSet.SetProvider(DataSetProvider);
ClientDataSet.Open;
Flags := UpperCase(ParamStr(2));
if Pos('X',Flags) > 0 then
ClientDataSet.SaveToFile(ChangeFileExt(ParamStr(1),'.xml'))
else
ClientDataSet.SaveToFile(ChangeFileExt(ParamStr(1),'.cds'))
finally
ClientDataSet.Free
end
finally
DataSetProvider.Free
end
finally
ADOTable.Free
end
finally
CoUninitialize
end
except
writeln('Something went wrong...')
end
end.
One last remark: please note that this conversion is one-way only at this time.
Using the code above I can convert ADO's XML files to the ClientDataSet XML format used by Delphi, C++Builder and Kylix.
However, I cannot turn the file back again (although I'm working on that), so if you accidently overwrite your ADO XML file with a ClientDataSet version, please don't come back to me asking to undo the conversion!
In other words: The code in this article is free to use, but use at your own risk!
Having said that, I'm open to feedback and suggestions (by e-mail), of course.
And don't hesitate to let me know if you found this article useful, helpful or insightful in any way.
Thanks in advance!
