28 Sept 87



This information is provided by Jim Manley of Information Systems, Inc.
of Baltimore, MD., with the hopes that it will benefit other BASIC
users.


INFORMATION ON BTRIEVE FIELD TYPES USED WITH BASIC


The following pages report our findings on the BTRIEVE field types with
BASIC and a BASIC interface project. The section gives formulas for using
the BTRIEVE data types DATE, MONEY and in BASIC. The pages that explain
the three data types are preceded by a page of terminology and definitions
used throughout the actual formulas. Some of the explanations involve
specific BASIC and DOS commands and computer science terminology. You may
want to review the terminology list before you try to understand the
forumulas.


TERMINOLOGY
__________________________________________________________________________

Decimal Digit
Whole integers 1,2,3...

Decimal Representation
The decimal value of a character, Base 10.
(See an ASCII Character Set Table.)

Hexidecimal Representation
The representation of characters in Base 16: 0, 1-9, ABCDEF.

Byte
A portion of memory that can hold 2 characters or decimal
digits in hexidecimal representation.

Push
To write to a random file with an Lset statement.

Pull
To read from a random file.

Lset
To write to a field of any size left justifying the string
value in the field, thus padding to the right with spaces.
RSET to right justify.

ASC
Give The ASCII value of a character.

CVI
To convert a string to a 2 byte integer.

CVS
To convert a string to a 4 byte integer.

MKI$
The statement is used in an LSET or RSET statement to convert
integer type values to string type values of 2 bytes.

MKS$

Is used in an LSET or RSET statement to convert single
precision type values to string type values of 4 bytes.

Debug program

A BASIC program used to display the contents of a file.
This dump displays each byte in hexidecimal form and as
ASCII characters. We used it to dump the contents of
the BTRIEVE sales file to see the actual value of specific
fields.


BTRIEVE DATE FIELD TYPE
__________________________________________________________________________

Fields of BTRIEVE's DATE type are four byte values stored internally
with the leftmost byte a binary format of Day, the next byte a binary
format for Month and the last two (rightmost) bytes a binary
reprenstation of a 4 digit Year value.

This BTRIEVE Date field is not supported by BASIC but you can write to
and read from a four byte Date field using either of the following
formulas:

A) During our trip to Comdex '87 we met with people of
H2 Solutions of Atlanta. They supplied us with the following
formula to write to a BTRIEVE DATE FIELD:

DD = 2 digit value for Day
MM = 2 digit value for Month
YYYY = 4 digit value for Year

Field #1, 2 as DATE2$, 2 as DATE4$

Lset DATE2$ = mki$(DD +(MM * 256))
Lset DATE4$ = mki$(YYYY)

The formula to read a DATE variable is just the reverse:

MM = int(cvi(DATE2$) / 256)
DD = cvi(DATE2$) - (MM * 256)
YYYY = cvi(DATE4$)


B) During further work on the interface project we came up with
the following, easier method of working with the DATE variable
through BASIC. If you further break-down the four byte DATE
variable to the following:

Field #1, 1 as DATE1$, 1 as DATE2$, 2 as DATE4$

You can fill the first byte with the 2 digit Day value and
the second byte with a 2 digit Month value without the
calculation needed in the previous formula to fill Day and
Month simultaneously to a 2 byte field. This method to
write to a Date variable is much more readable:

Lset DATE1$ = mki$(DD)
Lset DATE2$ = mki$(MM)
Lset DATE4$ = mki$(YYYY)

To read in use this method:

DD = cvi(DATE1$)
MM = cvi(DATE2$)
YYYY = cvi(DATE4$)


BTRIEVE MONEY FIELD TYPE
__________________________________________________________________________

The BTRIEVE MONEY field is a decimal type preceded by an implied $
with two digits to the right of an implied decimal point. This
corresponds to the Great Plains DLR type that is stored internally as
packed BCD number with two decimal digits p


byte: 0 1 byte n - 1
_____|_____ _____
| | | | | | | |
|__|__|__|__| |__|__|
digit: 1 2 |3 4 digit sign
2n -1 byte

where the sign byte is "F" or "C" for positive numbers and "D" for
negative numbers. This MONEY type is directly supported by COBOL and
PASCAL but not BASIC.

To write to a MONEY field through BASIC you need to write byte by byte,
the decimal representation of the hexidecimal value you want to see
(ie., the dollar amount) in each byte (packed BCD number).

For example, to write the value $1234.56 to a 4 byte MONEY field, it
would be seen in a hex dump as:

byte: 0 1 2 3
| | |
| . 1 |2 .3 | 4 .5 | 6 .C |

You need to fill each byte one at at a time. Therefore, to fill the right
most byte you need to push the decimal equivalent of 6C or the decimal
108. You can think of it as pushing the sum of the hexidecimal
representation of each digit. The byte

For example:
To write to an 8 byte MONEY variable from a 7 digit value called FEE$,
use the following formula:

An 8 byte MONEY variable can hold a 15 digit value. The 7 digit value
in this example needs only the 4 right most bytes. Therefore the field
statements lumps the first 4 unused bytes into a 4 byte field that we pack
with zeroes.

Field #1, 4 as AMOUNT4$
Field #1, 4 as dummy, 1 as AMOUNT5$, 1 as AMOUNT6$, 1 as AMOUNT7$
Field #1, 7 as dummy$, 1 as AMOUNT8$.

Fill the leading unused bytes with 0 (zeroes)
Lset AMOUNT4$ = mks$(0) 'Use mks$ for a 4 byte field.

Fill the rightmost byte with the right most digit of FEE$ and the
correct sign byte: for positive values hex "C" = +12
for negative values hex "D" = +13

Lset AMOUNT8$ = mki$(val(right$(FEE$,1) * 16 + 12))

Pull off remaining digits from the right of FEE$ to fill the Money
field bytes from the right to the left.

II = 6: For I = ((len(FEE$) - 1) to 1 step - 1
DIGIT (II) = val(mid$(FEE$,I,1))
II = II - 1
Next I

Fill the remaining bytes with hexidecimal representation of remaining
digits.

Lset AMOUNT7$ = mki$(DIGIT(5) * 16 + DIGIT(6))
Lset AMOUNT6$ = mki$(DIGIT(3) * 16 + DIGIT(4))
Lset AMOUNT5$ = mki$(DIGIT(1) * 16 + DIGIT(2))

You can read from a Money variable with the following formula:

Since you know your first four bytes contain all zeroes you need not
convert them. The last four bytes can contain a total of seven digits
plus the sign value, with the highest digit = itself x 10E4 the next
highest = itself x 10E3, the (rightmost) last digit of the value =
itself x 10E-2.

To read the value you need to look at each byte individually in the
following manner:

For I = 4 to 1 'to look at the last four bytes of AMOUNT$
'x varies from 5 to 8

Left Digit = chr$(int(asc(AMOUNTx$/16) + 48)
Right Digit = chr$(int(asc(AMOUNTx$) MOD 16) + 48)

FEE = val(Left Digit) * 10EI + FEE :if I = 4 then I = 1:
goto 'SIGN'

FEE = val(Right Digit) * 10EI-1 + FEE

Next I = val(Right Digit) * 10EI-1

'SIGN' if Right Digit = "D" then FEE = 0 - FEE