Dear group,
how can I solve the following MAPCHAR problem?
CREATE TABLE prods
(
id Integer NOT NULL DEFAULT SERIAL (1),
prod_code Char (10) ASCII,
PRIMARY KEY ("ID")
)
INSERT INTO prods SET id = 1, prod_code = '�12345678'
INSERT INTO prods SET id = 2, prod_code = '�123456789'
SELECT id, prod_code, MAPCHAR(prod_code) as sort FROM prods
=> ERROR "General error;-2010 POS(1) Assignment impossible, char value
too long"
SELECT id, prod_code, MAPCHAR(prod_code) as sort FROM prods WHERE id = 1
=> IS OK
SELECT id, prod_code, MAPCHAR(prod_code) as sort FROM prods WHERE id = 2
=> ERROR "General error;-2010 POS(1) Assignment impossible, char value
too long"
I guess the reason of the error is: In the latter case MAPCHAR changes
'�123456789' (10 chars) into 'oe123456789' (11 chars), thus the result
exceeds the size of the field 'prod_code'. And MAPCHAR unfortunately
does not allow itself a field of longer size.
I hoped that SUBSTR may solve the problem but it doesn't. I replaced
'MAPCHAR(prod_code)' by SUBSTR(MAPCHAR(prod_code), 1, 5) and by
MAPCHAR(SUBSTR(prod_code, 1, 5)) but in both cases the error remained
the same.
The only solution I can think of so far is to count the number of
umlaute (chars like � and �) in the string I'd like to put into the db
and reduce its size not only to the field size but to 'field size' -
'number of umlaute' (so that MAPCHAR has enough space left) before I
insert or update it.
Does anybody know a more sophisticated solution?
Best wishes
Michael
