Hi Everyone,

My database needs to store a number of images (diagrams) for viewing
with an application and printing with Crystal Reports. The best
format to store the images is EMF (Windows vector graphics) in a BLOB
field which allows good scaling for the on-screen display and also
produces relatively small BLOBs (compared to colored BMPs).

However, Crystal Reports is unable to print EMFs directly, so - with
the help of this group - we figured out a way to convert the EMFs
into BMPs 'on demand'.

The solution uses a custom UDF MakeBitmap that takes a BLOB
containing the EMF and produces a BLOB containing the BMP. The
declaration is as follows:

DECLARE EXTERNAL FUNCTION PROPXMAKEBITMAP
BLOB, DOUBLE PRECISION
RETURNS BLOB
ENTRY_POINT 'ProPXMakeBitmap' MODULE_NAME 'ProPXUDF.dll';

The additional 'double precision' is just a scaling factor.

To access the BMPs, we have created a view to convert the EMFs into
BMPs as follows:

CREATE VIEW "SAMPLEATTACHMENTBMP" (
"SAMPLEGROUPID",
"KIND",
"DATA"
) AS

SELECT
sampleGroupID, kind, ProPXMakeBitmap(data,1.5)
FROM
SampleAttachment
WHERE format='EMF'

So far, everything is fine. The solution works, so we have created a
report printing the diagrams.

[THE PROBLEM]
However there is a severe disadvantage. After using the system for a
few hours, we discovered, that the initial database (3MB) had grown
to amazing 770MB although we didn't insert a single record. Backup
and restore shrinks the DB back to 3 MB, but sweep does not do
anything.
I have read about the oldest transaction, etc. so take the following
scenario:
In IBConsole, I tried
select * from sampleattachmentbmp
which gives a result of 10 rows with the BMP images. Afterwards, I
did a commit from the transaction menu. I am the only user, so to my
mind there is no transaction pending.
These steps grew the database from 3 to 180 MB!!

I should add that the system is supposed to run 7x24h unattended, so
this really is a killer bug/feature.

Anyone has any hints?

Mirco