Microsoft KB Archive/77578

= &quot;Overflow&quot; Using PROISAMD with INSERT Type > 1950 Bytes =

Article ID: 77578

Article Last Modified on 11/21/2006



This article was previously published under Q77578



SUMMARY
Using INSERT to place a record larger than 1950 bytes into an ISAM table produces an &quot;Overflow&quot; error. This limit is not documented in the manuals or online Help for Basic PDS. The same error occurs when the program uses the PROISAM.EXE TSR and when the program is linked to the ISAM library routines.

This information applies to the Microsoft Basic Professional Development System (PDS) versions 7.0 and 7.1 for MS-DOS and Basic version 7.1 for OS/2.



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 &quot;SAMPLE.MBA&quot; FOR ISAM Example &quot;Example&quot; 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 &quot;&quot;, 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, &quot;A,&quot; the resultant string consists of the character &quot;A&quot; plus 254 spaces. ISAM can then store the string as the character &quot;A&quot; 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 &quot;Overflow&quot; 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 &quot;Overflow&quot; 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 &quot;Overflow&quot; 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.

Additional query words: BasicCom 7.10 7.00

Keywords: KB77578

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.