Convert Double Data Type 2

[jordanking]

Hello,

I have a byte array of 8 bytes in length that is supposed to contain the binary of a Double data type. The official SDK documentaion for the data says that it is a "8 byte IEEE long real". It comes from a recordset given by an external database(not access). I cannot extract the double number. I want to be able to "see" what the double number is. I have isolated the byte array by using the copyMemory API and then chopping the data into its respective fields. The other character encoding of the recordset is ASCII Decimal, I don't know if that matters.

Here is some code:

                Call CopyMemory(pField.Data(0), pRecord.Data(558), 8)
                    With pField
                        For i = 1 To 8
                        abytTemp = pField.Data(i)
                        strTemp = abytTemp
                        strOutput = strOutput & " " & strTemp
                        Next
                        Debug.Print strOutput
                    End With

This is what the code returns:
0 0 0 0 0 240 191 0
or
00002401910

If I pass this number or the byte array (as data) to a double data type variable it returnsthe following. The value is supposed to be "5000" which I know for sure.

3.60745707139629E-313

It returns this regardle of what bytes or numbers I pass to it. There might be an easy function that does this, I found functions in VB that were close, can they be called in VBA?

Basically I am unsure how to work with double number formats from binary byte arrays.

Thanks in advance

J.K.

 

[ETID]

maybe this?

strOutput = strOutput & " " & cstr(strTemp)

 

[PCLewis]

You will need to find out how the source app stores those "8 byte IEEE long real" values. They clearly do not store them in the same way that VB does. Can I assume for a start that the other system is NOT windows based?

On Windows OS, as far as I have tested, 5000 is represented by:
0 0 0 0 0 136 179 64
and -5000 by:
0 0 0 0 0 136 179 192

check this link out for a good run down on floating point number storage.

http://www.nuvisionmiami.com/books/asm/workbook/floating_tut.htm

 

[PCLewis]

Hmmm, if you mentioned the name of the external database, it might be possible someone has documentation of how they store their 8 byte IEEE long real. Is it a particular field type in this external database? It is one of those tiny pieces of info that might help someone (maybe me!) in years to come so that's why I am interested in getting to the bottom of this strange representation.

 

[jordanking]

Sorry for the tardy reply,

I was reading up on double number data types. Quite a lot to learn. Anyway, the external data base is created by simply accounting 2005. It is a windows based app and I am accessing records through called api functions that were given with the SDK i got from simply. The data I get is from a "getRecord" API function that returns the record as an array of bytes where each field is identified only by length. I.e. field one is 4 bytes so field 2 starts at byte 5 and goes for 10 bytes etc... So the bytes I am passing to the double variable are simply (byteN to byteY) within the byte string that represents the entire retrieved record.

The suggestions you both made helped send me in the right direction to understand the data type, so stars all around.

But I still have no idea how to take hte binary data and turn it into a real number. Even if it returned the right numbers for 5000 as shown above by PCLewis how do I work with those number?

J.K.

 

[PCLewis]

To turn a byte array into a double, you could use Types and overlay one variable of one Type onto another (of equal size). That's a non API way to do it but since you are API savvy, you could use the CopyMemory API to do it for you.

Declare Sub CopyMemory Lib "KERNEL32" Alias "RtlMoveMemory" (hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)
Sub test3()
  Dim dbl As Double
  Dim sdbl As String
  Dim sng As Single
  Dim b(7) As Byte
  Dim s(3) As Byte
  dbl = 5000
  sng = 5000
 
  CopyMemory b(0), dbl, 8
  CopyMemory s(0), sng, 4
  
  dbl = 0
  sng = 0
  
  Debug.Print dbl, sng
  CopyMemory dbl, b(0), 8
  CopyMemory sng, s(0), 4
  
  Debug.Print dbl, sng
  
  
End Sub

 

[jordanking]

Thanks for the advice PCLewis,

I tried your sample and it worked, however I am having trouble implementing it into my project.



pRecord.Data() is a byte array that is 1999 bytes in length as is a user defined type in another module. It is defined as "Buffer":

Public Type Buffer
    Data(1999) As Byte
End Type

I tried to pass the byte array directly to the copy memory function with a double variable as the destination:

   Dim abytTemp() as Byte
   Dim dOut as Double
   Dim strTemp as String
       Call CopyMemory(abytTemp(0), pRecord.Data(558), 8)
         Call CopyMemory(dOut, abytTemp(0), 8)
         Debug.Print dOut

It returns a "-1"

SO I tried:

 Call CopyMemory(abytTemp(0), pRecord.Data(558), 8)
    Call CopyMemory(dOut, abytTemp(0), 8)
        Debug.Print "dOut = " & dOut
        strTemp = abytTemp()
        Debug.Print "strTemp = " & strTemp
        dOut = abytTemp()
        Debug.Print "dOut = " & dOut

the results in the immediate window is:
dOut = -1
strTemp = ?
dOut = 6.42253719392287E-318

I am stumped. Is there another API that converts these numbers, I am willing to code anything to get this. You mentioned another way where you could "overlay" variables?

 

[PCLewis]

No you are doing it right. The point I have been making is that the 8 bytes you are retrieving are not the same 8 bytes that Windows uses to store a "IEEE long real".

The technique is correct - the source data is in question. Heard of "garbage in, garbage out"?

Can you post on Simply's forums for help on correct interpretation of their 8 byte double representation?

 

[PCLewis]

Hmm ok here are the 64 bit binary representations of the number 5000 in both your app's view, and in MS's view.

MS
0000000000000000000000000000000000000000000100011100 11010000001 0

0000000000000000000000000000000000000000000011111111 11010000000 0

I will have a solution for you shortly. The pattern in the above is clear and I am sure that analysis will show me how they store the number. Already I can see that the exponent part of their number comes out as 11 (no bias) whereas the MS version is 12 (1023 bias). I'll check it out and come back to you.

 

[PCLewis]

Eureka..

http://support.microsoft.com/kb/q42980/

http://charon.alterego-it.pl/informatyka/formaty/binary/mbf.htm

That application appears to be using "Microsoft Binary Format" rather than IEEE to store it's floats. If so, then a conversion function will be possible.

An interesting investigation to be sure.

more to follow...

 

[PCLewis]

grrr sorry mate but it's not MBF I had high hopes too.

If the documentation says it's IEEE standard and if VB's docs say that their double is IEEE standard, and the two aren't the same then clearly one is wrong. I tend to have to assume its the Simply software in this case. I haven't worked out how to interpret their bit pattern yet though.

 

[jordanking]

I have explored the simply documentation and it corresponds the following data types:

SIMPLY VBA
CHAR: string
BYTE: byte
DATE: date
TIME: date
REAL: double
BCD: decimal
INT: integer
LONG: long
BOOL: boolean

The data I am capturing comes from a byte array (string) and then is passed to the double variable. I wonder if that affects it some how. Is there a way for me to display the bits of the byte array to see if it changes between copy memory and passing it to the double variable?

 

[jordanking]

I have an email into thier support centre, i will post information when I get it.

thanks for the effort PCLewis

 

[PCLewis]

You touch upon another issue which may well be the root of your problem.

I have not been able to work out how the bytes you provided equate to the number 5000 so what if, the bytes you provided are incorrect? The thing is, there are enough points of commonality between my predicted result and your data to tell me it's more likely that you are right and my abilities are lacking.

In any case, perhaps you could deliver the entire bytearray to me (in this forum) and the details on the relative positions of the elements in the array. Perhaps provide a second and third example of 8 bytes and the actual number they are meant to represent as well. That would help in this (for me) fun exercise.

As far as checking the contents of the string are not altered, you can use STRCONV or the CopyMemory api to convert your bytestream into a stirng or back again. I guess, just scrolling through the array and string and compare each element is all you can do.

I've never had a problem with either strconv or copymemory.

One other small thing that should not have bearing on the Double discussion but might have meaning elsewhere in your work, is that Intel use "Little Endian" for multibyte storage and processors like Motorolla use "Big Endian". This might mean something if for example Simply Accounting was developed for use on a MAC, and then ported to PC.

Little Endian means that a Long Int (for example) is stored like this in memory:
memory address + 0: byte 0
memory address + 1: byte 1
memory address + 2: byte 2
memory address + 3: byte 3

Big Endian is the opposite:
memory address + 0: byte 3
memory address + 1: byte 2
memory address + 2: byte 1
memory address + 3: byte 0

I already tried messing about with the bye order you gave but to no avail. I wish an expert in this area would stumble upon this and after laughing at my feeble attempts to understand this whole Floating Point thing, put us right in 2 sentences or something

 

[jordanking]

You are right. I want to thank you for your time. You examples made me go back through mt work and rethink the way I was passing the byte array. Fact is, I was passing the wrong array. Once I figured that out, just right now, I retried you previous examples and skipped passing the bytes through an array and passed them directly to a double variable. Well it worked. So after all that I really want to thank you for your effort. It was my error in passing the wrong byte array.

Here is what finally worked:

Call CopyMemory (abytTemp(0), pRecord.Data(558), 8) 
   Call Copy Memory (dblOut, abytTemp(0), 8)
    Debug.Print dblOut

the result was 5000 on my screen.

I was very excitied.

So again, thanks for the effort, I hope it was of some value to you.

J.K.

 

[jordanking]

But now I am on to figuring out how to convert a signed BCD number that represents a date into something readable.

What a never ending process.

Thanks again

 

[PHV]

Why not simply this ?
Call CopyMemory (dblOut, pRecord.Data(558), 8)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886