EXECUTE BLOCK parameter character set comes from attachment not declaration
---------------------------------------------------------------------------
Key: CORE-5316
URL: http://tracker.firebirdsql.org/browse/CORE-5316
Project: Firebird Core
Issue Type: Bug
Components: Charsets/Collation
Affects Versions: 2.5.6
Environment: Windows 10, 32bit Firebird
Reporter: Geoff Worboys
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.
--
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
------------------------------------------------------------------------------
What NetFlow Analyzer can do for you? Monitors network bandwidth and traffic
patterns at an interface-level. Reveals which users, apps, and protocols are
consuming the most bandwidth. Provides multi-vendor support for NetFlow,
J-Flow, sFlow and other flows. Make informed decisions using capacity planning
reports.http://sdm.link/zohodev2dev
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
