| View previous topic :: View next topic |
| Author |
Message |
Meredydd Davies
Newbie
Joined: 12 Feb 2012
Posts: 3
|
 Posted: Sun Feb 12, 2012 9:16 am Post subject: [solved] max field length in Base |
 |
|
Hi folks,
I'm new to Base and am trying to find out what is the maximum number of characters allowed in a text [varchar] field? Years ago I used to work with Access and I think there was a 255-character limit .. but no mention of anything like that with Base. Could I really have, say, a seachable 500-character field ? and is there any disadvantage to such a field size?
Thanks
Last edited by Meredydd Davies on Sat Feb 18, 2012 4:19 am; edited 1 time in total |
|
| Back to top |
|
 |
Sliderule
Super User
Joined: 29 May 2004
Posts: 2477
Location: 3rd Rock From The Sun
|
| Posted: Sun Feb 12, 2012 9:59 am Post subject: Re: max field length in Base |
|
|
You said / asked:
| Meredydd Davies wrote: | Could I really have, say, a seachable 500-character field ? and is there any disadvantage to such a field size?
Thanks |
The answer is yes, you can have a VARCHAR field type of 500 characters . . . or . . . even much longer, with the default database driver HSQL. No problem.
But, I would make a suggestion . . . rather than using VARCHAR . . . use . . . VARCHAR_IGNORECASE instead. Let me explain.
If you are searching on a field that is defined as VARCHAR and the text you entered is 'Meredydd Davies' . . . in that case, the search MUST also match the CASE ( UPPER / Mixed / lower ) . . . so for example, a search of 'Meredydd Davies' would match, but, 'meredydd Davies' would NOT ( see the difference in the first character  ).
However, if you define the field type as VARCHAR_IGNORECASE . . . you could search on 'Meredydd Davies' or 'meredydd Davies' or 'Meredydd davies' or 'MEREydd davies' etc would all match.
Bottom line, I recommend using VARCHAR_IGNORECASE and, yes, you can define it with a length of 500, or much bigger.
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
Meredydd Davies
Newbie
Joined: 12 Feb 2012
Posts: 3
|
| Posted: Sat Feb 18, 2012 4:24 am Post subject: |
|
|
| Thanks. Thatr's useful, and I'll use the 'ignore case' as you suggest'. I can't see myself needing more than 500 characters in any of my fields ... but I'm still mildly curious, there must be a limit eventually? |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2477
Location: 3rd Rock From The Sun
|
| Posted: Sat Feb 18, 2012 8:10 am Post subject: |
|
|
| Meredydd Davies wrote: | | Thanks. Thatr's useful, and I'll use the 'ignore case' as you suggest'. I can't see myself needing more than 500 characters in any of my fields ... but I'm still mildly curious, there must be a limit eventually? |
While I am not certain ( I really do not care ) . . . I tried an arbitrary LENGTH of a VARCHAR and VARCHAR_IGNORECASE field, defined for 66588887 . . .
yes, that is: Sixty Six Million Five Hundred Eighty Eight Thousand Eight Hundred Eighty Seven characters,
and it worked without problem. Now I have two question for you:
- Is that large enough? If so, great.
- If it is not large enough, care to make the length longer, until it does not work . . . and . . . if so, please report back if you need it longer.
Sliderule |
|
| Back to top |
|
|
Meredydd Davies
Newbie
Joined: 12 Feb 2012
Posts: 3
|
| Posted: Sun Feb 19, 2012 12:34 pm Post subject: |
|
|
| Awesome ! Anyway ... Yes; it is plenty big enough ... and No, I don't envisage needing longer fields in the foreseeable future. Thanks for the info, and now I suppose I had better get back to creating my tables ... Ho-hum. |
|
| Back to top |
|
|
dacm
Super User
Joined: 07 Jan 2010
Posts: 734
|
| Posted: Sun Feb 19, 2012 6:49 pm Post subject: |
|
|
| Meredydd Davies wrote: | | ... but I'm still mildly curious, there must be a limit eventually? |
For the curious, the limit corresponds to the "Java array size limit of 2,147,483,648 (2^31) objects per array...This limit applies to number of characters in names, rows per table, columns per table, and characters per CHAR/VARCHAR."
The HSQL documentation seems intentionally ambiguous on this point and even calls it a theoretical maximum, inferring that the practical maximum will typically be reduced due to memory constraints. And I'm not clear on what is meant by the term "character" or "object." A character can be represented with a single, 8-bit "byte" but that depends on the encoding. For instance, UTF-8 encodes the first 256 characters of the character-set using a single byte, but extended characters use more than one byte for storage. So is it 2 billion "characters" that can be addressed by a Java array, or is it 2 billion bytes? This may depend upon the particular implementation.
You might also find these posts interesting:
http://www.oooforum.org/forum/viewtopic.phtml?p=442948#442948
http://www.oooforum.org/forum/viewtopic.phtml?p=383165#383165
http://www.oooforum.org/forum/viewtopic.phtml?p=383180#383180
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Migrating 'Embedded databases' |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
