Re: Truncation error w/ INSERT stmt using blob concatenation...

[Mamta Satoor]

 

Daniel John Debrunner wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Army wrote:
[...]
> 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?

Dan.

The issue here is that the ? parameter is getting bound to VARCHAR() FOR BIT DATA with
length of 32672bytes. This binding happens in ConcatenationOperatorNode.bindExpression()
at line number # 162 as copied below
   if (leftOperand.getTypeId().isBitTypeId())
   {
    rightType = TypeId.getBuiltInTypeId(Types.VARBINARY);
   }

The truncation exception at pSt.execute() is really caused by pSt.setBytes(1, bData);
which is trying to put 32700bytes into parameter which can only take 32672 bytes. If the
code snippet from Army is changed to cast ? to BLOB, the concatenation would
succeed.
   PreparedStatement pSt = con.prepareStatement(
            "insert into bt values (cast (x'1010' as blob) || cast(? as blob))");
 

So, as Dan mentioned, what should be the type of the ? parameter? Rather than
getting set to VARCHAR FOR BIT DATA, should it always be set to BLOB, if
the other operand of concatenation operator is bit type?

Thought? Comments?
Mamta