Importing a report from .rtm file to another database

rbuser

Hi,

I'm using rb end user application to create reports.
To developer it, I'm using Oracle and save it to database.

But, to use in a production environment, I have to export it from Oracle and import it to
Interbase database.

I'm using report designer to Save to .rtm file from Oracle, and Load from .rtm file to
Interbase. However, the SQL code generated by RB is different between them, and the
TableAlias used in Interbase has a "_1" at end. So, the report imported from Oracle
doesn't work in a Interbase environment.

Is there a way to do this?

Using D7 with RB Ent 7.04.

TIA,
Laercio

nicocizik

Hi Laercio,

Take a look at the article below. It refers to BDE and ADO but the concept
is the same. BDE = (your Oracle connection) and ADO = (your Interbase
connection) in your case.

-------------------------------------------------
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

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hello Nico,

Thanks for your help.
Based on your tip and in an example about how to use OnLoadEnd event, I created a routine
to automatic convert Oracle templates to Interbase templates.
Until now, I think it's working well with all templates that I have tested.
Please, see the code below, and say me if is everything all right or there are something
wrong with my code:



procedure TfModRpb.DataModuleCreate(Sender: TObject);
begin
// OnLoadEnd from Report.Template
ppReport1.Template.OnLoadEnd := SLReportLoadEndEvent;
end;

procedure TfModRpb.SLReportLoadEndEvent(Sender: TObject);
const
coFLDSEP = '_';
clINTERBASEALIAS = coFLDSEP + '1';
var
lSql: TdaSQL;
iTot, i: integer;


procedure SetFieldInt(AField: TdaField);
begin
if AField <> nil then
if Pos(coFLDSEP, AField.TableSQLAlias) = 0 then
AField.TableSQLAlias := AField.TableSQLAlias + clINTERBASEALIAS;
end;


procedure SetTablesAliasInt;
var
j, k: integer;
lTable: TdaTable;
begin
// Alias from Selected Tables
for j := 0 to lSql.SelectTableCount-1 do
begin
lTable := lSql.SelectTables[j];

if Pos(coFLDSEP, lTable.SQLAlias) = 0 then
lTable.SQLAlias := lTable.SQLAlias + clINTERBASEALIAS;

// Joins
for k := 0 to lTable.TableJoinCount-1 do
begin
SetFieldInt( lTable.TableJoins[k].LocalField );
SetFieldInt( lTable.TableJoins[k].ForeignField );
end;

end;
end;


procedure SetFieldsAliasInt;
var
j: integer;
begin
// Alias from selected fields
for j := 0 to lSql.SelectFieldCount-1 do
SetFieldInt(lSql.SelectFields[j]);

// Alias from calc fields
for j := 0 to lSql.CalcFieldCount-1 do
SetFieldInt( lSql.CalcFields[j] );

// Alias from group by fields
for j := 0 to lSql.GroupByFieldCount-1 do
SetFieldInt( lSql.GroupByFields[j] );

// Alias from order by fields
for j := 0 to lSql.OrderByFieldCount-1 do
SetFieldInt( lSql.OrderByFields[j] );
end;


procedure SetCriteriaAliasInt;
var
j: integer;
begin
// Alias from criteria fields
for j := 0 to lSql.CriteriaCount-1 do
SetFieldInt(lSql.Criteria[j].Field);
end;


procedure SetLinkAliasInt;
var
j: integer;
begin
// Alias from link fields
for j := 0 to lSql.LinkCount-1 do
begin
SetFieldInt( lSql.Links[j].MasterField );
SetFieldInt( lSql.Links[j].DetailField );
end;
end;


begin
// Only convert when loading from file
if ppReport1.Template.SaveTo = stDatabase then
Exit;

iTot := GetSQLObjectCount(ppReport1);

for i := 0 to iTot-1 do
begin
if GetSQLObject(ppReport1, lSql, i) then
begin
// Only when databasetype not match
if lSql.DatabaseType <> ppDesigner1.DataSettings.DatabaseType then
begin
// Datasettings configuration
lSql.DatabaseName := ppDesigner1.DataSettings.DatabaseName;
lSql.DatabaseType := ppDesigner1.DataSettings.DatabaseType;

case lSql.DatabaseType of
ppTypes.dtInterBase:
begin
// Tables
SetTablesAliasInt;

// Fields, Calcs, Group by, Order by
SetFieldsAliasInt;

// Criteria
SetCriteriaAliasInt;

// Links
SetLinkAliasInt;
end;
end;
// Set SQL
SetSQLObject(ppReport1,lSql, i);
end;
lSql.Free;
end;
end;
end;



function GetSQLObjectCount(aReport: TppReport): integer;
var
lDataModule: TdaDataModule;

begin
Result := 0;

{get the datamodule}
lDataModule := daGetDataModule(aReport);

if (lDataModule <> nil) then
Result := lDataModule.DataViewCount;
end;


function GetSQLObject(aReport: TppReport; var aSQL: TdaSQL; aIndex: integer = 0): Boolean;
var
lDataModule: TdaDataModule;
lDataView: TdaDataView;
begin
aSQL := TdaSQL.Create(nil);

{get the datamodule}
lDataModule := daGetDataModule(aReport);

if (lDataModule <> nil) and (aIndex >= 0) and (aIndex < lDataModule.DataViewCount) then
begin
lDataView := lDataModule.DataViews[aIndex];

if (lDataView <> nil) and (lDataView is TdaQueryDataView) then
aSQL.Assign(TdaQueryDataView(lDataView).SQL);
end;

Result := (aSQL <> nil);
end;


procedure SetSQLObject(aReport: TppReport; aSQL: TdaSQL; aIndex: integer);
var
lDataModule: TdaDataModule;
lDataView: TdaDataView;
begin
{get the datamodule}
lDataModule := daGetDataModule(aReport);

if (lDataModule <> nil) and (aIndex >= 0) and (aIndex < lDataModule.DataViewCount) then
begin
lDataView := lDataModule.DataViews[aIndex];

if (lDataView <> nil) and (lDataView is TdaQueryDataView) then
TdaQueryDataView(lDataView).SQL := aSQL;

end;
end;




Thanks again,
Laercio



Nico Cizik (Digital Metaphors) escreveu:

nicocizik

Hi Laerico,


At first glance, your code seems fine. Is there still an error you are
experiencing?

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,

As I said, until now I have no problems with the code. I'd like to know if there is
anything more that I have to do.
By the way, on the preview code, I just added the following line:

// Datasettings
lSql.DatabaseName := ppDesigner1.DataSettings.DatabaseName;
lSql.DatabaseType := ppDesigner1.DataSettings.DatabaseType;


If there are any more things to do on my code, please reply me.

Thanks a lot,
Laercio



Nico Cizik (Digital Metaphors) escreveu: