MORE INFORMATION
The 1950 byte limit mentioned above includes the overhead required for
the record involved in the INSERT. The base overhead is 14 bytes, and
the overhead for a field depends on the data type of the field. There
is 1 byte of overhead for each string field of less than 255 bytes,
and 1 byte of overhead per 64 bytes of non-string fields.
Each example below applies to an ISAM syntax as follows:
TYPE Example
'Different elements as shown below
END TYPE
DIM Var as Example
OPEN "SAMPLE.MBA" FOR ISAM Example "Example" AS #1
INSERT #1, Var 'Overflow occurs here
CLOSE #1
Valid types and their space requirements are detailed below.
TYPE IsamType1 ' 14 (base overhead)
a1 AS STRING * 255 ' +256 (255 + 1 byte overhead)
a2 AS STRING * 255 ' +256 (255 + 1 byte overhead)
a3 AS STRING * 255 ' +256 (255 + 1 byte overhead)
a4 AS STRING * 255 ' +256 (255 + 1 byte overhead)
a5 AS STRING * 255 ' +256 (255 + 1 byte overhead)
a6 AS STRING * 255 ' +256 (255 + 1 byte overhead)
a7 AS STRING * 255 ' +256 (255 + 1 byte overhead)
a8 AS STRING * 143 ' +144 (143 + 1 byte overhead)
END TYPE ' ----
' total: 1950
INSERTing a variable of this type would be successful, but changing a8
to STRING * 144, increasing the total bytes to 1951 would produce and
overflow on the INSERT statement.
With this example, an overflow would only occur if the string values
have not been initialized or are entirely full of data (that is, the
strings have 255 characters in them). In Basic, when a fixed length
string is dimensioned, it is filled with null characters (ASCII 0).
However, the first time the fixed length string is assigned data, or
set to "", those nulls change to spaces (ASCII 32). The ISAM engine
always trims trailing spaces from string data, and so stores only the
actual string contents (plus a null terminating character).
This means that if the string variable Var.a1 above was not
initialized, ISAM sees it as a string of 255 nulls, which are not
spaces and so are not trimmed. However, if Var.a1 is assigned a value,
for example, "A," the resultant string consists of the character "A"
plus 254 spaces. ISAM can then store the string as the character "A"
plus one null character, making the length only 2 bytes relative to
the 1950 byte limit.
The following example involves non-string data:
TYPE IsamType2 ' 14 (base overhead)
a1 AS DOUBLE ' +9 (8 + 1 byte overhead)*
a2 AS DOUBLE ' +8 (8 bytes)
a3 AS DOUBLE ' +8 (8 bytes)
a4 AS DOUBLE ' +8 (8 bytes)
a5 AS DOUBLE ' +8 (8 bytes)
a6 AS DOUBLE ' +8 (8 bytes)
a7 AS DOUBLE ' +8 (8 bytes)
a8 AS DOUBLE ' +8 (8 bytes)
a9 AS DOUBLE ' +9 (8 + 1 byte overhead)
a10 AS DOUBLE ' +8 (8 bytes)
...
a232 AS DOUBLE ' +8 (8 bytes)
a233 AS DOUBLE ' +9 (8 + 1 byte overhead)
a234 AS DOUBLE ' +8 (8 bytes)
a235 AS DOUBLE ' +8 (8 bytes)
a236 AS DOUBLE ' +8 (8 bytes)
a237 AS DOUBLE ' +8 (8 bytes)
a238 AS DOUBLE ' +8 (8 bytes)
END TYPE ' ----
'total: 1948 ((238*8)+(14+30 overhead)
*Again: one byte of overhead for every 64 bytes of non-string data.
Note that if one more DOUBLE (for example, a239, and so on) elements
were added, the total number of bytes would be > 1950 bytes, which
would produce and "Overflow" on an attempt to INSERT.
All of the data types used above have sizes less than 256 bytes. If
any element in the type is larger than 256 bytes, ISAM stores the
element as an aggregate data type. Only a reference to the appropriate
aggregate type is stored in the record; aggregate data types
themselves are stored outside the actual record, in a separate area of
the database. Each reference takes 8 bytes in the record (relative to
the 1950 byte limit). For example, the type below is greater than 1950
bytes, it does not produce an "Overflow" because it is stored as an
aggregate type and only occupies 8 bytes in the record:
TYPE IsamType3 ' 14 (table overhead)
a1 AS STRING * 2000 ' + 9 (8 byte reference
END TYPE ' + 1 byte overhead)
' ----
'total: 23
Another example - nested types:
TYPE IsamType4 ' Isam4Type > 256 bytes
a1 AS STRING * 250
a2 AS DOUBLE
END TYPE
TYPE IsamType5 ' 14 (overhead)
a3 AS IsamType4 ' +9 (8 bytes + 1)
a4 AS IsamType4 ' +8 (8 bytes)
a5(500) as integer ' +8 (8 bytes)
...
END TYPE
To avoid the "Overflow" error and create larger records, you can use
aggregate data types as described above. However, there are two
drawbacks to this method: first, aggregate data types cannot be
indexed on, and second, their storage is not as efficient as standard
record storage, and will result in larger database files.