You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by tom_ <to...@web.de> on 2008/11/24 21:31:38 UTC
varchar length
When defining a datatype with varchar(128) and providing an input with more
than 128 characters the value is not inserted in the database. There is an
error message "error when trying to cut to 128 characters" (translated from
german).
I could encrease the maximum length but when there are a lot of values with
shorter length and only very few with longer length than 128 this would
decrease the speed of search operations ...
--
View this message in context: http://www.nabble.com/varchar-length-tp20669171p20669171.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: varchar length
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Kristian Waagan wrote (2008-11-25 13:36:54):
> tom_ wrote:
>> Hello Kristian,
>>
>> this is a good idea, truncating by java before writing by SQL. Yes, varchar
>> removes trailing blanks, though it would help if it would cut larger values
>> instead of not writing them. If you have a look at the error message it
>> shows that he tries to cut the value to the limit but has an exception.
>>
>
> Yes, it is trying to cut, but is not allowed to do so because the data
> to be cut consists on non-blanks.
> There is no support for allowing Derby to truncate "real data". You
> could add a feature request to Jira[1], but since there are relatively
> simple work arounds available, I don't know if it will get much traction
> in the community.
Another thing, I don't think increasing the size of the VARCAHR in Derby
should affect Derby performance.
--
Bernt Marius Johnsen, Staff Engineer
Database Technology Group, Sun Microsystems, Trondheim, Norway
Re: varchar length
Posted by Kristian Waagan <Kr...@Sun.COM>.
tom_ wrote:
> Hello Kristian,
>
> this is a good idea, truncating by java before writing by SQL. Yes, varchar
> removes trailing blanks, though it would help if it would cut larger values
> instead of not writing them. If you have a look at the error message it
> shows that he tries to cut the value to the limit but has an exception.
>
Yes, it is trying to cut, but is not allowed to do so because the data
to be cut consists on non-blanks.
There is no support for allowing Derby to truncate "real data". You
could add a feature request to Jira[1], but since there are relatively
simple work arounds available, I don't know if it will get much traction
in the community.
cheers,
--
Kristian
[1] https://issues.apache.org/jira/browse/DERBY
> Thank you for helping
> Tom
>
>
> Kristian Waagan-4 wrote:
>
>> tom_ wrote:
>>
>>> When defining a datatype with varchar(128) and providing an input with
>>> more
>>> than 128 characters the value is not inserted in the database. There is
>>> an
>>> error message "error when trying to cut to 128 characters" (translated
>>> from
>>> german).
>>>
>>>
>> Hello Tom,
>>
>> Only trailing white space will be truncated by Derby. I *think* this is
>> according to the SQL standard, but I haven't checked this (anyone?).
>>
>> Two options would be to either extract a substring from the value in
>> Java (i.e. before you call PreparedStatement.setString), or rely on
>> exception handling to detect and handle the problem.
>>
>>
>> regards,
>> --
>> Kristian
>>
>>
>>> I could encrease the maximum length but when there are a lot of values
>>> with
>>> shorter length and only very few with longer length than 128 this would
>>> decrease the speed of search operations ...
>>>
>>>
>>
>>
>
>
Re: varchar length
Posted by tom_ <to...@web.de>.
Hello Kristian,
this is a good idea, truncating by java before writing by SQL. Yes, varchar
removes trailing blanks, though it would help if it would cut larger values
instead of not writing them. If you have a look at the error message it
shows that he tries to cut the value to the limit but has an exception.
Thank you for helping
Tom
Kristian Waagan-4 wrote:
>
> tom_ wrote:
>> When defining a datatype with varchar(128) and providing an input with
>> more
>> than 128 characters the value is not inserted in the database. There is
>> an
>> error message "error when trying to cut to 128 characters" (translated
>> from
>> german).
>>
>
> Hello Tom,
>
> Only trailing white space will be truncated by Derby. I *think* this is
> according to the SQL standard, but I haven't checked this (anyone?).
>
> Two options would be to either extract a substring from the value in
> Java (i.e. before you call PreparedStatement.setString), or rely on
> exception handling to detect and handle the problem.
>
>
> regards,
> --
> Kristian
>
>> I could encrease the maximum length but when there are a lot of values
>> with
>> shorter length and only very few with longer length than 128 this would
>> decrease the speed of search operations ...
>>
>
>
>
--
View this message in context: http://www.nabble.com/varchar-length-tp20669171p20680266.html
Sent from the Apache Derby Users mailing list archive at Nabble.com.
Re: varchar length
Posted by Kristian Waagan <Kr...@Sun.COM>.
tom_ wrote:
> When defining a datatype with varchar(128) and providing an input with more
> than 128 characters the value is not inserted in the database. There is an
> error message "error when trying to cut to 128 characters" (translated from
> german).
>
Hello Tom,
Only trailing white space will be truncated by Derby. I *think* this is
according to the SQL standard, but I haven't checked this (anyone?).
Two options would be to either extract a substring from the value in
Java (i.e. before you call PreparedStatement.setString), or rely on
exception handling to detect and handle the problem.
regards,
--
Kristian
> I could encrease the maximum length but when there are a lot of values with
> shorter length and only very few with longer length than 128 this would
> decrease the speed of search operations ...
>