Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Message-ID: <44A24EED.3040902@exist.com>
Date: Wed, 28 Jun 2006 17:42:05 +0800
From: Patrick Villacorta <pvillacorta@exist.com>
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
MIME-Version: 1.0
To: derby-user@db.apache.org
Subject: SQL Exception: The length resulting from CONCAT operation is greater
 than 32700.
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

I'm trying to convert an HSQL script into a Derby SQL script. I have an 
SQL INSERT statement wherein one of the values inserted is around 
200,000 characters long (String inserted into a CLOB). I read from 
http://db.apache.org/derby/docs/dev/ref/rrefstringlimits.html that a 
character constant cannot be more than 32,672 characters long. A 
concatenated character String meanwhile has a limit of 2,147,483,647. So 
what I did was divide the string into chunks of around 30,000 characters 
then use concat "||" to combine them. When I run the script through a 
Java program, the error "SQL Exception: The length resulting from CONCAT 
operation is greater than 32700." occurs. I did this procedure with 
several other long SQL statements in the script (the longest at around 
66,000 characters) and they run without errors. Is my understanding of 
char limits correct? How can I make large SQL statements like this work? 
I'm using derby-10.1.1.0.jar.

Thanks in advance,
Patrick