You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by George H <ge...@gmail.com> on 2010/04/21 07:27:42 UTC
can varchar for bit data size limit be exceeded?
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
Thanks.
--
George H
george.dma@gmail.com
Re: can varchar for bit data size limit be exceeded?
Posted by George H <ge...@gmail.com>.
On Wed, Apr 21, 2010 at 4:00 PM, Knut Anders Hatlen <Kn...@sun.com> wrote:
> On 04/21/10 10:17 AM, George H wrote:
>> 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?
>>
>
> I don't think there is a way to do this from IJ. Except, perhaps, if you
> could live with reading the contents of the BLOB from a file with the
> import procedures
> (http://db.apache.org/derby/docs/10.5/tools/ctoolsimport16245.html).
>
> --
> Knut Anders
>
>
OK, I guess I could look into that and see what I can make of it.
Thanks for helping me out.
--
George H
george.dma@gmail.com
Re: can varchar for bit data size limit be exceeded?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
On 04/21/10 10:17 AM, George H wrote:
> 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?
>
I don't think there is a way to do this from IJ. Except, perhaps, if you
could live with reading the contents of the BLOB from a file with the
import procedures
(http://db.apache.org/derby/docs/10.5/tools/ctoolsimport16245.html).
--
Knut Anders
Re: can varchar for bit data size limit be exceeded?
Posted by George H <ge...@gmail.com>.
On Wed, Apr 21, 2010 at 10:49 AM, Knut Anders Hatlen
<Kn...@sun.com> wrote:
> 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
>
Hi,
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
george.dma@gmail.com
Re: can varchar for bit data size limit be exceeded?
Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
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