db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@sourcery.org>
Subject Re: Truncation error w/ INSERT stmt using blob concatenation...
Date Mon, 17 Jan 2005 20:33:11 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<body bgcolor="#ffffff" text="#000000">
I suspect Derby is promoting Blob || Blob to a VARCHAR FOR BIT DATA. I
added the following two lines to SQLBinary.java:<br>
&nbsp;&nbsp;&nbsp; System.out.println("declaredLength = "+declaredLength);<br>
&nbsp;&nbsp;&nbsp; System.out.println("ClassName =
When I run the code provided by Army, I got:<br>
&nbsp;&nbsp;&nbsp; declaredLength = 32672<br>
&nbsp;&nbsp;&nbsp; ClassName = org.apache.derby.iapi.types.SQLVarbit<br>
But, if I insert only a ?, then the results are correct:<br>
&nbsp;&nbsp;&nbsp; declaredLength = 102400<br>
&nbsp;&nbsp;&nbsp; ClassName = org.apache.derby.iapi.types.SQLBlob<br>
So, I suspect the CONCAT is messing it up...<br>
Daniel John Debrunner wrote:<br>
<span style="white-space: pre;">&gt; Army wrote:<br>
&gt; &gt;I've come across the following behavior with the Derby engine.
It seems<br>
&gt; &gt;like a bug to me, but I thought I'd check to make sure it's
not "working<br>
&gt; &gt;as designed" before filing a JIRA entry.<br>
&gt; &gt;If I create a table with a large blob column (say 100K), and
then I try<br>
&gt; &gt;to insert a CONCATENATED blob value into the table, where one
of the<br>
&gt; &gt;blobs to be concatenated is larger than 32672 bytes, I get a
&gt; &gt;error, even though the blob column is large enough to hold the
&gt; &gt;without truncation.<br>
&gt; [ code, stack trace omitted]<br>
&gt; &gt;This is where my uncertainty begins. It seems to me that, in
the above<br>
&gt; &gt;reproduction, "the declared length" would be 100K and the
length of the<br>
&gt; &gt;host variable would be 32700. In that case, since 32700 &lt;
100K, this<br>
&gt; &gt;should be a valid insertion. But since the variable is
rejected, either<br>
&gt; &gt;1) I'm misinterpreting what the "declared length" is, or 2)
the declared<br>
&gt; &gt;length is not being calculated correctly (it's being set to
32672 when<br>
&gt; &gt;it _should_ be 100K).<br>
&gt; I think the issue/bug is that the contactenated operator is
resulting in<br>
&gt; a type of VARCHAR(32762) FOR BIT DATA, not BLOB. Or maybe the type
&gt; the ? is mapped to VARCHAR(32762) FOR BIT DATA. I wonder what the
&gt; of the ? parameter should be with your statement?<br>
&gt; &gt;Note that this "checkHostVariable" method is called for Blobs,
but is<br>
&gt; &gt;NOT called for Clobs. Thus, if I try to do the exact same
thing using<br>
&gt; &gt;clobs instead of blobs (with characters instead of bytes, of
&gt; &gt;everything works fine.<br>
&gt; The difference it there to match DB2's JCC driver. Basically<br>
&gt; checkHostVariable() is a check in additional to normalization.<br>
&gt; Column assignment always goes through normalization to ensure the
&gt; is legitimate. Normalization rules allow truncation of pad
&gt; (0x20) when setting a value. DB2's JCC driver does not allow any<br>
&gt; truncation of binary values from a host variable.<br>
&gt; E.g.<br>
&gt; 'abc ' is valid for VARCHAR(3)<br>
&gt; 'abcd ' is not valid for VARCHAR(3)<br>
&gt; X'abcdef2020' is valid for VARCHAR(3) FOR BIT DATA (padded with two<br>
&gt; bytes) *except* when it is a host variable (set by a parameter
&gt; Dan.</span><br>

View raw message