Привет.
FB2.5.8 CS, charset WIN1251
В некоторых случаях при alter procedure портится описание параметра, если оно (описание) содержит кириллические символы.
Удалось получить воспроизводимый пример путем выжимки метаданных из рабочей БД:
create domain D_01 as integer;
create domain D_02 as varchar(1024);
create domain D_03 as varchar(2048);
create domain D_04 as varchar(32);
create domain D_05 as varchar(100);
create domain D_06 as integer default 0 check ( value is null or value in ( 0, 1 ) );
create domain D_07 as varchar(512);
create domain D_08 as varchar(1023);
create table T01 (
ID D_01 not null,
F01 D_08 not null,
F02 D_08,
F03 D_05,
F04 computed by ( F01 || iif ( coalesce(F02, '') = '', '', ' ' || F02 ) || iif ( coalesce( F03, '') = '', '', ' ' || F03 ) ),
F05 computed by ( trim( F01 ) || coalesce( ' ' || upper( left( trim( F02 ), 1 ) ) || '.', '' ) || coalesce( ' ' || upper( left( trim( F03 ), 1 ) ) || '.', '' ) )
);
create table T02 (
ID D_01 not null,
F01 D_04,
F02 D_05,
F03 D_05,
F04 D_05,
F05 D_02,
F06 D_05
);
create table T03 (
ID D_01 not null,
F01 D_04 not null,
F02 D_01,
F03 D_07,
F04 D_07,
F05 D_05,
F06 D_05,
F07 D_05
);
create table T04 (
ID D_01,
F01 D_04 not null,
F02 D_01,
F03 D_01,
F04 D_06 default 1 not null,
F05 D_06 default 0 not null,
F06 D_06 default 0,
F07 D_01 default 0 not null
);
create table T05 (
ID D_01,
F01 D_08 not null,
F02 D_08
);
set term ^ ;
create or alter procedure P01 (
I_P01 D_05,
I_P02 D_01 = null,
I_P03 D_01 = null,
I_P04 D_06 = 0 )
as
declare variable L_V01 D_01;
declare variable L_V02 D_04;
declare variable L_V03 D_03;
declare variable L_V04 D_05;
declare variable L_V05 D_05;
declare variable L_V06 D_05;
declare variable L_V07 D_07;
declare variable L_V08 D_07;
begin
if ( :I_P01 containing '~AB~' ) then
begin
for
select a.F01,
a.F02,
a.F04 || ' ' || a.F03 as L_V03,
a.F05 as L_V04,
a.F06 as L_V05,
a.F07 as L_V06
from T03 a
join T02 b on ( b.ID = a.F02 )
where not a.F02 is null
into :L_V02,
:L_V01,
:L_V03,
:L_V04,
:L_V05,
:L_V06
do
begin
if ( coalesce( trim( :L_V05 ), '' ) = '' ) then
if ( :L_V06 > '' ) then
L_V05 = :L_V06;
L_V07 = null;
if ( :I_P04 = 1 ) then
begin
for
select b.F02
from T04 a
join T05 b on ( b.ID = a.F02 and a.F05 = 0 and a.F06 = 0 and a.F07 = 0 and a.F04 = 1 )
where a.F01 = :L_V02
order by b.F01
into :L_V07
do
begin
if ( :L_V07 > '' ) then
L_V07 = :L_V07 || '^' || :L_V07;
else
L_V07 = :L_V07;
end
for
select b.F05
from T04 a
join T01 b on ( b.ID = a.F03 and a.F05 = 0 and a.F06 = 0 and a.F07 = 0 and a.F04 = 1 )
where a.F01 = :L_V02
order by b.F04
into :L_V07
do
begin
if (:L_V07 > '') then
L_V07 = :L_V07 || '^' || :L_V07;
else
L_V07 = :L_V07;
end
end
update T02 a
set a.F01 = :L_V02,
a.F02 = coalesce( :L_V04, a.F02 ),
a.F03 = coalesce( :L_V06, a.F03 ),
a.F04 = coalesce( :L_V05, a.F04 ),
a.F05 = coalesce( :L_V03, a.F05 ),
a.F06 = coalesce( :L_V08, a.F06 )
where a.ID = :L_V01;
end
end
end ^
set term ; ^
comment on parameter P01.I_P01 is 'QWR-ЧШЯ';
Сценарий таков:
- создаем пустую БД с чарсетом WIN1251 (размер сраницы 16384 ежличо).
- выполняем скрипт, который под спойлером;
- запрос:
1.
2.
3.
4.
select x.RDB$DESCRIPTION, char_length( x.RDB$DESCRIPTION )
from RDB$PROCEDURE_PARAMETERS x
where x.RDB$PROCEDURE_NAME = 'P01'
and x.RDB$PARAMETER_NAME = 'I_P01'
вернет:
1.
2.
RDB$DESCRIPTION CHAR_LENGTH
QWR-ЧШЯ 7
Теперь выполним alter procedure (текст процедуры не менялся):
set term ^ ;
create or alter procedure P01 (
I_P01 D_05,
I_P02 D_01 = null,
I_P03 D_01 = null,
I_P04 D_06 = 0 )
as
declare variable L_V01 D_01;
declare variable L_V02 D_04;
declare variable L_V03 D_03;
declare variable L_V04 D_05;
declare variable L_V05 D_05;
declare variable L_V06 D_05;
declare variable L_V07 D_07;
declare variable L_V08 D_07;
begin
if ( :I_P01 containing '~AB~' ) then
begin
for
select a.F01,
a.F02,
a.F04 || ' ' || a.F03 as L_V03,
a.F05 as L_V04,
a.F06 as L_V05,
a.F07 as L_V06
from T03 a
join T02 b on ( b.ID = a.F02 )
where not a.F02 is null
into :L_V02,
:L_V01,
:L_V03,
:L_V04,
:L_V05,
:L_V06
do
begin
if ( coalesce( trim( :L_V05 ), '' ) = '' ) then
if ( :L_V06 > '' ) then
L_V05 = :L_V06;
L_V07 = null;
if ( :I_P04 = 1 ) then
begin
for
select b.F02
from T04 a
join T05 b on ( b.ID = a.F02 and a.F05 = 0 and a.F06 = 0 and a.F07 = 0 and a.F04 = 1 )
where a.F01 = :L_V02
order by b.F01
into :L_V07
do
begin
if ( :L_V07 > '' ) then
L_V07 = :L_V07 || '^' || :L_V07;
else
L_V07 = :L_V07;
end
for
select b.F05
from T04 a
join T01 b on ( b.ID = a.F03 and a.F05 = 0 and a.F06 = 0 and a.F07 = 0 and a.F04 = 1 )
where a.F01 = :L_V02
order by b.F04
into :L_V07
do
begin
if (:L_V07 > '') then
L_V07 = :L_V07 || '^' || :L_V07;
else
L_V07 = :L_V07;
end
end
update T02 a
set a.F01 = :L_V02,
a.F02 = coalesce( :L_V04, a.F02 ),
a.F03 = coalesce( :L_V06, a.F03 ),
a.F04 = coalesce( :L_V05, a.F04 ),
a.F05 = coalesce( :L_V03, a.F05 ),
a.F06 = coalesce( :L_V08, a.F06 )
where a.ID = :L_V01;
end
end
end ^
set term ; ^
После этого запрос
1.
2.
3.
4.
select x.RDB$DESCRIPTION, char_length( x.RDB$DESCRIPTION )
from RDB$PROCEDURE_PARAMETERS x
where x.RDB$PROCEDURE_NAME = 'P01'
and x.RDB$PARAMETER_NAME = 'I_P01'
вернет
1.
2.
RDB$DESCRIPTION CHAR_LENGTH
QWR-ЧШЯ 10
Размер RDB$DESCRIPTION увеличился на 3 символа (по количеству кириллических символов - "ЧШЯ").
Запрос
1.
2.
3.
4.
select replace( x.RDB$DESCRIPTION, ascii_char( 0 ), '^' )
from RDB$PROCEDURE_PARAMETERS x
where x.RDB$PROCEDURE_NAME = 'P01'
and x.RDB$PARAMETER_NAME = 'I_P01'
возвращает "QWR-ЧШЯ^^^", т.е. дескрипшн дополнился тремя ASCIIZ.
С уважением, Polesov.
