BDE to ADO conversion

rbuser

I am using Delphi 7, RB 7.04, ADO and MS SQL Server 7/2000/2005.

I am finally converting my app from BDE to ADO and I want this upgrade to go
as smoothly as possible.

My users currently store their reports in the database in ASCII format.

I would like to update the LoadFromDatabase to convert the
TdaBDEQueryDataView to TdaADOQueryDataView before RBuilder gets an error. I
would like to do the same for TppChildBDEPipeline to TppChildDBPipeline.

I could develop a conversion utility to update the databases, but I would
prefer to have this happen at runtime. This would also allow for backward
compatibility if I were to load the opposite way.

Ideally I could do a StringReplace on the extracted DB Report code prior to
it being passed to RBuilder.

Can someone point me in the right direction? I assume people have tried this
before.

nardmoseley

- Below is an article about conversion. Based upon your post, I think it
contains information, which you are mostly already aware.

- To convert a template on the fly, try using the
Report.Template.OnLoadStart event. That event fires just after the blob
stream has been read from the dataset, but before any of the stream contents
have been processed by TReader. The OnLoadStart event passes the Stream as a
paramter to the event-hander. So you could read the stream, do the
conversion and then reload the new converted data to the stream.

I'm not aware of anyone that has tried to implement something that would
convert the template on the fly. Perhaps you can be the pioneer.


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com



-------------------------------------------------
Tech Tip: Convert BDE Template Dataviews To ADO
-------------------------------------------------

Currently when DADE is used to create dataviews, the DatabaseName is stored
as part of the query definition. (This is consistent with Delphi's approach
to specifying a database connection for a Query object).

When you created the reports originally, the daDBBDE.pas BDE DADE plugin was
used. Now you want to use ADO. You've already changed the
Designer.Datasettings but this had no effect on the old reports. They still
try to use the BDE connection. Changing the Designer.DAtaSettings only works
for new dataviews that are created, because the new query dataviews are
using the daADO.pas ADO DADE plugin.

In order to convert the templates from BDE to ADO, at the minimum you have
to change the database name and the DADE plugin class names that are stored
in the templates. When a BDE dataview is created, its class type is a
TdaBDEQueryDataview. When an ADO dataview is created, its class type is
TdaADOQueryDataview. These class types are stored in the template. These
have to be changed before the template is loaded into a report.

First, compare a BDE report template to an ADO report template which both
connect to the same database table. Save a BDE and an ADO based report
template to separate ASCII text files. Now compare the dataview definitions.
Change the TdaBDEQueryDataview class name to TdaADOQueryDataview in the BDE
template. Change the BDE alias to your ADOConnection object. Then compare
the table name and field names of the BDE template to the ADO template and
change them accordingly, removing the .db extension on the table name is
necessary. Now load the converted BDE template in your ADO end user
application.

The first step is to make a backup of all your templates before continuing
with a programatic approach. You can convert the templates programatically
by loading the ASCII template files to a TStringList object. Then loop
through the lines of the list and change the text programatically. You can
loop through the files in a directory using ppFileUtils.pas calling the
GetFileNamesForDirectory procedure to load the file names.

If you have binary report templates, you'll also be able to convert these
with an extra couple of steps. You can load the binary template file into
ASCII format, modify it, and then save it back to binary as shown in the
example links below. Keep in mind the conversion is performed without
loading the report template into a TppReport.


This example shows how to load reports stored to the ReportExplorer database
tables and convert them to ascii text.

http://www.digital-metaphors.com/tips/EditTemplatesAsText.zip


This example shows how to convert an .rtm file to asii text


http://www.digital-metaphors.com/tips/ConvertBinaryTemplateToASCII.zip



--
Tech Support mailto:support@digital-metaphors.com
Digital Metaphors http://www.digital-metaphors.com

rbuser

I have developed code that checks the template as it loads and converts a
Paradox template to run on MS SQL, by iterating through all the data
piplines and changing various properties, including table names (e.g. from
tablename.DB to dbo.tablename). The only problem is my current code still
uses the BDE to access MS SQL!

I am going to need exactly the same ability myself in the next 12 months so
would be happy to contribute what I have already done, if it helps...

Pete Colson
peter.colson@lalpac.com

rbuser

Thanks. I think I have it.

I don't have much experience with Streams and BinaryToText calls.

One issue I had was converting the stream back using ObjectTextToBinary so I
left it in the Text format. It seemed to work.

Do you see any problems with that?

Here is my event.

procedure TFormReportWriter.LoadReportStreamEvent(Sender: TObject;
Stream: TStream);
var
TempSize : integer;
TempString : string;
TempStream : TStringStream;
begin
// get string
TempSize := Stream.Size;
SetLength(TempString, TempSize);
Stream.Read(PChar(TempString)^, TempSize);

// check for legacy report
if (Pos('daBDEQueryDataView', TempString) > 0) then
begin
// convert binary to text
TempStream := TStringStream.Create('');
Stream.Position := 0;
ObjectBinaryToText(Stream, TempStream);
TempStream.Position := 0;
TempString := TempStream.DataString;
TempStream.Free;

// replace legacy data view
TempString := StringReplace(TempString, 'daBDEQueryDataView',
'daADOQueryDataView', [rfReplaceAll]);

// replace legacy pipeline
TempString := StringReplace(TempString, 'TppChildBDEPipeline',
'TppChildDBPipeline', [rfReplaceAll]);

TempSize := Length(TempString);
Stream.Position := 0;
Stream.Write(PChar(TempString)^, TempSize);
end;
end;

nardmoseley

No problems - text based report templates work well (they require more
space, so really depends upon how big your templates are and whether that is
an issue)

The Delphi ObjectTextToBinary routine is not documented and using it is not
simple like it should be. Check out the TextStreamToBinaryStream method in
ppTmplate.pas for an example.


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

Thanks.