Subject AW: [IBO] Re: Storing PDF into Firebird Blobs
Author Terry Black
Thanks Herbert, interesting hearing about some of the real world problems and peoples approach to solve them.


IT Manager | Public Health Unit Hornsby | Health Reform Transitional Organisation Northern
Hornsby Hospital, Main Building 1, 36-76 Palmerston Road, Hornsby NSW 2077
Tel. 02 9477 9400 | Fax. 02 9482 1650 | tblac@...

>>> "Herbert Senner" <hsenner@...> 24/05/2012 1:32 am >>>
Terry and Geoff,

one more comment from my side to Geoff's considerations.

I agree totally with Geoff, that the decision on the data's storing location
- whether as
blob in the database or as file in the file system - is that of the basic
concept. In my
experience both have advantages and disadvantages. Geoff has enumerated many

arguments for storing in files. They all are coherent and convincing.

The other side of this approach however is, that the administrative costs
seem to me much bigger.

My situation for example is, that I have to deal with about 60-100 newly
created documents
per day (medical reports, 20-40 kb each). It's not feasible to store them in
one folder (20-30k files
per year!), so I created subfolders based on type of medical examination,
year and month of

create date. To create and then to find again a special file in this
situation is not as easy and reliable than to let retrieve a
database row, to write the blob to a temporary file, to view it and then to
forget it.

In addition I would argue, that with the file system (which I would like to
call client-centric, because the client receives information
only about the path and then has to find it's way to the data by itself in
contrast to the server-centric
dbms mananged solution, where the server delivers the data to the client)
approach there are additional file system
related security issues which do not have to be taken into account with the
database storage.

At the end I think it is a question of a special situation whether to prefer
the one or the other approach.


Von: [] Im Auftrag
von Geoff Worboys
Gesendet: Mittwoch, 23. Mai 2012 03:35
Betreff: Re: AW: [IBO] Re: Storing PDF into Firebird Blobs

Terry Black wrote:
> Hi, thanks Herbert. If I am storing the PDF file contents in
> a blob file then do I need to store the actual PDF files at
> all. In theory I could store the PDF in a blob then delete
> the PDF file. Do you think this is a reasonable approach?

Hi Terry,

I thought I'd throw my 2c in, as I've been working on similar
stuff here in recent times.

The first question you may want to ask yourself is whether the
pdf file needs to be stored in the database at all?

I like to keep my database files as small as practical. While
there is no problem for Firebird in keeping huge files, there
is the ongoing administrative problem of backing and copying
truly huge files. There are ways to deal with files via the
db that don't require you to actually store the file in the db.

I feel this is especially true of files like pdfs, where, it
is generally assumed, the document is never going to change.

Of course there are situations where you definitely will want
the file in the database, but it's worth asking the question.

Related to the previous - is it only ever going to be pdf
files? I created solution that would work for any file and
allows much of the more difficult part to be taken over by
the usual operating system file association stuff.

When it comes to display objects like pdfs, and you have
insisted on having these objects stored in the database, then
I have so far found three possible solutions:

* If you have access to a library that will let you read
the pdfs (and whatever other sorts of documents you want
to keep) directly from the blob, without needing to save
out to file first, then you can avoid many of the
problems you are facing now. Such libraries exist and
could be adapted to Delphi.

* If your clients all have an application that are able
to read from some sort of shared memory then perhaps
you can use that ... I've been studying something along
these lines with OpenOffice/LibreOffice but haven't been
able to get it to work

* And lastly there is the option you appear to be looking
at: write the file out to a temporary location and let
the application look at it. This option leaves you with
the problems of how to remove that file when then app
is finished, to even know when the app is finished. For
this I have also looked at OLE and related technologies,
and while I have made some progress on this front it is
still not smooth. Alternatively you can just put the
files in the temp folder and ask Windows (or whatever)
to delete the files on the next reboot. Not pretty, but
it should work, assuming the client disk space is not
too limited.

So, for one particular requirement, I went back to my first
question and realised that the answer in that instance was No.
The files didn't have to be in the database. Instead I gave
the server access to a central repository - that the clients
also had access to - and that is where the files went. I
wrote UDFs to help me manage these external files in a fairly
secure manner. (The clients only have read-only access, while
the server had full access, clients dropped files on the app
which the server then copied to location in the repository.
The server then gives the client a link to that file and lets
the client open the file directly - no need to copy files
around, no need to delete files later, no need to blow out the
size of the database with data that never changes.)

For a different project I am still looking at interacting with
files more dynamically - files that do actually change, but
that is still a work in progress.

I don't know if any of this helps, just thought I'd share my
own experiences in this area.

Geoff Worboys
Telesis Computing Pty Ltd

[Non-text portions of this message have been removed]

Disclaimer: This message is intended for the addressee named and may contain confidential information. If you are not the intended recipient, please delete it and notify the sender.

Views expressed in this message are those of the individual sender, and are not necessarily the views of the Local Health District or associated entities.