11

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?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
user3179515
  • 513
  • 1
  • 4
  • 9

2 Answers2

11

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).

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • 1
    For blob is not allowed, what is the best method to change charset on blob text column for exemple ? – user3179515 Jan 16 '18 at 05:45
  • @user3179515 The only real way is to add a new column and update to assign the value of the old column to the new column (with appropriate casts where necessary), drop the old column and rename the new column. – Mark Rotteveel Jan 16 '18 at 10:22
  • @user3179515 suddenly it was said BLOB's are risk of data loss here: http://tracker.firebirdsql.org/browse/CORE-6052 – Arioch 'The Jul 10 '19 at 14:25
3

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

 ALTER TABLE tabname ALTER COLUMN colname TYPE typename
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Arioch 'The
  • 15,799
  • 35
  • 62
  • In Firebird 2.5 there is some thing you can't do. For example changing not null property... In Firebird 3.0 it's Ok – Hugues Van Landeghem Jul 10 '19 at 13:47
  • @HuguesVanLandeghem it is true both ways. If you type your columns using named `DOMAINs` then in FB2 you can change the field type from one `domain` to another by directly modifying a system table. In FB3 system tables were made read-only. However they still do not offer SQL for changing away from `VarChar` `domain` to another in a column definition. I do not remember exactly now, whether the destination `domain` was a longer `VarChar` or a `BLOB SUB_TYPE TEXT`, but it just did not work – Arioch 'The Jul 10 '19 at 14:18