Adriano dos Santos Fernandes wrote:
> On 22/07/2016 08:38, Geoff Worboys wrote:
>> the other regarding EXECUTE BLOCK parameters:
> You did not put full test case here, so I do not know what
> you mean.
> Of course, parameters are converted to client charset, even
> if they use explicit character sets.
Okay, here is what I mean without the confusion of UDFs or
other externals:
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.
You say "of course, parameters are converted to client charset"
but 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, but I suspect it is silently dropped). As a result
the code inside the block may behave differently to the same
code inside a stored procedure.
I will file a tracker item for this, it is definitely separate
to the UDF problem I started out with. I consider this one to
be quite important because the code you type is not what is
being executed.
--
Geoff Worboys
Telesis Computing Pty Ltd
------------------------------------------------------------------------------
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
