Firebird 2.5.2 change blob subtype

Question

Is there a posibility to change the SUBTYPE of a BLOB field ?

I have a BLOB with SUBTYPE BINARY an need to change it to SUBTYPE TEXT, because i get some strange Characters into the BLOB and in a BLOB with SUBTYPE TEXT i don't have this Problem

Irradiate

What do you mean with 'strange characters'? The binary subtype is for binary data, not for character data. – Indebtedness

ASCII char 255 and 254. I have another table where the subtype is Text an in this table this chars doesn't exist – Irradiate

ASCII only goes to 127. What characters do you expect for bytes 255 and 254? – Indebtedness

extended ascii goes to 255 ÿ(255) and þ(254) but i don't want this chars, somehow they come into the blob after an insert/update – Irradiate

Extended ASCII is an imprecise name for character sets whose bytes 0-127 are ASCII, and whose bytes 128-255 depend on the character set. Given the characters your mentioning, you either want or use ISO-8859-1 or Windows-1252 (or a similar character set). The problem you are experiencing might be related to the connection character set (the connection character set might map those bytes to a different character if you request the binary data as a string). – Indebtedness

Answer 1

Directly altering the subtype of a blob column is not possible (attempts to do this will give the error "Cannot change datatype for column BLOBCOLUMN. Changing datatype is not supported for BLOB or ARRAY columns.")

You will need to

1. Add a new column with an explicit character set (I am assuming windows 1252 based on your comments)

   ALTER TABLE table_name
     ADD blobcolumn_new BLOB SUB_TYPE TEXT CHARACTER SET WIN1252
   

2. Copy the data from the old column to the new column:

   UPDATE table_name SET blobcolumn_new = blobcolumn
   

3. Drop the old column

   ALTER TABLE table_name
     DROP blobcolumn
   

4. Rename the new column

   ALTER TABLE table_name
     ALTER COLUMN blobcolumn_new TO blobcolumn
   

Indebtedness