Hello,
currently I have a database with ISO8859_1 as character set. This character set
applies to most fields, but some have a different one, so that a table looks
like this:
CREATE TABLE MY_TABLE
(
...,
DESCRIPTION1 BLOB SUB_TYPE 1
SEGMENT SIZE 80,
DESCRIPTION2 BLOB SUB_TYPE 1
SEGMENT SIZE 80 CHARACTER SET ISO8859_5,
SHORT_DESCRIPTION1 VARCHAR( 100),
SHORT_DESCRIPTION2 VARCHAR( 100)
CHARACTER SET ISO8859_5,
SOME_TEXTFIELD VARCHAR( 50)
COLLATE ISO8859_1,
...;
);
We are planning to convert everything to unicode and have created a database
script + a database with UTF8 as character set, so that we can continue with
development and then see, how to convert existing customer databases etc. (this
will probably lead to a separate topic here with a lot of questions ;) )
Unfortunately I don't remember how we had created our current testing-database,
so that a few things need to be clarified.
Another step will be, to change some blob fields from SUB_TYPE 1 to SUB_TYPE 0,
because the component we will be using on the UTF8 version is working with a
binary format for descriptions etc., so that the similar part of the new script
(UTF8 database) looks like this:
CREATE TABLE MY_TABLE
(
...,
DESCRIPTION1 BLOB SUB_TYPE 0
SEGMENT SIZE 80 COLLATE UNICODE,
DESCRIPTION2 BLOB SUB_TYPE 0
SEGMENT SIZE 80 COLLATE UNICODE,
SHORT_DESCRIPTION1 VARCHAR( 100) COLLATE
UNICODE,
SHORT_DESCRIPTION2 VARCHAR( 100) COLLATE
UNICODE,
SOME_TEXTFIELD VARCHAR( 50)
COLLATE UNICODE,
...;
);
IBExpert shows me current UTF8 table as follows:
Field Name | Field Type | Size | Subtype | Charset | Collate |
-------------------------------------------------------------
DESCRIPTION1 | BLOB | 80 | Binary | UTF8 | UTF8 (*) |
DESCRIPTION2 | BLOB | 80 | Binary | UTF8 | UTF8 (*) |
SHORT_DESCRIPTION1 | VARCHAR | 100 | | UTF8 | UNICODE |
SHORT_DESCRIPTION1 | VARCHAR | 100 | | UTF8 | UNICODE |
SOME_TEXTFIELD | VARCHAR | 50 | | UTF8 | UNICODE |
Collate for the first two fields is different in IBExpert than in the script.
I've needed to add some fields to the table and tried this, based on the UTF8
script:
alter table MY_TABLE add ANOTHER_DESCRIPTION BLOB SUB_TYPE 0 SEGMENT SIZE 80
COLLATE UNICODE;
but this fails with an error like "Invalid datatype. Incorrect usage of
Character set or Collate".
In order to work it had to be:
alter table MY_TABLE add ANOTHER_DESCRIPTION BLOB SUB_TYPE 0 SEGMENT SIZE 80;
So the first question is, how is it possible, that IBExpert shows me UTF8 for
the "Collate" column (see above at the (*) symbol).
Because of the error with alter table, I thought that I must have created the
table without collate and just forgott to edit the script.
I thought, that IBXpert automatically shows UTF8 as character set and collate
in such a case, i.e. if nothing was specified for those fields.
But for the newely added field "ANOTHER_DESCRIPTION" IBExpert shows this:
Field Name | Field Type | Size | Subtype | Charset | Collate |
-------------------------------------------------------------
DESCRIPTION1 | BLOB | 80 | Binary | | |
So now I'm somehwat confused and am not sure, what is correct and why there are
such differences and how to use collate properly in that case.
Please put some light into the dark...
Kind regards,
Patrick
