can not insert more than 32270 charakters in long varchar, clob

• Key: DERBY-102
• Type: Bug
• Status: Closed
• Resolution: Fixed
• Priority: Critical
• Assignee: Unassigned
• Reporter: rainer garbotz
• Component/s: Documentation
• Affects Version/s: 10.0.2.1
• Fix Version/s: 10.1.1.0

Description

I have created an table with

create table egal (
text long varchar
);

Now I try to insert an 38000 charakters long String.

insert into egal values ('
......
');

But there are following error message:

ERROR 54002: A string constant starting with ''
ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd&' is too long.

I tryed the same with CLOB, but the same error occures.

The "Reference Manual" says to LONG VARCHAR:
"The LONG VARCHAR type allows storage of character strings of unlimited length"

Have I missunderstood this, or is this a BUG.

Thanks

Rainer

Comments

--I believe these are the limits currently enforced :-
VARCHAR maximum length 32 672
LONG VARCHAR maximum length 32 700
CLOB maximum length 2 147 483 647
BLOB maximum length 2 147 483 647
and hence the insert of 32800 chars into long varchar fails.

So it seems these didnt make it into the documentation. The following page will need to reflect the correct limit: http://incubator.apache.org/derby/manuals/reference/sqlj137.html#IDX1005

Also lot of the limits live in org.apache.derby.iapi.reference.DB2Limit

--I would think you should be able to insert into a clob. If you could post the test reproduction or SQLExceptions that you are getting for inserting a clob, along with traces in derby.log, that would be great.

[rainer garbotz]

start the database:

java org.apache.derby.tools.ij connect 'jdbc:derby:C:/Temp/derby/db;create=true';

create table with

create table egal (
text CLOB(2M)
);

or

create table egal (
text CLOB
);

and insert the data:

insert into egal values ('
dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd and so on...
');

I don't write alle the "d" charakters here!

All Datatypes

VARCHAR
LONG VARCHAR
CLOB

seems to have the same limitation!

In derby.log File there is no error message. Please try it by yourself.

Regards
Rainer

[Sunitha Kambhampati]

Dan replied to this on the derby developer list. Please check out the link below incase you are not subscribed to the derby developer list:-
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=[e-mail]&msgNo=1354

summary of it is:-
String constants (literals) in the text of a SQL statement are limited in length.
But if you use parameter markers '?' then you should be able to insert values with the below limits (as already stated in previous comment).

VARCHAR maximum length 32 672
LONG VARCHAR maximum length 32 700
CLOB maximum length 2 147 483 647
BLOB maximum length 2 147 483 647

[rainer garbotz]

Hi!

Thanks for your help!

With "PreparedStatement" all seems OK for CLOB.

But anybody should change the documentation! Can I help on this?

Regards

Rainer

[Daniel John Debrunner]

Changed to documentation bug.

[Brenda Reyes]

I fixed this in the contributed DITA source files for the Derby Reference Manual and gave the changed file to Jeff Levitt, who is currently working on the DITA files as well. I wanted to avoid overiding his changes. I ran this change by Sunitha, and she says it looks good, so once the Manual is sent to Jean again by Jeff, please check and close this bug if everything looks good!

[Brenda Reyes]

The files that I modified to fix this were:

rrefsqlj41207.dita - VARCHAR

rrefsqlj15147.dita - LONG VARCHAR

rrefclob.dita - CLOB

rrefblob.dita - BLOB

 

[rainer garbotz]

Hello!

I don`t find the "DITA" Files. I think, I don`t have permissions.

How can I close this Bug?

Regards
Rainer

[Jeff Levitt]

Rainer,

Can you find the DITA files now? The nightly output is available on the Manuals page of the Detby website, just expand the word "DITA" in the nav frame and view the Reference Manual. Here's the LONG VARCHAR page:

http://incubator.apache.org/derby/docs/ref/rrefsqlj15147.html

[Andrew McIntyre]

Sunitha or Rainer,

Could you please review the documentation changes that were made for this issue? If there are no further comments, I will close this by Friday.

[Sunitha Kambhampati]

Andrew, I have reviewed the files that were attached by Brenda (see her earlier comment) and they look good to me.

Sunitha.

[Andrew McIntyre]

Fix in original contributed DITA documentation. Review by Sunitha was positive. closing.

[Brian Bonner]

It seems like IJ should be able to support literals longer than 32770 characters.

Many people use scripts to initalize the databases. the scripts don't normally use prepared statements. Is this limitation related somehow to the the statement cache?

Thanks.

[Brian Bonner]

I just found the reference in the source.

It looks like it's a limit imposed by DB2.

DB2_MAX_CHARACTER_LITERAL_LENGTH = 32672

I found this in org.apache.derby.impl.sql.compile.sqlgrammar.jj

Is this really required?

Thanks.

[Satheesh Bandaram]

Clobs can be as large as upto 2GB. So, even if this limit is relaxed, passing a large clob constant could still hit the maximum size of the query limit. I am not exactly sure if Derby has a limit on maximum size of a query, but it may be present. It is also inefficient to insert large data using character constants. Are there any problems using PreparedStatements and parameters?

If it is required to avoid PreparedStatements, it may be possible to use functions to import clob data at runtime. Clob data will have to be placed in files and a file name could be passed as an argument to this import function.

[Rainer Schmitz]

The problem is that CLOB columns can not be filled from SQL-Scripts. E. g. Liferay Portal Server provides scripts for many databases, but none for Derby because of this problem.
While the solution provided in the comments above might be correct from a technical point of view, it does not solve all use cases.