Avoiding MEMO Fields

Memo fields are special fields set aside for large or unlimited text or byte streams.

In most file-based database systems, memo fields contribute to an enormous amount of problems, especially in larger installations. Because of their fixed-sized record lengths, traditional XBase tables are generally more reliable and/or easier to repair than MS-Access tables. However, memo fields are the exception. Because their size is open-ended, a series of internal pointers are needed to track the position, beginning, end, and expansion of memo field sections. More pointers means more risk of data corruption if the power suddenly shuts off on a machine, for example.

Memo fields can be a huge source of headaches and data corruption. Memo fields have given XBase an undeserved scalability problem reputation. (I have also encountered memo field problems in SQL-Server 7.0. Thus, such problems may not be limited to just file-based databases.)

If you are planning for a robust setup, then I suggest that you avoid direct usage of memo fields if possible.

Here are possible alternatives to direct memo fields:

1. Commercial memo systems. (Sorry I don't have any links right now.)

2. Save the large text to a file if it does not fit into a regular text field. This requires a bit of programming, but may be worth it. Build a generic utility that can be used on multiple projects. One naming scheme is to have a sub-folder named for the table, and then the file name is the unique key for that record or memo. Example: invoice/473619.txt where "invoice" is the folder (directory) name and 473619 is the invoice number. If an invoice uses more than one memo field, then perhaps something like "473619_a.txt" and "473619_b.txt" can be used.

3. Split large text into multiple records. An example set of routines is given below.

Sample Routines

These examples assume that there is only one memo field column per table, which is the usual case. However, you can modify them as needed for more fields per table.

DISCLAIMER: These are only example routines and have not been thoroughly "road-tested." Test thoroughly before going live with any project that uses them. (Please notify me if you find any bugs.) They are also meant only for internally-used or simple text, not formal contracts, press releases, etc. For more formal stuff, the file approach mentioned above is recommended.

PROCEDURE SaveMemo    
* Simulate memos for typical text, version: 1.0
* Table name, table key, the text to save
Parameters p_tableNm, p_key, p_text

private flength   && length of target field
private worktext, reloop, pos

worktext = rtrim(p_text) + space(1)   
select 0          && New area (some dialects different)
use memosim.dbf
flength = len(aline)     && target field size

do while len(rtrim(worktext)) > 0

  * Find a blank to split on
  pos = flength
  if pos > len(worktext)
    pos =  len(worktext)
  endif
  reloop = .t.  
  do while reloop
    if substr(worktext, pos, 1) = space(1) 
      reloop = .f.
    else
      pos = pos - 1
      if pos < 10    && Not likely to find space, split anyhow
        pos = flength
        if pos > len(worktext)
          pos =  len(worktext)
        endif  
        reloop = .f.
      endif
    endif
  enddo

  append blank
  replace tablename with p_tableNm
  replace refkey    with p_key
  replace aline     with ltrim(rtrim(substr(worktext, 1, pos)))
  
  * chop off part already saved
  worktext = substr(worktext, pos)   
  
enddo

use    && close table

RETURN

PROCEDURE GetMemo    
* Get memos saved via SaveMemo.prg, version: 1.0
* Note that deletion of old data step is not done here.
* Table name, table key, the text to save
Parameters p_tableNm, p_key, p_text

private worktext

worktext = space(0)
select 0          && New area (some dialects different)
use memosim.dbf

set filter to tablename = p_tableNm .and. refkey = p_key
* Note that you may want to implement indexing for speed
goto top

do while .not. eof()
  worktext = worktext + space(1) + rtrim(ltrim(aline))
  skip
enddo

use    && close table

p_text = ltrim(rtrim(worktext))      && function not used because varies per dialect
RETURN