| Subject | Re: [firebird-support] Problem changing CHARACTER SET UNICODE_FSS > ASCII. |
|---|---|
| Author | Helen Borrie |
| Post date | 2004-09-15T02:13:54Z |
At 10:04 PM 14/09/2004 +0200, you wrote:
is already stored there. If you stored unicode_fss data in that column, it
is still unicode_fss and will be uninterpretable as ASCI (because it isn't
ASCII)I. If you didn't get overflow problems with the varchar(14) it is
only that the actual data items stored previously have not exceeded 4
characters. But any data there will be in 3-byte sequences which will be
alphabetti if they are read as though they were ascii.
With the char, gbak is expecting one byte and is encountering 3 - hence the
overflow exception.
There is no wizardry for changing the character set of a column that has
data in it. It's a case of having to create a temp column of the charset
you want and "casting" each value across to this temp column, viz.
update MyTable
set Tempcol = cast (Col1 as varchar(14) CHARACTER SET ASCII)
where Col1 is not null;
After that, drop Col1; commit; then add it again, this time with the
correct charset.
Then,
update MyTable
set Col1 = TempCol;
A word of warning, though: this sort of switch needs to be pretty
carefully thought out for a live database...if you have any characters
stored that don't have an equivalent in the ASCII charset (which is very
limited!!), then you will get both corruption and exceptions. If any of
these data are involved in keys, you can anticipate referential breakages
as well. If this is a possibility, then don't choose ASCII. Choose either
a charset that supports the characters in your data; or go for character
set NONE instead.
./heLen
>Hi,Ah, sweet illusion!
>
>I need to change the CHARACTER SET of columns in a table from
>UNICODE_FSS to ASCII.
>Column 1 is of type VARCHAR(14)
>Column 2 is of type CHAR(1)
>Using IBExpert, when I change the CHARACTER SET of column 1 everything
>seems fine.
>But when I change column 2 I get an error: arithmetic exception, numericChanging the character set of a column doesn't do anything to the data that
>overflow, or string
>truncation, when making a backup of the database.
>
>Am I performing illegal operations here?
is already stored there. If you stored unicode_fss data in that column, it
is still unicode_fss and will be uninterpretable as ASCI (because it isn't
ASCII)I. If you didn't get overflow problems with the varchar(14) it is
only that the actual data items stored previously have not exceeded 4
characters. But any data there will be in 3-byte sequences which will be
alphabetti if they are read as though they were ascii.
With the char, gbak is expecting one byte and is encountering 3 - hence the
overflow exception.
There is no wizardry for changing the character set of a column that has
data in it. It's a case of having to create a temp column of the charset
you want and "casting" each value across to this temp column, viz.
update MyTable
set Tempcol = cast (Col1 as varchar(14) CHARACTER SET ASCII)
where Col1 is not null;
After that, drop Col1; commit; then add it again, this time with the
correct charset.
Then,
update MyTable
set Col1 = TempCol;
A word of warning, though: this sort of switch needs to be pretty
carefully thought out for a live database...if you have any characters
stored that don't have an equivalent in the ASCII charset (which is very
limited!!), then you will get both corruption and exceptions. If any of
these data are involved in keys, you can anticipate referential breakages
as well. If this is a possibility, then don't choose ASCII. Choose either
a charset that supports the characters in your data; or go for character
set NONE instead.
./heLen