RB template portability issue

rbuser

I'm trying to get a MS SQL Server verison of our software working.

I have noticed that all report templates created on the Paradox version of
our software stores the table names as tablename.db.

Needless to say, this means that reports created in the Paradox version are
not useable in the SQL version, even though the core tablenames and
fieldnames are all the same - it;s just the .DB fil extension in the
tablename that kills it!!

I'm sure there's an easy fix for this - can you please point me in the right
direction?

P.S. May I suggest changing th default fieldnames for the Report Expolorer
table RBITEM - SIZE caused us a few problems on MS SQL Server as it would
appear to be a reserved word!

digitalmetaphors

No, the templates will not automatically convert from Paradox to SQL Server.
The data access definition is saved inside the template for the dataviews.
You'll have to parse the template and convert the object definitions, just
as if you were opening up a dfm as text and manipulating it. Same idea.
Here is a demo which can open a binary template as ASCII text:

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


For example, you need to change the TdaBDESession class name to
TdaADOSession to convert a BDE query dataview to work with ADO, You'll have
to change all of the other classnames specific to the BDE query dataview
classes to the ADO DADE plugin equivalent. All of the daADO classes can be
found in the DADE ADO plugin called daADO.pas. The BDE plugin classes can
be found in daDBBDE.pas.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Oh ... will have a look at sample you mentioned ...

For better or worse, we are not accessing MS SQL with ADO but with BDE
instead (although will probably change to dbExpress when available) e.g.
the same...

So, as it stands, I won't presumably need to change the SessionType,
although I am setting in code the DatabaseType to dtParadox or dtMSSQLServer
as appropriate.

I was thinking how nice it would be if RB stored the base information in the
template which it could then use to generate a SQL query at runtime
appropriate for the DatabaseType it is connecting to e.g. if Paradox then
tablename = tablename + '.DB.', otherwise no extension (not as though one
NEEDS the extension for Paradox in a SQL query anyway!).

This would certainly make the template far more portable in my case - not
sure about other situations though.!

Would life be better if I used ppDataDictionary? Would it use the alias
names or the raw tablenames?

Is there any way of telling RB not to use the file extensions in with
Paradox? Is there any documentation that explains which properties are used
to determine the SQL code created (there doesn't seem to be much in the Help
system for DataSettings)..

As I've only just started createing completely stand-alone templates, I'd
much rather have a way of creating 'generic' templates if possible ...

Any thoughts?

rbuser

Have just found that I can overcome the problem by amending daDBBDE's
GetTableNames procedure to NOT retrieve filename extensions ...

How do I now go about overriding daDBBDE's GetTableNames procedure in code?

Unless you have any other suggestions e.g. to my other email!!

Regards,

Pete

digitalmetaphors

You can create your own DADE plug-in, modeled after daDBBDE.pas, which can
work this way.

The data dictionary won't help in this case, but it is useful when you want
to control the joins which can be created, which fields and tables are
visible, and what the table names and field names appear as (aliases) as an
end user of the designer. The field name and field alias are saved in the
template for each field that is defined in the dataview.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Thanks for this ...

But how?

Have saved my amended version of daDBBDE.pas as LPdaDBBDE.pas and then
changed all references of daDBBDE in the Uses clause of all my units to
LPdaDBBDE .

It SEEMS to work..

Is it really that easy?

Still suggest you amend yours in the future to allow us to decide whether or
not to use file extensions - it appears to be the only thing stopping us
from using a template created with Paradox or DBase DB's on other databases
such as MS SQL... portability would surely be a big benefit?

Otherwise, RB still best add-on I have used - by a mile!!

Regards,

Pete Colson

rbuser

> I was thinking how nice it would be if RB stored the base information in
the

I'd like this as well. We support DBISAM, Oracle and SQLServer with our
app.server and are facing the exact same problem!

Cheers,
Lars

digitalmetaphors

Probably the best solution for you is to save the reports down always in one
format, such as SQL server using ADO. Then write converters to convert the
report templale on the fly to another database type. This way you can
change databases but only haveone set of templates. There is a utility that
you can use to load the templates from binary format to ascii so that you
can load them into a string list for easy text manipulation. Our FTP server
is down, so I'll post the code here:

{LOAD}
procedure TForm1.Button1Click(Sender: TObject);
var
lsFileName: String;
lFileStream: TFileStream;
lTextStream: TMemoryStream;
begin

Button1.Enabled := False;
Button2.Enabled := True;

lsFileName := ExtractFilePath(ParamStr(0)) + 'example.rtm';

lFileStream := TFileStream.Create(lsFileName, fmOpenRead);
lTextStream := TMemoryStream.Create;

try

lFileStream.Position := 0;

ObjectBinaryToText(lFileStream, lTextStream);

lTextStream.Position := 0;

Memo1.Lines.LoadFromStream(lTextStream);

finally

lFileStream.Free;
lTextStream.Free;

end;

end;

{SAVE}
procedure TForm1.Button2Click(Sender: TObject);
var
lsFileName: String;
lFileStream: TFileStream;
lTextStream: TMemoryStream;
lWriter: TWriter;
begin

Button2.Enabled := False;

lsFileName := ExtractFilePath(ParamStr(0)) + 'example.rtm';
DeleteFile(lsFileName);

lFileStream := TFileStream.Create(lsFileName, fmCreate);
lTextStream := TMemoryStream.Create;

try

Memo1.Lines.SaveToStream(lTextStream);

lTextStream.Position := 0;

ObjectTextToBinary(lTextStream, lFileStream);

lWriter := TWriter.Create(lFileStream, 1024);
try
lWriter.WriteListEnd;
finally
lWriter.Free;
end;

lFileStream.Position := 0;

finally

lFileStream.Free;
lTextStream.Free;

end;

end;


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Nearly there!!

Have written a function to do pretty much as you suggested - it does a
simple text search and replace and seems to do the trick...

The only thing I am now stuck on is how to get it to run when a user loads a
report in Report Explorer. Although I have prevented new reports from
storing the file extension, I still ideally need to check existing user
reports to ensure 'backward compatibility'.

I have tried the ppDesignerCustomOpenDoc event but it doesn't seem to fire
...

What event can I use to ensure that ALL templates are checked when loading
(e.g. regardless of whether from file or database, and regardless of opening
to print, preview or design)?

Your help would (as always!) be much appreciated...

Regards,

Pete Colson

digitalmetaphors

Use the template OnLoadEnd event to run each template through your "template
cleaner." Check out the Templates thread in the Tech-Tips newsgroup for an
article on using template events.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Feedback ....

Have tried that (didn't realise that the ppReport component I created in my
form was automatically used by the Report Explorer component ...).

As there appears to be no way of directly adressing the template data, am
using the Template.SaveToStream method to gain access to the ASCII text,
making my amendments, then using the Template.LoadFromStream method to
update the template. I was also doing a Template.Save to ensure the amended
template is saved.

My problems / comments now are (specifically for using this in my
ReportExplorer form):

1. As the Template.SaveToType is set to stDatabase, the
Template.SaveToStream triggers the TppReportExplorer.ReportSaveEndEvent
which fails because qryItem is not in edit mode. Have worked around this by
setting Template.SaveToType to stFile, then reverting afterwards to
stDatabase.

How come this event fires when only 'saving' to a memory stream? Surely it
should not update the 'database'?

2. Having made my changes, I then use the Template.LoadFromStream method to
update the template in memory.
Needless to say this fires my new OnLoadEnd event again and goes around in a
loop ...

Have worked around this by temporarily unhooking the OnLoadEnd event.


Otherwise it now seems to work OK...

Am thinking it would have been better to have a housekeeping routine to do
this as a one-off instead (as you may have suggested earlier!)

Thanks for all your help on this, it's much appreciated ...

digitalmetaphors

Try calling Template.SaveToDatabase instead of SaveToStream.

You can access the template data directly, by reading it into a TStringlist
without having to load the template into a report object first. The code I
posted before shows how to do this. You can get the template from a blob
field using a TBLOBStream instead of getting it from a TFileStream.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Point taken ...

But then I'd have to cover both loading from database as well as from file,
make my changes then save them and re-load into the report object prior to
moving on with the action the user has chosen (e.g. design, print or
preview)... seems hard work when the template is already in memory ...

To manipulate the text I need to get it into a memory stream to be able to
save it to a StringList. Hence using SaveToStream.

Once manupulated, if the template is modified I then LoadFromStream and Save
to
a) ensure the report they have just loaded runs with the updated template
and
b) to write the changes back to file / database.

The ONLY problem with this is that, when using SaveToStream, stSaveToType is
still set to stDatabase and therefore RB tries to update the template's
'date and time updated' in rbItem. This fails because rbItem is not in Edit
mode.

The problem is only caused by the generic Save procedure trying to update
the rbItem record because it thinks it's saving to database, when it
isn't...e.g. it's looking at the stSaveToType REGARDLESS of which procedure
(Save, SaveToStream, SaveToDatabase, SaveToFile) was called ...

My view is that SaveToStream should NOT cause rbItem to be updated... it's
misleading to say the least....only saving to database should update rbItem

Perhaps there should be a third stSaveToType, stMemoryStream, to rectify
this and avoid any ambiguity?

As previously mentioned, I have managed to get it all working reasonably
well, I merely wished to point out a couple of problems I had on the way,
one of which I consider a design flaw, which you can't be expected to
rectify if nobody tells you about it!

Regards,

Pete Colson

digitalmetaphors

Thanks for showing us your approach and the case that you ran into. We can
look into adding another option for saving to a memory stream, instead of
database or file as the save to type.

Cheers,

Jim Bennett
Digital Metaphors