/* * The contents of this file are subject to the Initial * Developer's Public License Version 1.0 (the "License"); * you may not use this file except in compliance with the * License. You may obtain a copy of the License at * http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_idpl. * * Software distributed under the License is distributed AS IS, * WITHOUT WARRANTY OF ANY KIND, either express or implied. * See the License for the specific language governing rights * and limitations under the License. * * The Original Code was created by Adriano dos Santos Fernandes * for the Firebird Open Source RDBMS project. * * Copyright (c) 2007 Adriano dos Santos Fernandes <adrianosf@uol.com.br> * and all contributors signed below. * * All Rights Reserved. * Contributor(s): ______________________________________. */ set term !; create or alter procedure rdb$fix_metadata (charset varchar(31) character set ascii) returns (table_name char(31) character set unicode_fss, field_name char(31) character set unicode_fss, name1 char(31) character set unicode_fss, name2 char(31) character set unicode_fss) as declare variable system integer; declare variable field1 char(31) character set unicode_fss; declare variable field2 char(31) character set unicode_fss; declare variable has_records integer; begin for select rf.rdb$relation_name, rf.rdb$field_name, (select 1 from rdb$relation_fields where rdb$relation_name = rf.rdb$relation_name and rdb$field_name = 'RDB$SYSTEM_FLAG'), case rdb$relation_name when 'RDB$CHARACTER_SETS' then 'RDB$CHARACTER_SET_NAME' when 'RDB$COLLATIONS' then 'RDB$COLLATION_NAME' when 'RDB$EXCEPTIONS' then 'RDB$EXCEPTION_NAME' when 'RDB$FIELDS' then 'RDB$FIELD_NAME' when 'RDB$FILTERS' then 'RDB$INPUT_SUB_TYPE' when 'RDB$FUNCTIONS' then 'RDB$FUNCTION_NAME' when 'RDB$GENERATORS' then 'RDB$GENERATOR_NAME' when 'RDB$INDICES' then 'RDB$INDEX_NAME' when 'RDB$PROCEDURES' then 'RDB$PROCEDURE_NAME' when 'RDB$PROCEDURE_PARAMETERS' then 'RDB$PROCEDURE_NAME' when 'RDB$RELATIONS' then 'RDB$RELATION_NAME' when 'RDB$RELATION_FIELDS' then 'RDB$RELATION_NAME' when 'RDB$ROLES' then 'RDB$ROLE_NAME' when 'RDB$TRIGGERS' then 'RDB$TRIGGER_NAME' else NULL end, case rdb$relation_name when 'RDB$FILTERS' then 'RDB$OUTPUT_SUB_TYPE' when 'RDB$PROCEDURE_PARAMETERS' then 'RDB$PARAMETER_NAME' when 'RDB$RELATION_FIELDS' then 'RDB$FIELD_NAME' else NULL end from rdb$relation_fields rf join rdb$fields f on (rf.rdb$field_source = f.rdb$field_name) where f.rdb$field_type = 261 and f.rdb$field_sub_type = 1 and f.rdb$field_name <> 'RDB$SPECIFIC_ATTRIBUTES' and rf.rdb$relation_name starting with 'RDB$' order by rf.rdb$relation_name into :table_name, :field_name, :system, :field1, :field2 do begin name1 = null; name2 = null; if (field1 is null and field2 is null) then begin has_records = null; execute statement 'select first 1 1 from ' || table_name || ' where ' || field_name || ' is not null' || iif(system = 1, ' and coalesce(rdb$system_flag, 0) in (0, 3)', '') into :has_records; if (has_records = 1) then begin suspend; execute statement 'update ' || table_name || ' set ' || field_name || ' = ' || ' cast(cast(' || field_name || ' as blob sub_type text character set none) as ' || ' blob sub_type text character set ' || charset || ') ' || iif(system = 1, 'where coalesce(rdb$system_flag, 0) in (0, 3)', ''); end end else begin for execute statement 'select ' || field1 || ', ' || coalesce(field2, ' null') || ' from ' || table_name || ' where ' || field_name || ' is not null' || iif(system = 1, ' and coalesce(rdb$system_flag, 0) in (0, 3)', '') into :name1, :name2 do begin suspend; execute statement 'update ' || table_name || ' set ' || field_name || ' = ' || ' cast(cast(' || field_name || ' as blob sub_type text character set none) as ' || ' blob sub_type text character set ' || charset || ') ' || ' where ' || field1 || ' = ''' || name1 || '''' || iif(name2 is null, '', ' and ' || field2 || ' = ''' || name2 || ''''); end end end end! commit! create or alter procedure rdb$check_metadata returns (table_name char(31) character set unicode_fss, field_name char(31) character set unicode_fss, name1 char(31) character set unicode_fss, name2 char(31) character set unicode_fss) as begin for select table_name, field_name, name1, name2 from rdb$fix_metadata ('UTF8') into :table_name, :field_name, :name1, :name2 do begin suspend; end end! commit! set term ;!