> Yes, it is, for searches (LIKE, STARTING WITH, CONTAINING).

Ah. Ok that makes more sense.

> As I understand it, no. When the blob comes and goes as a blob,
> the database doesn't read what's in the byte stream. But what you
> are doing is taking this stream of bytes and converting it to a
> string (for some unfathomable reason!!) So the illegal characters
> are showing up when you present it as a varchar.

The reason is quite simple. In my clients database, the
varchar(32000) transfers across the line around 3 or 4 times faster
over their VPN connection than the blob field. The blob is subtype 1
(text) so why wouldn't I convert it to a *simple* string and store it
as a varchar. The whole point was to make the entire application
usable over his VPN connection. On the LAN the blobs were not an
issue and it wasn't until the application was used outside the LAN
over the VPN that the speed issue first became noticable.

> Now, there are two unknowns here:
> First, I don't see that you've mentioned anywhere that you have
> ASCII set up as the client-connection character set. If the client
> is character set NONE and the database is ASCII (you can check this
> by viewing RDB$DATABASE) then you have a client-database mismatch.
> If the transliteration of a particular character is valid, the
> engine can perform it automatically, provided it knows the character
> set of the incoming data.

I haven't set the client connection character set to be anything.
Until now, AFAIK, it has never been set. If you read my previous post
I state that the database is set to ASCII, that the blob and varchar
fields are set to character set id 2. Which I've been told is ASCII.

> Secondly, I *believe* the ASCII character set is limited to
> characters from ascii 32 through 126 - the characters you can type

<snip>

I've just done some quick research and I also believe that you are
correct. The standard ASCII character set is a 7 bit character set
so 0 to 127. Obviously character 146 or x92 is not a standard ASCII
character, but an extended ASCII character. This would make sense
with what we've seen thus far.

> You say that the blob data came through by being copy-pasted from
> Word into Access...if I understood that correctly, then the Access
> data would respect the special Windows extended character mappings
> (that you can access in Word via "Insert..Symbol").

I've never mentioned Access. The data is being read from a Firebird
DB BLOB field and trying to be written back into the same Firebird DB
as a VarChar(32k) instead. The front end application uses IBObjects
to do the delphi connectivity. My assumption is that one of the users
of the application copied something from either word or an email and
pasted it into a memo or edit field in the application. I don't
know.

> Now, here's an interesting test. In Word, I created this document

<snip>

> The next step with this would be to create a database with default
> character set ASCII, connect to it with a client also set to ASCII
> and see where you get to with these two strings. The ASCII
> substitute string looks like an apostrophe (ascii 39) so my guess is
> that it would trip over on the first non-escaped apostrophe
> character.

I'm not sure it would. I have a feeling that FB would trip over it
because of the extended ASCII value not because of the
"apostrophe"-ness of it.

> This is interesting from an *academic* point of view but I do wonder
> why you even consider storing a 32Kb varchar in your database...

Your right, it is interesting from an academic point of view. As
I've stated at the top of the email the decision to go with a 32k
varchar rather than stick with the blob fields is purely a speed
solution when running over their VPN connection. Blob fields in that
situation sucked. Plain and simple.

Granted the database design could have been much better, but I wasn't
involved in that. I've only been brought in to try speeding up the
application and clean it up as needed.

Thanks for your insights Helen. I've got a better understanding of
what's possibly wrong.

Ryan.