db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: Truncation error w/ INSERT stmt using blob concatenation...
Date Mon, 17 Jan 2005 20:19:32 GMT
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Army wrote:
>
> I've come across the following behavior with the Derby engine.  It seems
> like a bug to me, but I thought I'd check to make sure it's not "working
> as designed" before filing a JIRA entry.
>
> If I create a table with a large blob column (say 100K), and then I try
> to insert a CONCATENATED blob value into the table, where one of the
> blobs to be concatenated is larger than 32672 bytes, I get a truncation
> error, even though the blob column is large enough to hold the value
> without truncation.
>

[ code, stack trace omitted]

> This is where my uncertainty begins.  It seems to me that, in the above
> reproduction, "the declared length" would be 100K and the length of the
> host variable would be 32700.  In that case, since 32700 < 100K, this
> should be a valid insertion.  But since the variable is rejected, either
> 1) I'm misinterpreting what the "declared length" is, or 2) the declared
> length is not being calculated correctly (it's being set to 32672 when
> it _should_ be 100K).


I think the issue/bug is that the contactenated operator is resulting in
a type of VARCHAR(32762) FOR BIT DATA, not BLOB. Or maybe the type of
the ? is mapped to VARCHAR(32762) FOR BIT DATA. I wonder what the type
of the ? parameter should be with your statement?

> Note that this "checkHostVariable" method is called for Blobs, but is
> NOT called for Clobs.  Thus, if I try to do the exact same thing using
> clobs instead of blobs (with characters instead of bytes, of course),
> everything works fine.

The difference it there to match DB2's JCC driver. Basically
checkHostVariable() is a check in additional to normalization.
Column assignment always goes through normalization to ensure the value
is legitimate. Normalization rules allow truncation of pad characters
(0x20) when setting a value. DB2's JCC driver does not allow any
truncation of binary values from a host variable.

E.g.

'abc  ' is valid for VARCHAR(3)
'abcd ' is not valid for VARCHAR(3)

X'abcdef2020' is valid for VARCHAR(3) FOR BIT DATA (padded with two
bytes) *except* when it is a host variable (set by a parameter marker).

Dan.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFB7B3UIv0S4qsbfuQRAkGzAJsEq8JELSjV4Plgp+Pi84xhvKUhwACgwQxc
HuK44Wz/oZZRIaLBYPMfPpI=
=5Gaz
-----END PGP SIGNATURE-----


Mime
View raw message