Bill, Be sure to carefully understand the limitations for VARCHAR columns. Especially understand that they are stored in the RB4/RX4 file as large objects. Large objects (Character and Binary) have special rules/syntax for loading/importing and unloading/exporting. Not all functions operate on VARCHAR data (columns or variables). There are limitations to assigning VARCHAR data to other data types such as TEXT or NOTE. You can not assign VARCHAR to these data types. There are also ODBC considerations for VARCHAR data types.
Jim Bentley Wednesday, October 12, 2011 12:20 PM Subject: [RBASE-L] - Re: note fields > > >Thanks for that, Larry. Based on this, I'm beginning to like varchar all the >more and can see my reasons not to use note are strengthening. > > >On Wed, Oct 12, 2011 at 10:01 AM, Lawrence Lustig <[email protected]> >wrote: > >This happens because: >> >> >>1. Note fields are stored in the row with the rest of the data. >> >> >>2. Each row has a maximum size it cannot exceed (4000 bytes in older >>versions, a longer limit in newer versions, I think). >> >> >>3. Because a note field is variable length, the total size of the row >>increases when you add characters to a note field. >> >> >>4. Because you can have more than one note field per row, it's entirely >>possible that if you have several note fields in a single row and they each >>contain a lot of data, the total amount of data you're trying to store in the >>row will be longer than the largest allowed row length. >> >> >>One advantage of VARCHAR fields in R:Base is that they are not stored in the >>row with the other data. Only a single pointer value into the RB4 file is >>stored (presumably 4 bytes in 32 bit R:Base and 8 bytes in 64 bit R:Base). >>Therefore the length of the VARCHAR field does not affect the length of the >>R:Base row (aside from that tiny little pointer value). >>-- >>Larry >> >> >> >>________________________________ >>From: William Stacy <[email protected]> >>To: RBASE-L Mailing List <[email protected]> >>Sent: Wednesday, October 12, 2011 12:37 PM >> >>Subject: [RBASE-L] - Re: note fields >> >> >>Maybe I need a cup of coffee. You said: 'The room taken by other columns in >>the table may limit how much you can actually store.' >> >>So lets say you have a 3 column table , 1st table is int type, 2nd is 8 char >>txt type, 3rd is note. How in the world does the room taken by cols 1 and 2 >>affect how much can be stored in col 3? Or is note not actually a column but >>some creature that consumes what is left over? >> >>?? >> >> >>On Wed, Oct 12, 2011 at 9:26 AM, Dennis McGrath <[email protected]> wrote: >> >> >>>The maximum width of a row of data is fixed. >>> >>> >>>Dennis McGrath >>>Software Developer >>>QMI Security Solutions >>>1661 Glenlake Ave >>>ItascaIL 60143 >>>630-980-8461 >>>[email protected] >>> >>>________________________________ >>> >>>From:[email protected] [mailto:[email protected]] On Behalf Of William Stacy >>>Sent: Wednesday, October 12, 2011 11:24 AM >>> >>>To: RBASE-L Mailing List >>>Subject: [RBASE-L] - Re: note fields >>> >>>I don't understand the second sentence. How would the other columns affect it? BTW I'm interested in the formatting available in varchar, although much of that functionality may be unused because of report formatting (it would be weird to have different formatting for varchar columns than for the rest of the report, although I can envision some uses for it, such as imported stuff). >>>On Wed, Oct 12, 2011 at 9:00 AM, Dennis McGrath <[email protected]> wrote: >>>Just remember that NOTE columns can only hold up to 4000 characters. >>>The room taken by other columns in the table may limit how much you can actually store. >>> >>> >>>Dennis McGrath >>>Software Developer >>>QMI Security Solutions >>>1661 Glenlake Ave >>>ItascaIL 60143 >>>630-980-8461 >>>[email protected] >>> >>>________________________________ >>> >>>From:[email protected] [mailto:[email protected]] On Behalf Of William Stacy >>>Sent: Wednesday, October 12, 2011 10:54 AM >>>To: RBASE-L Mailing List >>>Subject: [RBASE-L] - Re: note fields >>> >>>Hi, and I to am sorry I missed the conf. I was planning to make heavy use of varchar in my migration, but your comments make me wonder if I should steer more toward note. I doubt if comments will ever be over 4000 char. and I would like to be able to search on all columns, but that too is not a show stopper. Any other reasons for one over the other (you didn't mention the overhead for varchar). >>>On Wed, Oct 12, 2011 at 8:16 AM, A. Razzak Memon <[email protected]> wrote: >>>At 10:51 AM 10/12/2011, Dan Goldberg wrote: >>>I am looking for your opinion on data types. >>> >>>Are note data type columns the best to use for long text fields? >>> >>>I have used them in the past but now there are other data types >>>that might be better to use. >>> >>>Dan, >>> >>>A few suggestions: >>> >>>01. If you know the range of your text (1 - 1500), and there is no >>> plan to expand, use TEXT (value) data type. >>> >>>02. If going beyond 1,500 and not exceeding 4,092 characters, use >>> NOTE fields. >>> >>>03. If going beyond 4,092 characters and having the flexibility of >>> variable characters and formatted text data, use VARCHAR data >>> type. >>> >>>Notes and Tips: >>> >>>TEXT Data Type: >>>- Holds alphanumeric data >>>- The default length is eight characters; the maximum is 1,500 characters >>>- Unlike NOTE columns, you set the maximum length of a TEXT column >>>- R:BASE reserves a minimum of four bytes of internal storage space >>>- Indexes and constraints are allowed on TEXT data types >>> >>>NOTE Data Type: >>>- Holds variable length text of up to 4,092 characters >>>- NOTE data type columns cannot be used in an expression >>>- The actual length of each NOTE entry is determined by the data contained >>> in the column; you do not set a length >>>- R:BASE reserves a minimum of four bytes of internal storage space >>>- The NOTE data type now supports a size (n). The NOTE data type will >>> truncate at (n) characters and give a warning >>>- Indexes and constraints are allowed on NOTE data types >>>- No length is needed >>> >>>VARCHAR Data Type: >>>- Holds alphanumeric data >>>- No length is needed >>> >>>Also, refer to R:BASE in-line Help for String Manipulation Functions >>>and restrictions, if any. >>> >>>Hope that helps! >>> >>>Very Best R:egards, >>> >>>Razzak. >>> >>> >>> >>> >>>-- >>>William Stacy, O.D. >>> >>>Please visit my website by clicking on : >>> >>>http://www.folsomeye.net >>> >>> >>> >>> >>> >>>-- >>>William Stacy, O.D. >>> >>>Please visit my website by clicking on : >>> >>>http://www.folsomeye.net >>> >>> >>> >> >> >>-- >>William Stacy, O.D. >> >>Please visit my website by clicking on : >> >>http://www.folsomeye.net >> >> >> >> >> >> > > >-- >William Stacy, O.D. > >Please visit my website by clicking on : > >http://www.folsomeye.net > > > > > >
