You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "rainer garbotz (JIRA)" <de...@db.apache.org> on 2004/12/13 11:36:56 UTC

[jira] Created: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

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

         Key: DERBY-102
         URL: http://nagoya.apache.org/jira/browse/DERBY-102
     Project: Derby
        Type: Bug
  Components: SQL  
    Versions: 10.0.2.1    
 Environment: Windows XP/2000
    Reporter: rainer garbotz
    Priority: Critical


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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://nagoya.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "rainer garbotz (JIRA)" <de...@db.apache.org>.
     [ http://nagoya.apache.org/jira/browse/DERBY-102?page=comments#action_56697 ]
     
rainer garbotz commented on DERBY-102:
--------------------------------------

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

> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://nagoya.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: SQL
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://nagoya.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "rainer garbotz (JIRA)" <de...@db.apache.org>.
     [ http://nagoya.apache.org/jira/browse/DERBY-102?page=comments#action_56626 ]
     
rainer garbotz commented on DERBY-102:
--------------------------------------

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

> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://nagoya.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: SQL
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://nagoya.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


Re: [jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by Daniel John Debrunner <dj...@debrunners.com>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Sunitha Kambhampati (JIRA) wrote:

>      [
http://nagoya.apache.org/jira/browse/DERBY-102?page=comments#action_56591 ]
>
> Sunitha Kambhampati commented on DERBY-102:
> -------------------------------------------
>
> --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.

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


The issue is that string constants (literals) in the text of a SQL
statement are limited in length.

If the insert statement used a parameter marker '?' instead of a
constant, then larger values could be passed in, up to the limits
Sunitha lists above.


Dan.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFBvf1oIv0S4qsbfuQRAsGKAJ0dMLFdHnIOk9XFdNQuShzv73xbXACeKRJu
9MfUDiPqhrEfdNsmMGT0f+A=
=RFgU
-----END PGP SIGNATURE-----


[jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "Sunitha Kambhampati (JIRA)" <de...@db.apache.org>.
     [ http://nagoya.apache.org/jira/browse/DERBY-102?page=comments#action_56591 ]
     
Sunitha Kambhampati commented on DERBY-102:
-------------------------------------------

--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. 

> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://nagoya.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: SQL
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://nagoya.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


[jira] Closed: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "Samuel Andrew McIntyre (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-102?page=all ]
     
Samuel Andrew McIntyre closed DERBY-102:
----------------------------------------

     Resolution: Fixed
    Fix Version: 10.1.0.0

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

> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://issues.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: Documentation
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical
>      Fix For: 10.1.0.0

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "Sunitha Kambhampati (JIRA)" <de...@db.apache.org>.
     [ http://nagoya.apache.org/jira/browse/DERBY-102?page=comments#action_56627 ]
     
Sunitha Kambhampati commented on DERBY-102:
-------------------------------------------

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=derby-dev@db.apache.org&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



> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://nagoya.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: SQL
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://nagoya.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "Sunitha Kambhampati (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-102?page=comments#action_66789 ]
     
Sunitha Kambhampati commented on DERBY-102:
-------------------------------------------

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

Sunitha. 

> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://issues.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: Documentation
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


Re: [jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
Jeff Levitt wrote:
> --- Daniel John Debrunner <dj...@debrunners.com> wrote:
> 
> 
>>Brenda Reyes (JIRA) wrote:
>>
>>>     [
>>
> http://issues.apache.org/jira/browse/DERBY-102?page=comments#action_59997
> 
>>]
>>
>>>     
>>>Brenda Reyes commented on DERBY-102:
>>>------------------------------------
>>>
>>>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!
>>
>>Is there any reason changes to the DITA files should
>>not be handled the
>>same way as code changes, through patches on the
>>list? Jeff may not be
>>the only person working on the DITA files.
>>
>>Since no-one can see what you actually changed it's
>>hard to see if your
>>change is correct.
>>
>>Dan.
>>
>>
> 
> 
> I agree.  I am almost done converting the Tools and
> Utilities Guide to DITA (will be done this week), and
> then all the manuals will be in DITA.  I think at that
> point we should remove the zips from the manuals page
> and have them checked in as if they were code.  Can we
> do that? 

Did somebody announce final results for the "[vote] Re-vote for Derby 
docs in DITA" thread?
(See 
http://mail-archives.eu.apache.org/mod_mbox/db-derby-dev/200502.mbox/%3c20050211180100.98392.qmail@web81608.mail.yahoo.com%3e 
)

  -jean



Re: [jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by Jeff Levitt <de...@mylevita.com>.
--- Daniel John Debrunner <dj...@debrunners.com> wrote:

> Brenda Reyes (JIRA) wrote:
> >      [
>
http://issues.apache.org/jira/browse/DERBY-102?page=comments#action_59997
> ]
> >      
> > Brenda Reyes commented on DERBY-102:
> > ------------------------------------
> > 
> > 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!
> 
> Is there any reason changes to the DITA files should
> not be handled the
> same way as code changes, through patches on the
> list? Jeff may not be
> the only person working on the DITA files.
> 
> Since no-one can see what you actually changed it's
> hard to see if your
> change is correct.
> 
> Dan.
> 
> 

I agree.  I am almost done converting the Tools and
Utilities Guide to DITA (will be done this week), and
then all the manuals will be in DITA.  I think at that
point we should remove the zips from the manuals page
and have them checked in as if they were code.  Can we
do that?



Re: [jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by Daniel John Debrunner <dj...@debrunners.com>.
Brenda Reyes (JIRA) wrote:
>      [ http://issues.apache.org/jira/browse/DERBY-102?page=comments#action_59997 ]
>      
> Brenda Reyes commented on DERBY-102:
> ------------------------------------
> 
> 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!

Is there any reason changes to the DITA files should not be handled the
same way as code changes, through patches on the list? Jeff may not be
the only person working on the DITA files.

Since no-one can see what you actually changed it's hard to see if your
change is correct.

Dan.


[jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "Brenda Reyes (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-102?page=comments#action_59997 ]
     
Brenda Reyes commented on DERBY-102:
------------------------------------

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!

> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://issues.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: Documentation
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "Jeff Levitt (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-102?page=comments#action_64362 ]
     
Jeff Levitt commented on DERBY-102:
-----------------------------------

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

> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://issues.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: Documentation
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "Brian Bonner (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-102?page=comments#action_12319029 ] 

Brian Bonner commented on DERBY-102:
------------------------------------

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.

> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://issues.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: Documentation
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical
>      Fix For: 10.1.1.0

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "Samuel Andrew McIntyre (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-102?page=comments#action_66777 ]
     
Samuel Andrew McIntyre commented on DERBY-102:
----------------------------------------------

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.

> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://issues.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: Documentation
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "Brenda Reyes (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-102?page=comments#action_60157 ]
     
Brenda Reyes commented on DERBY-102:
------------------------------------

The files that I modified to fix this were:

rrefsqlj41207.dita - VARCHAR 

rrefsqlj15147.dita - LONG VARCHAR

rrefclob.dita - CLOB

rrefblob.dita - BLOB

 

> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://issues.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: Documentation
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "Rainer Schmitz (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-102?page=comments#action_12363264 ] 

Rainer Schmitz commented on DERBY-102:
--------------------------------------

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.


> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://issues.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: Documentation
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical
>      Fix For: 10.1.1.0

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "Brian Bonner (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-102?page=comments#action_12319033 ] 

Brian Bonner commented on DERBY-102:
------------------------------------

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.



> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://issues.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: Documentation
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical
>      Fix For: 10.1.1.0

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


[jira] Updated: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "Daniel John Debrunner (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-102?page=history ]

Daniel John Debrunner updated DERBY-102:
----------------------------------------

    Component: Documentation
                   (was: SQL)

Changed to documentation bug.

> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://issues.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: Documentation
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "rainer garbotz (JIRA)" <de...@db.apache.org>.
     [ http://issues.apache.org/jira/browse/DERBY-102?page=comments#action_60357 ]
     
rainer garbotz commented on DERBY-102:
--------------------------------------

Hello!

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

How can I close this Bug?

Regards
Rainer

> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://issues.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: Documentation
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira


[jira] Commented: (DERBY-102) can not insert more than 32270 charakters in long varchar, clob

Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
    [ http://issues.apache.org/jira/browse/DERBY-102?page=comments#action_12319065 ] 

Satheesh Bandaram commented on DERBY-102:
-----------------------------------------

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.


> can not insert more than 32270 charakters in long varchar, clob
> ---------------------------------------------------------------
>
>          Key: DERBY-102
>          URL: http://issues.apache.org/jira/browse/DERBY-102
>      Project: Derby
>         Type: Bug
>   Components: Documentation
>     Versions: 10.0.2.1
>  Environment: Windows XP/2000
>     Reporter: rainer garbotz
>     Priority: Critical
>      Fix For: 10.1.1.0

>
> 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

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira