Biting the DB_NUMERIC bullet

I tried hard to avoid it. I really did. I always looked at the DB_NUMERIC structure with a mix of shock and horror. How do you handle such a beast? How do you put there a number and how do you handle it?

Well, you avoid biting the bullet until you definitely have to do it. Right now I'm working on a native code text-to-SDF conversion tool for a customer and one of the types I need to handle is the dreaded numeric.

It's interesting that this is a very popular type due to the fact that it represents a fixed-precision number. What's more, you get to define the precision. The downside to this type is its sheer size: 19 bytes! As a matter of fact, a numeric value is represented as a DB_NUMERIC structure. If you look it up either on MSDN or on oledb.h, you will see what I'm talking about. The damned thing is a monster.

Now my problem was to deserialize a text representation of a numeric value and store it in a DB_NUMERIC structure so I could feed it to the SQL CE OLE DB provider. If you are thinking about using the IDataConvert interface then you can forget it because Microsoft does not provide the msdadc.dll file anymore, so you have to convert the beast by yourself.

Firstly I tried a two tier approach: convert the text to a double and then convert the double to a DB_NUMERIC (there's some code out there that does this). You have to be careful with rounding to get the conversion done, but it works... slowly.

My second approach was actually based on the first one, but I removed most of the floating point calculations. It is fast but limited to numbers with a precision of 18 digits. Take a look:

bool CDbNumeric::Parse(LPCTSTR pszText)
{
    __int64    big        = 0;
    LPCTSTR    pCur;
    int        nScale    = -1;
    int        i;

    for(pCur = pszText; *pCur == ' '; ++pCur)
        ;

    if(*pCur == '-')
    {
        ++pCur;
        sign = 0;
    }
    else
        sign = 1;

    for(; *pCur && nScale; ++pCur, --nScale)
    {
        if(IsNumeric(*pCur))
        {
            big = big * 10 + (*pCur - '0');
        }
        else if(*pCur == '.')
        {
            if(nScale > 0)
                return false;

            nScale = scale + 1;
        }
        else
            return false;
    }

    memset(val, 0, sizeof(val));

    for(i = 0; i < 16 && big != 0; ++i)
    {
        val[i] = (BYTE)(big & 0xFF);

        big >>= 8;
    }

    return true;
}

It's faster, believe me.