On Wed, Apr 21, 2010 at 10:49 AM, Knut Anders Hatlen <[email protected]> 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 [email protected]
