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

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