Hi

I asked about sorting BLOB (subtype text) columns quite sometime back, and got the following
working query.

SELECT SUBSTRING(MSG_FROM FROM 1 FOR 1000) FROM MESSAGES ORDER BY 1;

(MSG_FROM is a BLOB SUBTYPE TEXT column)
I already have a system running with data in it. Character set was NONE.

Now, I "hacked" the system to convert the existing columns to UNICODE_FSS character set by
performing the following query, and then performed a database backup and restore.
(I found this hack somewhere in newsgroups)

update RDB$DATABASE set RDB$CHARACTER_SET_NAME = 'UNICODE_FSS';
update RDB$FIELDS set RDB$CHARACTER_SET_ID = 3 where RDB$CHARACTER_SET_ID = 0;
update RDB$FUNCTION_ARGUMENTS set RDB$CHARACTER_SET_ID = 3 where RDB$CHARACTER_SET_ID = 0;

I tried these :
SELECT SUBSTRING(MSG_FROM FROM 1 FOR 1000) FROM MESSAGES ORDER BY 1;
SELECT SUBSTRING(MSG_FROM FROM 1 FOR 1000) FROM MESSAGES;

But I got this error:
Conversion error from string "BLOB"
Statement: SELECT SUBSTRING(MSG_FROM FROM 1 FOR 1000) FROM MESSAGES ORDER BY 1

Was my conversion to unicode faulty or it's simply the case that substring cannot work under UNICODE_FSS ?
Are there any quick solutions to this?

Thanks!

Regards
Kenneth


[Non-text portions of this message have been removed]