Subject Re: [firebird-support] How can I enter a BLOB SUB_TYPE TEXT of more than 32K
Author Helen Borrie
At 07:22 AM 16/06/2006, you wrote:
>I created a test database with 1 table and 2 columns:
>I can insert records into it as long as my testblob is shorter than
>30000 characters.

The actual limit is 32767 bytes, the maximum length of a VARCHAR.

>INSERT INTO TEST (ID, TestBlob) VALUES (1, '<long string>')
>If the string is greater than that, the server seems to crash.

The server should not crash, it should return an overflow
exception. What version of the server and client are you using?

>What am I doing wrong?

Exceeding the byte-length limit for the string type.

Permitting the passing of a string to a text blob was a Firebird
innovation - the DSQL parser at the server side encodes a valid
string as blob using an algorithm similar to the UDF StringToBlob().

It is handy if you are storing relatively short pieces of
text. However, blobs are designed to be of unlimited size. So, if
your applications potentially will store text data longer than the
byte limit of string, then you should pass them as blobs. It's not
something you can do interactively - the API provides a specialised
structure for blobs. Most interface layers have methods for
converting client-side data structures to blobs and passing (or
streaming) a blob object across the interface.

Note also that the limit is in bytes, not characters. So if, for
example, your blob data is unicode or some other multi-byte character
set, the length limit in characters is reduced arithmetically. 32767
bytes of unicode_fss data is 10,922 characters.

Also to note is that passing huge strings as strings is not network-friendly.