Subject Re: [firebird-support] problem converting database to unicode_fss
Author Helen Borrie
At 02:00 AM 2/12/2005 +0000, you wrote:
>For consistency (and to avoid transliteration errors) I need to
>convert a few databases from charset none to unicode_fss.
>I tried using some data pumping tools to do this (specifically ibpump
>and fbcopy) but both give me errors (ibpump has problems with circular
>dependencies in the data structure (even when told to disragard these
>constraints), fbcopy give an error message "XSDA:SetValue incompatible
>What can I do to fix this?

I don't know the fbcopy tool at all, but telling a client application to
"disregard constraints" won't have any effect on the database side. A
constraint violation is a constraint violation. The recommended thing to
do here is to drop the foreign key constraints altogether in the target
database. Just make sure you have a DDL script so that you can restore the
constraints after the pump.

As for the exception you are getting, it's not a database exception. It
looks like a client-local reinterpretation of type mismatch exception, of
which their are numerous kinds. Even an attempt to pass a string of the
wrong character set would throw this error (amongst many other
possibilities, including overlfows). OTOH, if the client program is
looking for errors based on byte count, as a way for testing for correct
length, then a buggy routine there could cause a "false" error. Some
interfaces are not careful to distinguish between the length in characters
and the length in bytes. So the word 'Nonesuch' would have 8 characters in
both charsets, whereas its byte length would be 8 in NONE and 24 in

>Is there a possible manipulation on the tables? Another tool which can
>help me?

Any tool that you might use has to be able to re-cast your strings as
Unicode_fss, e.g. under the hood, to loop through the source table (which
is charset NONE), take the current row value of each character type column
as a parameter and convert the current value in that parameter to
unicode_fss. What it does to get there is an implementation detail; but
the insert statement for the target table would have to be (for any char or
varchar parameter):

insert into a (...., atextcol, ...)
values (?, cast (:atextcol as varchar(n) character set unicode_fss), ?...)

where "n" is the defined length of the text column in characters (not
bytes). It will be OK if you defined atextcol to be longer in the target
table than in the source table, but you will get an overflow if n in the
target table is less than n in the source table.

So it could be that your pumping/copying tools just aren't configured right
for the task or (less probable) they don't support converting from
single-byte character sets to multi-byte.