description: EXECUTE BLOCK (
"Param1" VARCHAR(80) CHARACTER SET WIN1252 = :"Param1"
) RETURNS (
"Result" VARCHAR(255) CHARACTER SET UTF8
) AS
DECLARE TmpUtf8 VARCHAR(20) CHARACTER SET UTF8;
BEGIN
TmpUtf8 = _utf8 x'C690';
"Result" = TmpUtf8;
SUSPEND;
"Param1" = TmpUtf8; -- <<< Expect Error Here
"Result" = "Param1";
SUSPEND;
END

If I use WIN1252 for the connection character set then the block fails at the marked line, as expected (although it gives an overflow/truncation error rather than a transliteration failure, not sure why).

However, if I use UTF8 as the connection character set then the block completes successfully.

Ergo: the character set of the parameters to an EXECUTE BLOCK come from the attachment character set and NOT from the declared character set.

This is both far from obvious and contrary to the documentation. From the v2.5 Language Reference Update:

" Firebird 2.1 and up allow the use of domains instead of SQL datatypes when declaring input/output parameters and local variables. With the "TYPE OF" modifier only the domain's type is used, not its NOT NULL setting, CHECK constraint and/or default value. If the domain is of a text type, its character set and collation are always included. "

and, under the heading of "TYPE OF COLUMN in parameter and variable declarations"

" Only the type itself is used; in the case of string types, this includes the character set and the collation."

The reason why I say it is far from obvious is that execute block statements look so much like stored procedures, but in this respect their behaviour varies.

With a stored procedure the transliteration occurs with the input of the the client data into the parameter - the parameter itself remains with the type (charset and collation) that it was declared with.

But with the execute block the parameter type has been silently changed to the attachment character set (haven't studied the collation). As a result the code inside the block may behave differently to the same code inside a stored procedure. This is potentially very confusing and not predictable (the same block may behave differently depending on how the client connects).

I would prefer that the engine was fixed to respect the declared character set (whether declared directly or via TYPE OF references). If that is not possible then the documentation needs to be changed to highlight that EXECUTE BLOCK will change the character set (and collation?), and so the code may not behave the same as the equivalent stored procedure.

=>

EXECUTE BLOCK \(
    "Param1" VARCHAR\(80\) CHARACTER SET WIN1252 = ?
\) RETURNS \(
    "Result" VARCHAR\(255\) CHARACTER SET UTF8
\) AS
    DECLARE TmpUtf8 VARCHAR\(20\) CHARACTER SET UTF8;
BEGIN
    TmpUtf8 = \_utf8 x'C690';
    "Result" = TmpUtf8;
    SUSPEND;
    "Param1" = TmpUtf8;   \-\- <<< Expect Error Here
    "Result" = "Param1";
    SUSPEND;
END

If I use WIN1252 for the connection character set then the block fails at the marked line, as expected (although it gives an overflow/truncation error rather than a transliteration failure, not sure why).

However, if I use UTF8 as the connection character set then the block completes successfully.

Ergo: the character set of the parameters to an EXECUTE BLOCK come from the attachment character set and NOT from the declared character set.

This is both far from obvious and contrary to the documentation. From the v2.5 Language Reference Update:

" Firebird 2.1 and up allow the use of domains instead of SQL datatypes when declaring input/output parameters and local variables. With the "TYPE OF" modifier only the domain's type is used, not its NOT NULL setting, CHECK constraint and/or default value. If the domain is of a text type, its character set and collation are always included. "

and, under the heading of "TYPE OF COLUMN in parameter and variable declarations"

" Only the type itself is used; in the case of string types, this includes the character set and the collation."

The reason why I say it is far from obvious is that execute block statements look so much like stored procedures, but in this respect their behaviour varies.

With a stored procedure the transliteration occurs with the input of the the client data into the parameter - the parameter itself remains with the type (charset and collation) that it was declared with.

But with the execute block the parameter type has been silently changed to the attachment character set (haven't studied the collation). As a result the code inside the block may behave differently to the same code inside a stored procedure. This is potentially very confusing and not predictable (the same block may behave differently depending on how the client connects).

I would prefer that the engine was fixed to respect the declared character set (whether declared directly or via TYPE OF references). If that is not possible then the documentation needs to be changed to highlight that EXECUTE BLOCK will change the character set (and collation?), and so the code may not behave the same as the equivalent stored procedure.