From derby-user-return-12623-apmail-db-derby-user-archive=db.apache.org@db.apache.org Wed Apr 21 07:50:15 2010 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 33604 invoked from network); 21 Apr 2010 07:50:15 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 21 Apr 2010 07:50:15 -0000 Received: (qmail 68541 invoked by uid 500); 21 Apr 2010 07:50:15 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 68412 invoked by uid 500); 21 Apr 2010 07:50:12 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 68405 invoked by uid 99); 21 Apr 2010 07:50:11 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 21 Apr 2010 07:50:11 +0000 X-ASF-Spam-Status: No, hits=-3.1 required=10.0 tests=AWL,RCVD_IN_DNSWL_MED,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [192.18.6.21] (HELO gmp-eb-inf-1.sun.com) (192.18.6.21) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 21 Apr 2010 07:50:02 +0000 Received: from fe-emea-13.sun.com (gmp-eb-lb-1-fe1.eu.sun.com [192.18.6.7] (may be forged)) by gmp-eb-inf-1.sun.com (8.13.7+Sun/8.12.9) with ESMTP id o3L7ndO1008014 for ; Wed, 21 Apr 2010 07:49:39 GMT MIME-version: 1.0 Content-transfer-encoding: 7BIT Content-type: text/plain; CHARSET=US-ASCII Received: from conversion-daemon.fe-emea-13.sun.com by fe-emea-13.sun.com (Sun Java(tm) System Messaging Server 7u2-7.04 64bit (built Jul 2 2009)) id <0L1700M00UUY2900@fe-emea-13.sun.com> for derby-user@db.apache.org; Wed, 21 Apr 2010 08:49:20 +0100 (BST) Received: from [129.159.112.134] ([unknown] [129.159.112.134]) by fe-emea-13.sun.com (Sun Java(tm) System Messaging Server 7u2-7.04 64bit (built Jul 2 2009)) with ESMTPSA id <0L1700LOSV27QNE0@fe-emea-13.sun.com> for derby-user@db.apache.org; Wed, 21 Apr 2010 08:49:20 +0100 (BST) Date: Wed, 21 Apr 2010 09:49:18 +0200 From: Knut Anders Hatlen Subject: Re: can varchar for bit data size limit be exceeded? In-reply-to: Sender: Knut.Hatlen@Sun.COM To: derby-user@db.apache.org Message-id: <4BCEADFE.8000602@sun.com> References: User-Agent: Mozilla/5.0 (X11; U; SunOS i86pc; en-US; rv:1.9.1.9) Gecko/20100318 Lightning/1.0b1 Thunderbird/3.0.4 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