Subject Re: [IBO] BLOB Size Restricted to 64k
Author Helen Borrie
At 11:30 AM 7/02/2006, you wrote:
>AFAIU, Blob size is first restricted by page size.


>IB_SQL reports that my Page Size is 4096.
>However, the IBODatabase Page Size property
>defaults to 1024.

That would kick in you were creating a database. It doesn't have any
effect on an existing database. I think it's time Jason either upped
or eliminated that default.

>Is this just a default in the code, or is it
>actually getting the info from the server?

IBO *can* provide info from the server about this and a raft of other
database info *properties*. Page size is a DPB parameter that's used
only in the context of creating a database. I'm not aware that any
conditional use applies to it, i.e. that for non-existent db's it's
the page_size parameter while for existing ones it's the value from
the database info structure.

>AFAIU, if the default page size of 4Kb is used, this
>_should_ provide the basis for BLOB size => 4 Gb.

Misconception. Page size has nothing to do with the maximum size of
blobs. (Performance and storage usage with blobs *can* be affected
by an inappropriate page size, but that's another story.)

>My BLOBS, declared simply as <BLOB> with no
>SUB_TYPE or SEGMENT SIZE, appear in the source
>description provided by IB_SQL as BLOB( 65535, 0 )
>I don't know how to interpret this.
>Does <65535> refer to SEGMENT SIZE or what?
>It seems coincidental with the BLOB size
>limit I'm running up against.

I don't know why Jason displays it this way but, rest assured, the
numbers are coincidental. A blob has a (now meaningless) segment
size and a (still meaningful) sub_type. Your "plain blobs" (sub_type
0) aren't a smart way to store text blobs, but the default segment
size is 80 bytes, not 65,535. It doesn't make sense to define blobs
with a segment size any more. The server does its own thing with
segmenting blobs. The 80-byte default goes back to the days when
80-bytes was one line of an 80-char ascii text terminal!

>Since my limited knowledge indicates that my Db may
>be created correctly, I hope that my question is
>appropriate here.
>How do I begin to resolve this?

Resolve what? There is no restriction on blob size that you need to
be worried about if you're not planning to store the academic version
of the Encyclopaedia Brittanica in a single blob. It's terabytes, I
don't know the limit.

>Even though I'm storing text in the BLOB, I have always
>left the SUB_TYPE undefined, i.e., = 0.
>Does this actually make a difference?

Yes. Fb/IB have inbuilt blob filters to enable sub_type 1 (a.k.a.
sub_type text) to be treated like a string in some ways. Some string
functions (such as CONTAINING, STARTING WITH and so on) can be
applied to text blobs and they will work as though the blob were a
string (which it's not). In Fb 1.5 and up, you can actually pass a
string literal (though not a string parameter) to a blob and it will
work -- as long as you obey string laws and don't pass strings longer
than 32767 bytes.

IBO takes advantage of the text blob's "stringiness" in e.g. the
fuzzy text search components: they work with text blobs but not with
sub_type 0.

>I could build a new db using SUB_TYPE 1, but
>I think I need to put a hold on my guessing for now
>and get some clarity.

In fact, the database engine stores all blobs (other than arrays,
which are a formatted blob) in exactly the same way. This is one of
the places where you could alter the column to make the required
change without breaking anything: just

alter yourtable
alter yourblob type blob sub_type text;

Another thing - that's better from a design POV - is to create a
domain for your text blobs and then alter the binary blob column type
to your domain:

create domain plaintext
blob sub_type 1 /* text */;

alter yourtable
alter yourblob type plaintext;

Note that you're not changing anything in the data by doing
this. You're just making the text features available to your blobs,
basically telling the engine and your applications that the data in
these blobs is string.