Access-about Delphi Operating single-precision data in an Access database

In recent several posts, and QQ group discussion inside, I found that many users have encountered the problem is due to improper use of single-precision/double-precision values. So I want to talk about this topic specifically.

Single-and double-precision numeric types first appear in the C language (in a more general language), and the single-precision type in C is called floating-point type (float), which is by definition a floating decimal point for data storage. These two data types were originally generated for scientific calculations, and he was able to provide scientific calculations with high enough precision to store values that were more demanding for precision. But at the same time, he also fully conforms to the concept of numerical values in scientific calculations:

When we compare the length of the two sticks, one method is to compare the lengths of each other, one way is to measure the length separately. But in fact there are no two sticks of exactly the same length in the world, the length accuracy we measure is limited by the human visual ability and the accuracy of measuring tools. In this sense, it makes no sense to judge whether the two sticks are the same length, because the result must be false, but we can compare the two which are longer or shorter. This example provides a good overview of the design intent and significance of single-precision/double-precision numeric types.

Based on the above understanding, the single-precision/double-precision numeric type is not an accurate numeric type from the beginning of the design, he only guarantees that the accuracy of this numeric type is accurate, and the accuracy is not guaranteed, for example, a value of 5.1, which is likely to be stored in a single-precision/ The actual value in the double-precision number is 5.100000000001 or 5.09999999999999. There are two ways to explain the cause of this phenomenon:

A simple way to explain:

You can try in the properties panel of any one of the controls, set his width to: 3.2CM, when you are finished, you will find that the value automatically becomes 3.199cm, no matter how you change, you cannot enter 3.200CM, because actually stored in the computer is not the number of CM units, but "twips" Is the value of the unit, and the ratio between "twips" and CM is a very difficult to be removed, so after you input, the computer automatically converted to the nearest "twips" value, and then converted to centimeters display to the properties panel, this one by one, two rounds, the error is out. Single-precision/double-precision is similar to the principle, in fact, in the binary storage, single-precision/double-precision is similar to the same method, and such a storage is impossible to achieve accurate.

In-depth interpretation methods:

Let's see what our single-precision/double-precision values are stored in digital media, and we use the following code to dissect a single-precision type:

Public Declare Sub copymemory Lib "kernel32" Alias "RtlMoveMemory" (Destination as any, Source as any, ByVal Length as Lon G

Public Sub floattest ()
Dim Dblvar as Single

Dblvar = 5.731/8
Dbloutput Dblvar

Dblvar = Dblvar * 2
Dbloutput Dblvar

Dblvar = Dblvar * 2
Dbloutput Dblvar

Dblvar = Dblvar * 2
Dbloutput Dblvar

Dblvar = Dblvar * 2
Dbloutput Dblvar

Dblvar = Dblvar * 2
Dbloutput Dblvar

End Sub

Public Sub dbloutput (ByVal Dblvar as single)
Dim Bytvar (3) as Byte
Dim I As Integer, J As Integer
Dim Strvar as String

copymemory ByVal varptr (Bytvar (0)), ByVal VarPtr (Dblvar), 4
Strvar = Dblvar & ":"
for i = 3 to 0 Step-1
For j = 7 to 0 Step-1
Strvar = Strvar & (Bytvar (i) and 2 ^ j)/2 ^ J
Next J
Strvar = Strvar & ""
Next i
Debug.Print Strvar

End Sub
After running we get the output (output format is high left, low right):

. 716375:00111111 00110111 01100100 01011010
1.43275:00111111 10110111 01100100 01011010
2.8655:01000000 00110111 01100100 01011010
5.731:01000000 10110111 01100100 01011010
11.462:01000001 00110111 01100100 01011010
22.924:01000001 10110111 01100100 01011010
here, we turn the single-precision type into binary data output, and here we see that although these six numbers are completely different, but their binary storage is surprisingly similar, we see the red labeled portion, each time adding 1, in fact, The single-precision data type uses the 1th bit from the high position as the positive and negative mark bit (green), the 2nd to the 9th bit, is a cross-byte signed byte type data, this value determines the direction and the number of digits (red) of the decimal movement, the 10th bit to 32 bits holds an integer (blue) in the stored procedure, The computer first shifts the input value (multiplication 2) until the integer portion of the number takes up all 24 bits of the integer, then writes the number of bits moved to the floating point (red), and the shifted result is written to the integer part (blue and green), and the fractional part is discarded. The evaluation of the time is the reverse process, based on the positive and negative digits and the whole number of values, and then based on the red part of the integer to shift (multiplication 2 of the second side), and ultimately we get the single-precision value. Double-precision is the same principle, but the number of bits is more.

By dissecting the binary storage format of single-precision numeric values, we can see clearly that, in fact, single-precision/double-precision storage is done by multiplication and division, which must be rounded, and if your values are rounded in the division, then the initial value you assign is probably not exactly the same as the values you end up storing, the tiny difference, is not inconsistent with the single-precision/double-precision design objectives.

When we use a single-precision/double-precision numeric value in a database or VBA code, you may not see the difference in the interface, but in the actual storage, the difference is really there, and when you compare it equally, the system simply makes a binary comparison, The interface can not be reflected in the small differences, in the binary comparison before there is no place to hide, so, you are equal to the comparison returned an unexpected false.

Conclusion

In this paper, we introduce the nature of single-precision/double-precision data types and their characteristics (pros and cons), by comparing and dissecting we learned that single-precision/double-precision is actually stored as an approximation, floating-point characteristics determine that he can store very small numbers, can also store a large number, His data accuracy is not an absolute value, but a percentage of stored values, if you store 10 of 100 square, the error may be 10 80 square, if you store 10-100 times, the error may be 10-120 times. Therefore, single-precision/double-precision data types cannot be compared (or database associated) equally.

If you need to compare or correlate equivalence, there are several scenarios:

1. Use a currency type designed for accuracy.
2. Use integer type to store and shift in code.
3, in some specific cases can be stored in text.

Access-about Delphi Operating single-precision data in an Access database