Help needed with Template Portability

rbuser

I know this is an old subject, but this is a new angle on it!

Like many other users, we need the ability to use RB templates across a
number of different database types.

When loading a template it appears that RB does not check to see if the
template was saved with the same database type as the currently-connected
database.

Our main headache to date has been with templates created for Paradox being
run on MS SQL in that MS SQL (understandably) doesn't like Paradox's .DB
extensions in the SQL query. This, with your help, we have largely managed
to resolve.

However it now appears that there are a number of other changes in the
template that need to be made to make fully compatible with MS SQL. It's a
bigger issue than we first thought!

I have stumbled on a number of different problems loading templates that
were created on different machines, conencted to different databases or
printers, namely...

1. If connected to a different database type than that specified in the
template, I invariably get an error loading it (even after losing the .DB
table extensions).

2. If the originally specified printer is not available (e.g. running
template on different machine) RB often appears to print endless pages
(presumably page margins or permissible paper sizes etc. are different -
and not all op system dependent printer drivers support the same paper
sizes!) .

If I use the Designer to load, EDIT then save a Paradox template when
connected to MS SQL, RB makes all the necessary modifications and saves the
template in MS SQL format. This DOESN'T happen if I simply load and save - I
MUST make a change to force RB to make the appropriate modifications to the
template.

Q1. How can I trigger RB to completely regenerate the template, as though I
had used the Designer to edit it? I'm very much hoping that there is some
procedure or another in RB I can call to do this.

Q2. Why does RB not do this automatically when key elements of the template
are different to current situation e.g. database type or printer?

Q3. Would it not have been helpful if RB at least checked that the template
was not configured for this database type or printer, and notified the user
accordingly?

Armed with the information you gave me before on updating templates I can
now (if necessary) do the relevant checks but have no desire to have to code
all the changes that will be required, especially knowing that RB already
does everything necessary, if only I can find out how to get at it!

As to the gerenal portability issue for ourselves and other software houses
with vertical market packages that will be run on a variety of databases and
printers, I agree with your earlier suggestion that we create all our
templates to run one one base (presumably our 'most popular' one!) then
check the key elements to see if a modification is required. If this is
likely to aversely effect performance, then maybe it could be enabled or
disabled in the Object Inspector (as also possibly the ability to
'UpdateTemplateOnDBChange' and 'SaveTemplateInOriginalDBFormat'. This would
make the templates fully portable. In the meantime, if you can expose and
publicise how we can use your existing code to do this, it would very much
appreciated!

I gather RB 7 now handles change of printers better - maybe this is all part
of the same overall issue and one that RB will in time address fully?...

Look forward to you pointing me in the right direction!

Regards,

Pete Colson.

digitalmetaphors

The query dataview classes aren't going to be converted automatically, you
have to convert them in code. It sounds like you have this working already.

The database name is saved down in the template. Let's say you are using the
BDE to connect to paradox and also SQL Server. When using Paradox, you can
save down 'Database1' (which is a TDatabase on a form) as the database name
that the data settings should use in DADE. If the template was saved using
this database name for paradox, then it may be that when you load the
template to the MS SQL Server project, if you have 'Database1' on the form
connecting to MS SQL Server data, then the template will try to use that
database component and make it appear that it is converting the dataview.
The dataview classes will not be changed or converted by the report
designer.

We can look into support for conversions to connect to other databases, if
the table and field names match, or new data dictionary which has the
conversion information in it, including the dataview classes, has the
corresponding table and field names predefined in order to convert the
dataviews. You would need to make sure that the databases matched up, or
data aware controls in the template might not get connected to the new
database fields if they don't exist. No, a notification to the user that a
data access conversion is about to be attempted should not be necessary,
because the template could be stored down for any of the defined databases.
When it is loaded, it could be converted to and from for any of the known
database conversion types. Thankyou for the suggestions!


Cheers,

Jim Bennett
Digital Metaphors

rbuser

This doesn't answer my main question!

The Scenario...for our vertical market application (currently over 60
different sites)...

- I create a template to work with a Paradox database - it works fine and we
make available to all users.
- Some clients not happy with Paradox DB performance so we make work with MS
SQL 7 also.
- Those now using

We therefore want one set of templates that will work connected to either
Paradox or MS SQL.

Our application uses a TDatabase component which connects to a BDE alias -
either Paradox or MS SQL. This allows us to leave nearly all of the rest of
the code static e.g. not DB specific. To achieve this, we use the same table
names, field names etc. and we don't use the Paradox .DB table extensions in
SQL queries - the net result is a single software package that works fine
with both Paradox and SQL (and hopefully others in the future).

However, RB is a little more pedantic and DOES use table name extensions
(although in our current scenario, this is not necessary) and makes other
changes dependent on the ppDesigner.DataSettings.DatabaseType and
ppDesigner.DataSettings,SQLType setting.

So, in answer to your comments about the Database Name, they seem irrelevent
in our scenario as we are always using the same database name - it's just
the DatabaseType that is likely to change.

We have now realised that the differences between a template created for
DatabaseTye dtParadox and for DatabaseType of dyMSSQLServer are far more
pronounced than we first thought - there are lots of them!

If I load a template configured for DatabaseType dtParadox, EDIT IT then
save it with DatabaseType dtMSSQLServer, RB regenerates the template
correctly for use with MS SQL. (there are no issues with table names and
data names if we disable ValidateTableNames and ValidateFieldNames).

If I load and save a template without actually EDITing the template in some
way, RB does not appear to regenerate the template.

What I really need at the moment is the ability to fire the regeneration of
the template on certain conditions that I am happy to monitor e.g. if the
template was created for DatabaseType dtParadox but I am now connected to
DatabaseType dtMSSQLServer. CAN I GET AT THIS? Let's face it - it seems daft
for me to have to code all this myself when all the tested and working code
is ALREADY in RB!

Regards your last paragraph, you are on the right track! Our database
structure already fits in with your stated requirements to make this
possible.

My point wasn't that a user notification should be necessary before a data
conversion, it was that some sort of user notification would be useful if
the current DatabaseType is different to thee one stored in the template
(thus indicating a strong likelihood that the report will fail to run!). It
has taken us quuite a while to debug the various error messages only to find
it is because the SQL syntax is wrong for the current DatabaseType - if RB
were to check that the template DatabaseType is the same as the current
DatabaseType before running the template, and warn accordingly, it would
save a lot of people a lot of time trying to find out why their templates
don't work! The user is invariably unaware that the template they are
running was created for a different DatabaseType, so the resultant error
messages are somewhat alarming! If they happen to load it into the Designer
to see what is wrong, then happen to edit something, then save it, it
appears mysteriously to work! It's not exactly intuitive to a user! (And it
wasn't to us, either!)

As previously mentioned, we ideally need to be able to trigger your template
regeneration code...

For portability, the ideal would be to have all templates saved in one
format (say dtParadox) but if the DatabaseType is then changed to
dtMSSQLServer in code (e.g. currently running on MS SQL) then we could get
RB to regenerate the code in memory before running it. From what I have
learnt so far, this would certainly work for our scenario of connection to
either a Paradox or a mirrored MS SQL database, via a single TDatabase
component and I expect other databases too.

We made a point at the outset of designing a system that is easy to maintain
but configurable and portable. We have thus far achieved this in every
respect - apart from report templates!

If you can tell me how to access RB;s template regeneration procedure, I
will be just about home and dry. If I have to manually make all the code
changes myself, I will feel very let down and frustrated - especially
knowing that what I need is JUST out of my grasp!

Look forward tto hearing from you!

Regards,

PC

digitalmetaphors

There is no conversion. When you change the database type in the designer
and save the report, then the new database type is saved down in the
dataview in the template along with the SQLType property value. The
"conversion" you need to do is the same. Extract the dataview's TdaSQL
object after the template is loaded, then change these two properties and
save the report to a template.

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


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Cracked it!

Thanks for pointing me in roughly the right direction....

As you advised, I had to iterate through all the dataviews first to see if
any had a different DatabaseType or SQLType to what is required for the
currentl;y connected database.

It was also necessary to iterate through all the TableNames to ensure
compatible with currently connected database e.g. ensure no .DB extensions
if not dtParadox.

Have also included code for iterating through FieldNames to ensure templates
are modified to cope with any field modifications made to the database since
the template was created.

If any changes are made it finally calls the CreateSQL procedure in daSQL
which regenerates all the SQL (just what I wanted in the first place!).

Have shown the code below for anyone interested... hope it helps someone!

For the record, saving the whole template to a file or memory stream in
ASCII format then doing lots of StringReplace then reloading the template
just isn't a practical option...there's far too much work to be done to
ensure SQL will work with a different DatabaseTye for a start...

Regards,

Pete Colson



unit LPUtilsRB;

{ M o d i f i c a t i o n H i s t o r y :


}

{---------------------------------------------------------------------------
---}
interface

uses
GenPacClasses,
Classes, Dialogs,
ppReport, ppTypes, ppClass,
HRUtilsStr, Utils,
StringListDlg,
daDataView, daQueryDataView, {LP}daDBBDE, daDataModule, daSQL;

procedure LPValidateRBTemplate( AReport: TppReport;
ACurrentDatabaseType: TppDatabaseType;
ACurrentSQLType: TppSQLType);

procedure LPRecreateSQL(lSQL: TdaSQL; lDatabaseType: TppDatabaseType;
lSQLType: TppSQLType);

procedure UpdateFieldNames(lSQL: TdaSQL);

procedure SetTableNames(lSQL: TdaSQL; lDatabaseType: TppDatabaseType);

function ReplaceFieldName(lField: TdaField; lOldValue, lNewValue: string):
Boolean;

{---------------------------------------------------------------------------
---}
implementation

uses
daIDE;


{---------------------------------------------------------------------------
---}

{ call this procedure on TemplateOnLoadEndEvent
- need to set ACurrentDatabaseType and ACurrentSQLType dependent on
currently connected database

- if called whenever a template is loaded, will recreate template SQL if
template was created
for different database type

- can also be used to update any fieldnames if db fieldnames have been
modified
since the template was created
}

procedure LPValidateRBTemplate( AReport: TppReport;
ACurrentDatabaseType: TppDatabaseType;
ACurrentSQLType: TppSQLType);
var
lSQL: TdaSQL;
liIndex: integer;
lDataModule: TdaDataModule;
lDataView: TdaDataView;
begin

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

if (lDataModule <> nil) then
begin
{ iterate through the data modules - there may be more than one }
for liIndex := 0 to lDataModule.DataViewCount - 1 do
begin
lDataView := lDataModule.DataViews[liIndex];
if (lDataView <> nil) and (lDataView is TdaQueryDataView) then
begin
lSQL := TdaQueryDataView(lDataView).SQL;

{ if databasetype in the template is not compatible with current
databasetype }
{ set databasetype to current database then recreate SQL}
if (lSQL.DatabaseType <> ACurrentDatabaseType) { or SQLType <>
ACurrentSQLType? } then
LPRecreateSQL(lSQL, ACurrentDatabaseType, ACurrentSQLType);
end;
end;
end;
end;

{---------------------------------------------------------------------------
---}
procedure LPRecreateSQL(lSQL: TdaSQL; lDatabaseType: TppDatabaseType;
lSQLType: TppSQLType);
begin

lSQL.DatabaseType := lDatabaseType;
lSQL.SQLType := lSQLType;

{ iterate through tablenames and ensure correct format for current
databasetype }
SetTableNames(lSQL, lDatabaseType);

{ iterate through fieldnames and replace any obsolete ones }
UpdateFieldNames(lSQL);

{ recreate SQL to use new tablenames, fieldnames and make compatible
with current database }
lSQL.CreateSQL;

end;

{---------------------------------------------------------------------------
---}
procedure SetTableNames(lSQL: TdaSQL; lDatabaseType: TppDatabaseType);
var
liIndex: integer;
begin

{ ensure .DB extension exists when Paradox but remove if MS SQL }

for liIndex := 0 to lSQL.SelectTableCount - 1 do
begin
if lDatabaseType = dtParadox then
lSQL.SelectTables[liIndex].TableName :=
NoFileExt(lSQL.SelectTables[liIndex].TableName) + '.DB'
else
lSQL.SelectTables[liIndex].TableName :=
NoFileExt(lSQL.SelectTables[liIndex].TableName);
end;

end;

{---------------------------------------------------------------------------
---}
procedure UpdateFieldNames(lSQL: TdaSQL);
var
lField : TdaField;
liIndex: integer;
begin

for liIndex := 0 to lSQL.SelectFieldCount - 1 do
begin
lField := lSQL.SelectFields[liIndex];
ReplaceFieldName(lField, 'LAR_REQ_DANCE_INF', 'LAR_REQ_AREA_1');
ReplaceFieldName(lField, 'LAR_REQ_RESTAURANT', 'LAR_REQ_AREA_2');
ReplaceFieldName(lField, 'LAR_REQ_CLOSE_SEAT', 'LAR_REQ_AREA_3');
ReplaceFieldName(lField, 'LAR_GRANT_DANCE_INF', 'LAR_GRANT_AREA_1');
ReplaceFieldName(lField, 'LAR_GRANT_RESTAURANT', 'LAR_GRANT_AREA_2');
ReplaceFieldName(lField, 'LAR_GRANT_CLOSE_SEAT', 'LAR_GRANT_AREA_3');
end;

end;

{---------------------------------------------------------------------------
---}
function ReplaceFieldName(lField: TdaField; lOldValue, lNewValue: string):
Boolean;
begin

Result := False;

if (lField.FieldName = lOldValue) then
begin
lField.FieldName := lNewValue;
{ maybe display message to make sure report is updated - no need to when
in Designer
as it detects any changes anyway and prompts user to save template }
{ ShowMessage('Field ' + lOldValue + ' has been replaced with ' +
lNewValue
+ #13
+ #13 + 'Please use the Report Designer to save the modified'
+ #13 + 'template to avoid this message appearing the next
time'
+ #13 + 'you use this report template.');}
Result := True;
end;
end;

{---------------------------------------------------------------------------
---}
end.