SQL Exception: The length resulting from CONCAT operation is greater than 32700.

[Patrick Villacorta <pv...@exist.com>]

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

Re: SQL Exception: The length resulting from CONCAT operation is greater than 32700.

[Stanley Bradbury <St...@gmail.com>]

Patrick Villacorta wrote:
> 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
>
Hi Patrick -
Since you can get this to work in some instances [you wrote: "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."] I am 
wondering what is the difference between the concatenations that are 
successful and the ones that fail?  My impression based on the error you 
are getting when this fails is that the result of the string 
concatenation is being cast to a varchar (limited to 32,372) rather than 
an CLOB.  Is there a varchar or limited string datatype being specified 
or implied in the the handling of the concatenation that fails that is 
not used when the insert succeeds?  Are you mixing the built in 
concatenation syntax ('string1' || 'string2' || ..) with the JDBC escape 
syntax ({fn concat (/CharacterExpression/, /CharacterExpression/) )?

If you would post the snipet of code that is handling the concatenation 
and throwing the Exception along with the complete exception and stack 
trace I might be able to speculate more accurately what is going on.