Subject Re: [firebird-support] Fw: JPEG Images and BLOBS
Author Helen Borrie
At 03:38 PM 28/09/2009, you wrote:
>Good Day,
>I have recently started using Firebird with REALBasic (V2008 R5, on
>Windows XP) - connecting through Firebird ODBC, I'm using Superserver
>version 2.1.2, dialect 3.
>I am trying to store and retrieve JPEG images in BLOB fields, without much
>Whilst doing this in REAL (using SQLite) databases in the past presented
>no difficulties, I can't get it to work in Firebird. Having read Ms
>Borrie's "The Firebird Book" - all thousand pages of it - I'm no wiser.
>She puts lots of effort into describing the use of BLOB Subtype 1 fields
>to store large blocks of text, but, unfortunately, virtually none on using
>BLOB Subtype 0 fields, for storing images or other binary data.
>What I need is some simple guidelines on how to store and retrieve JPEGs
>in Firebird.

The problem here seems to be that you didn't read the bit about how Firebird has absolutely no notion of what to do with *anything* that is stored in a blob. Over the years, it has been taught a few things about manipulating plain text that is stored in a blob, since a lot of the features of plain text behave (more or less) the way VARCHARs do. It has no such native reference for non-text blobs - they are just so much soup.

Besides the appropriate firebird client library, you need two more "layers" to deal with blobs in your applications.

-- One layer will deal with transporting the streams of bytes back and forth (in your case, a suitable ODBC driver). Such layers are actually performing the job of converting that stream of bytes into a structure that fits some model that can work with your application host language. The raw material used by this driver layer (both reading and writing) is the set of BLOB functions in the Firebird API.

-- Another layer, written in (or for) your host language, will do the job of interpreting the complete stream (given the information, for example, that it is a JPEG image) and provide you with language constructs for displaying it, editing it, or whatever you want to do with it.

>Storing the image, as I understand it, is straightforward, same as any
>other data with 'Insert'.

Not as "straightforward" as say varchars or integers. Blobs don't have a predefined size, so it's the job of those intermediate layers to provide the structured information to both read through your JPEG file seqentially and package it for transporting to the database; and to construct appropriate read and write requests for the engine to work with.

>However, the image is stored 'elsewhere' and an
>ID is generated, as a pointer, but I'm not clear on where that pointer is
>put in the record.

The Blob_ID is stored with the rest of the (non-blob) data in your record.

> I have several other fields in the record, such as the
>picture title and subject - presumably I must make a provision for the
>BLOB ID (what data type would that be?).

The engine does that when you define the field. AFAIR, the blob_id's data type is unsigned integer, but you never have to be concerned about that unless you are writing a driver layer yourself.

A blob *may* be stored separately from the record that contains its blob_id; but, sometimes, if there is room, the engine will store the blob's data on the same page as the other data for that record. Again, you don't have to worry about that. The engine knows how to find it, wherever it is.

>Retrieving that image is a mystery to me. It seems to be a 2 step process,
>getting the ID and then using that ID to get the actual image back into an
>RB Picture variable. I don't understand either step!

Actually, the API call can ask for the blob to be returned with the record data, if your driver layer and the host language have the mechanisms to handle that. But, even if/when the API structures are taken care of, your application still has to make that "soup" do whatever a JPEG does.

>Can anyone assist ?

You will very likely get some tips and examples by signing up to the firebird-odbc-devel list - details and links

By the way, please clean up your email footer for posting to the lists. We don't want to see your contact details or your company's privacy notices, thanks.

^ heLen