I have in my possession an extract taken from a pervasive db running IRIS. The extract was taken via a ODBC connection. The extracts are is csv format.

I need to feed these into a data warehouse but the problem I have is with the way the currency/amounts are stored.

It appears we have a 2-byte and a 4-byte column which somehow (within IRIS) gets translated to a real world value. I realise this is probably something specific to IRIS, but I am postring here in the hope that someone might have come across this (since IRIS is so widely used on Pervasive).

Does anyone know how to convert the spilt integers to a real world value?

THis web page offers an explanantion of how it is supposed to work.

RE: Converting SmallInt and LongInt (Posted by Yull67) - www.foxite.com - The Home Of The Visual FoxPro Experts

I'll make a "picture" for you:

SmallInt - a small integer is kept on 2 bytes (16 bits): ssssssssssssssss
LongInt - a long integer is kept on 4 bytes (32 bits): LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL

TheRealArray is just a contiguous zone in memory (a buffer) were these 6 bytes will be moved. First, the small int, followed by the long int. Something like this:

ssssssssssssssssLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL

After this, the whole buffer is moved over (be very careful how you interpret this; it's not assigned to!) a Double variable, i.e. those 6 bytes, as they are, are just interpreted as the representation of a Double in memory, probably like this:

ssssssssssssssssLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL00 00000000000000

Note: I added zero's at the end, since a Double is represented on 8 bytes (64 bits), and I just mentioned to you that those 6 bytes were moved over a Double variable location in memory, more exactly, in the function stack. If they would have been assigned, then you would have finished with the zeros in front - don't bet on that though, it might depend on the OS or the compiler!

Now, this approach is tricky, and very low-level, since it's very tightly related with data representation in memory (I won't go into the details of it, since it's too much of a discussion) and it was chosen to minimize the space taken by the data on disk, probably for one or both of the following reasons:
1. the disk space was expensive at the time the application was implemented.
2. the tables are very big, so 2 extra-bytes really matter in terms of disk space and the speed the data is accessed on disk.

OK, so I think you might have two VFP functions that will help you perform this kind of operation. They are:

BINTOC(nExpression [, eFlags])
CTOBIN(cExpression [, cFlags])



Take a (very good) look at them and understand what they do. You will most probably need to play with the eFlags/cFlags parameter, especially with the "S" flag (see below).

I would also strongly advise you to make very, very, absolutely and paranoically certain that you obtain exactly the same values as from the Delphi function. I cannot stress enough how critical this is. Just think that you may corrupt all your data in your database if you do the smallest mistake! This could get you into a nasty problem. Be absolutely sure that you got it right, by testing all the fields kept this way and comparing with the current results you get.

OK, if this wasn't enough of a scare, then you will probably/hopefully need one of these two variants:

? CTOBIN(BINTOC(smallInt, "2") + BINTOC(longInt, "4") + BINTOC(0, 2), "B")
? CTOBIN(BINTOC(smallInt, "2S") + BINTOC(longInt, "4S") + BINTOC(0, 2), "BS")



Conversely, to store data in your SQL database you will need something like:

smallInt = CTOBIN(LEFT(BINTOC(doubleVal, 8), 2), "2")
longInt = CTOBIN(SUBSTR(BINTOC(doubleVal, 8), 3, 4), "4")



Again, play with the "S" flag to make sure that you get the same values as those stored in your database.

It's easy to understand what I did: I convert the binary values to character representations, I concatenate the strings, and convert the resulting string to a Double. Hopefully, I got it right. Please understand that I cannot be sure of that, since I don't have real data and a reference to compare with, i.e. the results you get through the Delphi function. So, you need to test, and test, and test...

Please note, and now you'll probably understand my warnings, that I also tested using "N" as a flag for CTOBIN and I've gotten a different value than with "B", so... do I need to say anything more?

These instructions are very good. I also need help on this information. I will try those parameters under the guidance offered by you. Thank you!