db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: can varchar for bit data size limit be exceeded?
Date Wed, 21 Apr 2010 07:49:18 GMT
On 04/21/10 07:27 AM, George H wrote:
> Hi,
>
> I am using derby 10.5.3.0 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


Mime
View raw message