Subject Re: [IBO] Newbie - TIB_ColumnMemo, blobs and character sets
Author Helen Borrie
At 10:29 AM 4/04/2003 +0000, you wrote:

>Delphi works generally in the character set used by Windows -
>WIN1252 for most of us.

Only if you have a distribution of Delphi which supports WIN1252 by
default. That is, you bought it through U.S., Australasian or U.K. channels.

> If you define a fully international
>UNICODE_FSS VARCHAR column in the DDL and correctly tell the
>IB_Connection that the client is using WIN1252, FB does a wonderful
>job of converting all the text between the character sets so it
>works just as if the column itself was WIN1252.

Only if the OS is coincidentally delivering only characters that map
correctly to the unicode character codes.

>I wanted to get the same to happen with a BLOB column (because FB
>creates excess network traffic if you use large VARCHARs that
>normally contain small amounts of text). The automatic
>transliteration between character sets does not happen with BLOB
>SUB_TYPE 1 unless the low-level code (deep inside IBO or IBExpress)
>specifically tells FB to use filters when accessing the data.

No, this is not true. As soon as you prepare the *query*, IBO knows the
SQL Data type of the blob column. So, if you defined it in the database as
BLOB SUB_TYPE 1, you are pre-defining the blob filter which is to be
used. (BLOB SUB_TYPES are blob filters!). The character set of the
database column is *not* known.

It doesn't transliterate anything. If you provide text that contains only
characters that are legal unicode characters, then the blob will be stored
correctly. What you can't do is read a text file, or user-input text, that
contains wrongly-mapped characters.

If you are using Firebird (but not InterBase), you can present the blob
text input as a string. This enables you to perform a forced
transliteration of the text to a designated character set.

So, let's say you want to store the WIN1252 string "This is a string" into
your text blob of charset UNICODE_FSS. You can assign the string like this:

insert into aTable (idcol, textblobcol)
values (99, _UNICODE_FSS 'This is a string');

>Of course if the column is the same as the client, no
>transliteration is needed so, provided all clients are on the same
>data set, you would never know there was a problem.

You will get problems if you set up your database and client to a character
set that is not supported by the client and server OS's. Delphi and IBO
can't do anything about that. The API can't do anything about it. Until
the work is actually posted to the server, there is no way to tell whether
the transliteration is valid. As I mentioned before, it is up to the
application to ensure that only valid characters are presented to the server.

>No custom blob filter - the filters are, I'm sure, already there.
>However, to tell it to use them, you have to add parameters when
>opening the blob with isc_open_blob2 or isc_create_blob2.

No, you don't. What if the blob is null? the "space" for it will still
only accept valid characters.

>You should only add the parameters if the blob is SUB_TYPE 1.
>You can find out that it is SUB_TYPE 1 after you have opened the
>blob with isc_open_blob2 or isc_create_blob2 - and I guess that is
>the point at which IBO decides that it should be a TIB_ColumnMemo
>rather than TIB_ColumnBlob.

No, IBO decides it is a TIB_ColumnMemo based on the metadata description,
before it has even brought ANY data across to the client.

>If I have guessed correctly then for IBO to request filters
>automatically it would have to close and re-open the BLOB every time
>it found it was SUB_TYPE 1. That would hardly improve the
>perormance. So, to enable the filters (and hence the VARCHAR-style
>transliteration), there would have to be some way for the user of
>the IBO component to warn it that a particular column would, when
>opened, be sub_type 1 and perhaps specifically request filters.

You're getting really carried away here. If you turn your attention to
BLOB SUB_TYPE 0 - you can store *anything* in it, including text, in
whatever character set you like, or any other mix of bytes. But you can't
declare a character set for BLOB SUB_TYPE 0, because it is an untyped binary.

Back to IBO and SUB_TYPE 1, IBO interprets these as its own 'Memo"
type. Have a look at it in the browser of IB_SQL. It calls SUB_TYPE 0
'Binary'. You will see this in the fields descriptions in IB_SQL, even
though no dataset is open. It is the database definition of the SUB_TYPE
that determines how it is seen by the client. There is no "secret code"
deep in IBO that reads the blob and makes decisions based on what it sees
there...other than to raise an exception if you try to push sub_type 1 into
a TPicture, for example...

>Thanks for the response - I hope I've now explained it.
>Of course it is not impossible to get it to work by writing code low-
>level to access the API, but I fear it may not be possible to
>configure IBO to do the work for me.

Sigh. You don't need to do it and, what's more, you shouldn't, since the
raw isc_ calls are already wrapped in the components.

>Certainly IBExpress has a nil
>blob parameter block in its calls to isc_open_blob2 and
>isc_create_blob2 so I'm sure it can't do it. (I've only downloaded
>the evaluation IBO so I can't look to see whether it is the same in

IBX and IBO won't be the same. IBX provides very sparse support for
Blobs. IBO implements the blob functions to the nth degree. Try to get
past the thinking that you have to re-implement the API calls yourself.