How change a column type in firebird3

Question

Since Firebird 3, I can't modify a column type.

Before I use this kind of update:

update RDB$RELATION_FIELDS set
RDB$FIELD_SOURCE = 'MYTEXT'
where (RDB$FIELD_NAME = 'JXML') and
(RDB$RELATION_NAME = 'XMLTABLE')

because I get ISC error 335545030 ("UPDATE operation is not allowed for system table RDB$RELATION_FIELDS").

Maybe there is another way in Firebird 3?

Answer 1

Firebird 3 no longer allows direct updates to the system tables, as that was a way to potentially corrupt a database. See also System Tables are Now Read-only in the release notes. You will need to use DDL statements to do the modification.

It looks like you want to change the data type of a column to a domain. You will need to use alter table ... alter column ... for that. Specifically you will need to do:

alter table XMLTABLE
    alter column JXML type MYTEXT;

This does come with some restrictions:

Changing the Data Type of a Column: the TYPE Keyword

The keyword TYPE changes the data type of an existing column to another, allowable type. A type change that might result in data loss will be disallowed. As an example, the number of characters in the new type for a CHAR or VARCHAR column cannot be smaller than the existing specification for it.

If the column was declared as an array, no change to its type or its number of dimensions is permitted.

The data type of a column that is involved in a foreign key, primary key or unique constraint cannot be changed at all.

This statement has been available since before Firebird 1 (InterBase 6.0).

Answer 2

Firebird 2.5 manual, chapter Data Definition (DDL) Statement, section TABLE:

 ALTER TABLE tabname ALTER COLUMN colname TYPE typename