Subject Re: [ib-support] Re: Database grows rapidly (3 to 770MB) when using BLOB views/UDFs
Author Claudio Valderrama C.
""mircostange"" <mirco@...> wrote in message
> The UDF is written in C (actually C++ with VC). It tried to extract
> the relevant pieces.
> The function is defined as
> __declspec(dllexport) void ProPXMakeBitmap(
> double *scaleFactor,
> IBBLOB ret)
> As you see, it takes a BLOB b, a scaleFactor and also a handle to the
> BLOB to be returned. As I understood, this is how the signature is
> supposed to look like for BLOB UDFs, i.e. there is an additional
> param for the return BLOB.

There's something rotten to my eye here. The declaration you gave is:

Did you extract such declaration with IBConsole or IB's isql? Sorry if I
upset someone, but only the FB isql extracts UDF declarations correctly when
they contain the PARAMETER keyword, otherwise the declaration is mangled.

If the above is REALLY your declaration, it means your UDF has to call the
IB/FB API to create a blob before pushing data on it. Otherwise, I have
strong problems to understand how your UDF is able to work.

I think your true declaration is:
BLOB returns parameter 3

Try extracting metadata with FB's isql and see if it matches.

> Finally, temp memory is freed and the function returns.

I understand makePicture() allocates the needed memory. Are you using malloc
inside it and free in ProPXMakeBitmap? Is make Picture defined inside the
same UDF or is it a separate DLL?

> Anything wrong with this?

Yes, a fundamental problem.

> Do I have to call some function to tell
> Firebird that this BLOB is temporary?

No. Any blob you create or that the engine creates on behalf of you is
temporary by default. It becomes permanent when it's attached to a table's

And here's your problem: when I create a temporary blob, usually it's for
storing it in the db, not only for returning it to the user. The blob uses
database pages. Because you never attach it to a table, it's temporary and
it's marked dead when BLB_close it called (you can't call it inside your
UDF). See this from the API:

isc_close_blob() is used to store a Blob in the database and clean up after
operations. Close any Blob after reading from or writing to it. If, for some
reason, your application does not close a Blob, you can lose data. If your
application might open a Blob without closing it then you should call
isc_cancel_blob() to make sure that the application does not try to open a
Blob that is already open.

InterBase temporarily stores Blob data in the database during create
operations. If, for some reason, you do not, or cannot, close a Blob, the
storage space remains allocated in the database and InterBase does not set
the handle to NULL. Call isc_cancel_blob() to release the temporary storage
in the database, and to set blob_handle to NULL. If you close the Blob in
the normal course of your application processing logic, this step is
unnecessary as InterBase releases system resources on a call to
Note: A call to this function does not produce an error when the handle is
Therefore, it is good practice to call isc_cancel_blob() before creating or
opening a Blob to clean up existing Blob operations.

I don't know if your program uses the API directly, but in your case I would
use isc_cancel_blob(), since you are doing nothing more than reading the
blob. This will mark it as released (and will take care of calling the
internal function BLB_cancel).

I think that isc_cancel_blob() is much better than isc_close_blob() in your
case because the returned blob will be taken by BLB_cancel() internally.
BLB_cancel() does an immediate job of trying to get rid of the db pages
allocated by the temporary blob. Instead, with the isc_close_blob() path you
rely on garbage collection to proceed later... maybe too late for you, after
the db has grown substantially.

If you find in practice that this is not enough for you, you'll have to
rethink the way you're working.

Claudio Valderrama C. - -
Independent developer
Owner of the Interbase® WebRing