In the process of moving the data back and forth, using a temporary field of the right character set, you have to drop the original field an then recreate it.

The process of dropping a field is a bit nasty because you have first to comment all the strored procedures and triggers in which this field is used, drop the field, and then uncomment those stored procedures and triggers.

I wonder if the following strategy has any pitfall:

1) Create the temporary field of the right character set
2) Fill it with data, using CAST to OCTETS as suggested by "The Firebird Book"
3) Alter the original field type, changing it to the right character set
4) Fill it with data from the temprorary field
5) Drop the temporary field

Notice that only the 2nd step could raise conversion errors, and you have to manage them there.
In the 4th step no error could occur since both fields are of the same type and character set.

Thanks

 

Pretty clear.
Thank you.

El 23/07/15 a las 05:54, Mark Rotteveel mark@... [firebird-support] escibió:

 

On Wed, 22 Jul 2015 21:09:12 -0300, "Aldo Caruso
aldo.caruso@...
[firebird-support]" <firebird-support@yahoogroups.com> wrote:
> Raffaele,
>
> thanks for your answer.
> The question is what prevents data loss in the first procedure ?
> Suppose the filed in question is
>
> FIRST_NAME VARCHAR(60) CHARACTER SET NONE
>
> so lets create a temporary field
>
> TMP_NAME VARCHAR(60) CHARACATER SET ISO8859_1
>
> and then lets fill it
>
> UPDATE TABLE1 SET TMP_NAME = CAST(FIRST_NAME AS VARCHAR(60)
> CHARACTER SET OCTETS)
>
> Whichever character (from 0 to 255) were in FIRST_NAME would be blindly
> copied to TMP_NAME.
> This has the same effect as assuming that the characters in FIRST_NAME
> where loaded as ISO8859_1, so simply changing FIRST_NAME type from NONE
> to ISO8859_1 would be equivalent.

No, if you alter the character set of an existing column, then the old
data will remain in its old format and format version, and will only be
converted when selected/queried. Leading potentially to conversion errors
at run time (there are a number of bytes in ISO-8859-1 that are not valid
to use). If you create a new column and transfer the data, the validity
check is done at the moment of transfer and you can fix any problems at
that time.

The important difference between these two is the time the conversion
error might occur.

Mark