db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: SQL Exception: The length resulting from CONCAT operation is greater than 32700.
Date Fri, 30 Jun 2006 14:46:44 GMT
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.



Mime
View raw message