HOWTO: Using OLE DB DBTYPE_VARNUMERIC

ID: Q229884

The information in this article applies to:

Microsoft Data Access Components versions 1.5, 2.0, 2.1, 2.1 SP1, 2.1 SP2, 2.5

SUMMARY

When using OLE DB with databases that support very large numeric types (for example, Oracle NUMBER, or SQL Server DECIMAL or NUMERIC, and so forth) OLE DB returns a datatype of DBTYPE_NUMERIC. These large numbers can be stored and retrieved using the DB_VARNUMERIC structure provided in OLEDB.h. The following code illustrates how to retrieve these numbers and store them in a C double.


   IColumnsInfo * pIColumnsInfo;
   ULONG cColumns;
   DBCOLUMNINFO * prgInfo;
   OLECHAR * pStringsBuffer;
   IMalloc * pIMalloc;
   int prec;
   int scale;
   int sign;
   BYTE * buffer;
   double dValue = 0;
   BYTE CalcVal;

   pICommandText->Execute( NULL, IID_IRowset, NULL,
		& cRowsAffected, ( IUnknown ** ) & pIRowset );

   pICommandText->Release();

   pIRowset->QueryInterface( IID_IColumnsInfo, ( void ** )
		& pIColumnsInfo );

   pIColumnsInfo->GetColumnInfo( & cColumns, & prgInfo, 
		& pStringsBuffer );

   // For the purposes of this example the table contains 
   // only 1 column of type NUMBER.
   // Tthe prgInfo[0].pTypeInfo will be DBTYPE_VARNUMERIC == 139

   DBBindings[0].iOrdinal = prgInfo[0].iOrdinal;
   DBBindings[0].obValue = 0;
   DBBindings[0].obLength = 0;
   DBBindings[0].obStatus = 0;
   DBBindings[0].pTypeInfo = NULL;
   DBBindings[0].pObject = NULL;
   DBBindings[0].pBindExt = NULL;
   DBBindings[0].dwPart = DBPART_VALUE;
   DBBindings[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
   DBBindings[0].eParamIO = DBPARAMIO_NOTPARAM;
   DBBindings[0].cbMaxLen = prgInfo[0].ulColumnSize;
   DBBindings[0].dwFlags = prgInfo[0].dwFlags;
   DBBindings[0].wType = prgInfo[0].wType;
   DBBindings[0].bPrecision = prgInfo[0].bPrecision;
   DBBindings[0].bScale = prgInfo[0].bScale;

   // Add 3 extra bytes to the data length for 
   // precision, scale, and sign.
   buffer = new BYTE[ prgInfo[0].ulColumnSize + 3 ];
   memset( buffer, 0, prgInfo[0].ulColumnSize + 3 );

   pIRowset->QueryInterface( IID_IAccessor, ( void ** )
		& pIAccessor );

   pIAccessor->CreateAccessor( DBACCESSOR_ROWDATA, 
		1, 
		DBBindings, 
		prgInfo[0].ulColumnSize, 
		& hAccessor, 
		DBBindStatus );

   pIRowset->GetNextRows( NULL, 0, 1, & cRowsObtained, 
		& prghRows );

   hr = pIRowset->GetData( rghRows, hAccessor, buffer );

   prec = ( int ) ( ( DB_VARNUMERIC * ) buffer )->precision;
   scale = ( int ) ( ( DB_VARNUMERIC * ) buffer )->scale;
   sign = ( int ) ( ( DB_VARNUMERIC * ) buffer )->sign;

When the data is retrieved with IRowset->GetData() it is placed in the buffer and will be in the standard numeric format. However, it will just be very large; too large to access as a double or integer. It will be necessary to process the data and move it to another datatype as in the preceding code.

The following algorithm can be used to move the value into a variable of type double:


   for( ULONG i = 0, j = 0 ; i < prgInfo[0].ulColumnSize ; i++, j+=2 )
   {
   CalcVal = ( ( DB_VARNUMERIC * ) buffer )->val[ i ];
	CalcVal <<= 4;
		CalcVal >>= 4;
		if( i > 0 )
			dValue += CalcVal * pow(16, j );
		else
			dValue += CalcVal;

		CalcVal = ( ( DB_VARNUMERIC * ) buffer )->val[ i ];
		CalcVal >>= 4;
		dValue += CalcVal * pow(16, j + 1);
   }

MORE INFORMATION

DB_VARNUMERIC is declared as follows:


typedef struct  tagDB_VARNUMERIC
    {
    BYTE precision;
    SBYTE scale;
    BYTE sign;
    BYTE val[ 1 ];
    }	DB_VARNUMERIC;

Obviously, the structures member variable val[1] is not large enough to hold a large number. Therefore, you can declare a BYTE array and cast the array as a DB_VARNUMERIC structure to access the data members.

Additional query words: OLEDB C++ NUMBER NUMERIC DECIMAL

Keywords : kbMDAC kbOLEDB
Version : WINDOWS:1.5,2.0,2.1,2.1 SP1,2.1 SP2,2.5
Platform : WINDOWS
Issue type : kbhowto