Provide ablity to change SUBTYPE of blob field
----------------------------------------------
Key: CORE-6195
URL: http://tracker.firebirdsql.org/browse/CORE-6195
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Reporter: Pavel Zotov
Priority: Minor
Suppose we have two tables, both with BLOB field.
First table was created with undefined blob sub-type, second - with blob that
we know forecast that it will store only text data.
Let's create and add multi-line content to these tables:
=======
set blob all;
commit;
recreate table b_undefined_type(b blob);
insert into b_undefined_type values(
'line1
line2
line3'
);
commit;
------------------------------------------
recreate table b_known_as_text(b blob sub_type text);
insert into b_known_as_text values(
'line1
line2
line3'
);
commit;
=======
If we try to extract BLOBs without any casting then all will be fine.
But if we cast blob to varchar (with providing enough length to accomodate its
content) then blob from "b_undefined_type" will be extracted with loosing CRLF
characters:
=======
set list on;
set echo on;
select b from b_known_as_text;
select cast(b as varchar(20)) as blob_to_varchar from b_known_as_text;
select b from b_undefined_type;
select cast(b as varchar(20)) as blob_to_varchar from b_undefined_type;
=======
Output (compare data in lines [1] and [2]):
=======
select b from b_known_as_text;
B 91:0
line1
line2
line3
select cast(b as varchar(20)) as blob_to_varchar from b_known_as_text;
BLOB_TO_VARCHAR line1 <<<<<<<<<<<<<<<<<<<<<<< [ 1 ]
line2
line3
select b from b_undefined_type;
B 90:0
line1
line2
line3
select cast(b as varchar(20)) as blob_to_varchar from b_undefined_type;
BLOB_TO_VARCHAR line1line2line3 <<<<<<<<<<<<< [ 2 ]
=======
Attempt to change blob sub_type to text will FAIL with message:
=======
SQL> alter table b_undefined_type alter b type blob sub_type text;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-ALTER TABLE B_UNDEFINED_TYPE failed
-Cannot change datatype for column B. Changing datatype is not supported for
BLOB or ARRAY columns.
=======
If this table ('b_undefined_type') has many dependencies then it will be
difficult to drop it and recreate again with proper blob sub_type.
Please consider ability to change blob sub-type by ALTER TABLE ALTER <COLUMN>
statement.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
