Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: neutral (hermes.apache.org: local policy)
Message-ID: <41EC1DD4.1090005@debrunners.com>
Date: Mon, 17 Jan 2005 12:19:32 -0800
From: Daniel John Debrunner <djd@debrunners.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
 rv:1.4.1) Gecko/20031008
MIME-Version: 1.0
To: Derby Development <derby-dev@db.apache.org>
Subject: Re: Truncation error w/ INSERT stmt using blob concatenation...
References: <41EC0242.1080900@golux.com>
In-Reply-To: <41EC0242.1080900@golux.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

-----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-----