Andrew Guts wrote:

> Andrew Guts wrote:
>
>> Svein Erling Tysvaer wrote:
>>
>>
>>> Andrew Guts wrote:
>>>
>>>
>>>
>>>> I need to convert some of my database fields into UTF-8. So I wrote and
>>>> run operator by operator the script below:
>>>> --------------------------------------------------------
>>>> alter table cms_wiki
>>>> add u_name varchar(100) character set UTF8,
>>>> add u_title varchar(100) character set UTF8,
>>>> add u_description blob sub_type 1 character set UTF8,
>>>> add u_text blob sub_type 1 character set UTF8;
>>>>
>>>> commit;
>>>>
>>>> update cms_wiki set
>>>> u_name = name, u_title = title, u_text = text, u_description =
>>>> description; /* Here it hangs */
>>>>
>>>>
>>>>
>>> Have you tried splitting the update statement into two, three or four
>>> separate update statements? That way, you could find if it was a problem
>>> with the blobs specifically and if the problem appeared whether you
>>> tried updating one or two blob fields in the same statement.
>>>
>>> I have no idea whether it is possible to convert character sets for text
>>> blobs this way or not, but there's no way updating 52 records should
>>> take more than 30 minutes (well, theoretically if each blob is huge and
>>> you use an extremely slow internet connection and the BDE, I suppose it
>>> could be that slow ;o) and you should at least have been given an error
>>> message. I expect that there is no other transaction active that
>>> accesses this table when you try to do the update?
>>>
>>>
>>>
>>>
> So, I've investigated the exact record executing the statement record by
> record, field by field. It hangs on 7th record (cms_wiki.ID = 15) when
> copying blob from cms_wiki.text into cms_wiki.u_text. I've extracted the
> text from the blob. It is 18701 bytes long. Is it too large? It is
> written in English and do not contain any char outside 7-bit ASCII (as I
> can see). Previous 6 records contain less data in the "text" field but
> do contain some Cyrillic characters.
> Any ideas?
>

Yes! Looks like an implicit hard-coded limitation or a bug. I've done
some experiments with that record and discovered this:
1) update cms_wiki set u_text = substring(text from 1 for 16383) where
id = 15; - works well.
2) update cms_wiki set u_text = substring(text from 1 for 16384) where
id = 15; - fails with the exception: "Cannot transliterate character
between character sets".

This is the text fragment, which crosses 16384th byte:
"... In spite of simplicity, ...".
Character #16384 is a space (ASCII 32) between "of" and "simplicity".

Did I miss something in the documentation? Or it has no relation to the
original problem, because of limitation of the SUBSTRING() itself?

Any comments?

> Regards,
>
> Andrew
>