db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From George H <george....@gmail.com>
Subject Re: can varchar for bit data size limit be exceeded?
Date Wed, 21 Apr 2010 08:17:33 GMT
On Wed, Apr 21, 2010 at 10:49 AM, Knut Anders Hatlen
<Knut.Hatlen@sun.com> wrote:
> On 04/21/10 07:27 AM, George H wrote:
>> Hi,
>> I am using derby and i'm in a situation where sending binary
>> data as a hex string like  INSET INTO MY_TABLE(SOME_COL)
>> VALUES(x'2ede42 ......etc') really benefits me. The only problem is
>> that from what I am reading in the reference guide is that the varchar
>> for bit data data type is limited to a size of 32,672 bytes. Derby
>> also does not let me insert hex strings into CLOB or BLOB columns
>> either which can be much larger.
>> I've tried doing this with MySQL for example and it allows me to send
>> hex strings to a blob column. I'm wondering if there is a way to
>> extend the size limit or to get derby to accept it into a blob. Or is
>> there really nothing I can do about it
> Hi George,
> You can insert hex strings into a BLOB column if you wrap it in a cast:
> ij> create table t (b blob);
> 0 rows inserted/updated/deleted
> ij> insert into t(b) values cast(x'0123456789abcdef' as blob);
> 1 row inserted/updated/deleted
> However, you will still be limited by the maximum size for the varchar
> for bit literal, so you can only use this to insert relatively short
> BLOBs. For longer BLOBs, you'll have to use setBytes(), setBlob() or one
> of the streaming methods on a PreparedStatement.
> I'm afraid there's no way to increase the maximum size of VARCHAR FOR
> BIT DATA. There is a LONG VARCHAR FOR BIT DATA type, though, but that
> only increases the maximum size by 28 bytes to 32700, so it's not of
> much help.
> --
> Knut Anders


Thanks for the extra info. I guess I am still stuck. I have then
another question, how does one insert large BLOBs (ie., 1MB size) into
derby just from the IJ console?  I know we "shouldn't" compare derby
to mysql but with mysql I can insert bytes into a BLOB column by
forming a very very very long SQL insert statement and just place the
bytes in there or put them in hex format. Doesn't derby have a
facility to do something like this? or is a prepared statement the
only way to go about this?

George H

View raw message