How To Use OLE DB DBTYPE_VARNUMERIC (229884)

MORE INFORMATION

The DB_VARNUMERIC structure is declared as follows:

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

The DB_VARNUMERIC structure member variable val[1] does not provide sufficient memory to hold a large number. To use this structure, declare a BYTE array with the appropriate size, and cast the array as a DB_VARNUMERIC structure to access the data members.

The following code illustrates how to retrieve these numbers and store them in a C double:

#define UNICODE
#define _UNICODE
#define DBINITCONSTANTS
#define INITGUID
#define NUMROWS_CHUNK 5

#include <windows.h>
#include <stdio.h>
#include <oledb.h>
#include <oledberr.h>

#include <stddef.h>
#include <math.h>

void main()
{
	IDBInitialize * pIDBInitialize;
	IRowset * pIRowset;
	IDBCreateSession * pIDBCreateSession;
	IDBCreateCommand * pIDBCreateCommand;
	ICommandText * pICommandText;
	ICommandProperties * pICommandProperties;
	IAccessor * pIAccessor;
	HACCESSOR hAccessor;
	IColumnsInfo * pIColumnsInfo;
	ULONG cColumns;
	DBCOLUMNINFO * prgInfo;
	OLECHAR * pStringsBuffer;
	IMalloc * pIMalloc;
	HRESULT hr;
	CLSID clsid;
	const ULONG nProps = 3;
	IDBProperties * pIDBProperties;
	DBPROP InitProperties[ nProps ];
	DBPROPSET rgInitPropSet;
	LPCTSTR wSQLString = OLESTR( "SELECT * FROM myTable" );
	LONG cRowsAffected;
	ULONG cRowsObtained;
	HROW rghRows;
	HROW * prghRows = & rghRows;
	ULONG cCmdPropertySets = 1;
	DBPROPSET rgCmdPropSet;
	DBBINDSTATUS DBBindStatus[2];
	DBBINDING DBBindings[1];
	BYTE * buffer;
	const ULONG nCmdProps = 2;
	DBPROP CmdProperties[ nCmdProps ];

	VariantInit( &InitProperties[ 0 ].vValue );
	InitProperties[ 0 ].dwPropertyID = DBPROP_INIT_DATASOURCE;
	InitProperties[ 0 ].dwOptions = DBPROPOPTIONS_REQUIRED;
	InitProperties[ 0 ].colid = DB_NULLID;
	InitProperties[ 0 ].vValue.vt = VT_BSTR;
	InitProperties[ 0 ].vValue.bstrVal = SysAllocString( OLESTR( "myServer" ) );

	VariantInit( &InitProperties[ 1 ].vValue );
	InitProperties[ 1 ].dwPropertyID = DBPROP_AUTH_USERID;
	InitProperties[ 1 ].dwOptions = DBPROPOPTIONS_REQUIRED;
	InitProperties[ 1 ].colid = DB_NULLID;
	InitProperties[ 1 ].vValue.vt = VT_BSTR;
	InitProperties[ 1 ].vValue.bstrVal = SysAllocString( OLESTR( "myUID" ) );

	VariantInit( &InitProperties[ 2 ].vValue );
	InitProperties[ 2 ].dwPropertyID = DBPROP_AUTH_PASSWORD;
	InitProperties[ 2 ].dwOptions = DBPROPOPTIONS_REQUIRED;
	InitProperties[ 2 ].colid = DB_NULLID;
	InitProperties[ 2 ].vValue.vt = VT_BSTR;
	InitProperties[ 2 ].vValue.bstrVal = SysAllocString( OLESTR( "myPWD" ) );

	rgInitPropSet.guidPropertySet = DBPROPSET_DBINIT;
	rgInitPropSet.cProperties = nProps;
	rgInitPropSet.rgProperties = InitProperties;

	CmdProperties[ 0 ].dwPropertyID = DBPROP_CANFETCHBACKWARDS;
	CmdProperties[ 0 ].dwOptions = DBPROPOPTIONS_REQUIRED;
	CmdProperties[ 0 ].dwStatus = DBPROPSTATUS_OK;
	CmdProperties[ 0 ].colid = DB_NULLID;
	CmdProperties[ 0 ].vValue.vt = VT_BOOL;
	CmdProperties[ 0 ].vValue.boolVal = VARIANT_TRUE;

	CmdProperties[ 1 ].dwPropertyID = DBPROP_SERVERCURSOR;
	CmdProperties[ 1 ].dwOptions = DBPROPOPTIONS_REQUIRED;
	CmdProperties[ 1 ].dwStatus = DBPROPSTATUS_OK;
	CmdProperties[ 1 ].colid = DB_NULLID;
	CmdProperties[ 1 ].vValue.vt = VT_BOOL;
	CmdProperties[ 1 ].vValue.boolVal = VARIANT_FALSE;

	rgCmdPropSet.guidPropertySet = DBPROPSET_ROWSET;
	rgCmdPropSet.cProperties = nCmdProps;
	rgCmdPropSet.rgProperties = CmdProperties;

	CoInitialize( NULL );

	hr = CLSIDFromProgID( L"MSDAORA", & clsid );

	
	hr = CoCreateInstance(clsid,
                               NULL,
                               CLSCTX_INPROC_SERVER,
                               IID_IDBInitialize,
                               ( void ** ) & pIDBInitialize );


	hr = CoGetMalloc( MEMCTX_TASK, & pIMalloc );

	pIDBInitialize->QueryInterface( IID_IDBProperties,
		( void ** ) & pIDBProperties );

	hr = pIDBProperties->SetProperties( 1, & rgInitPropSet );

	pIDBProperties->Release();

	SysFreeString( InitProperties[0].vValue.bstrVal );
	SysFreeString( InitProperties[1].vValue.bstrVal );
	SysFreeString( InitProperties[2].vValue.bstrVal );

	hr = pIDBInitialize->Initialize();

	hr = pIDBInitialize->QueryInterface( IID_IDBCreateSession,
		( void ** ) & pIDBCreateSession );

	hr = pIDBCreateSession->CreateSession( NULL,
			IID_IDBCreateCommand, ( IUnknown ** ) & pIDBCreateCommand );

	pIDBCreateSession->Release();

	hr = pIDBCreateCommand->CreateCommand( NULL,
			IID_ICommandText, ( IUnknown ** ) & pICommandText );

	pIDBCreateCommand->Release();

	hr = pICommandText->SetCommandText( DBGUID_DBSQL, wSQLString );

	hr = pICommandText->QueryInterface( IID_ICommandProperties,
			( void ** ) & pICommandProperties );

	hr = pICommandProperties->SetProperties( cCmdPropertySets, & rgCmdPropSet );

	pICommandProperties->Release();

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

	pICommandText->Release();

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

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

	// for the purposes of this example the table contains 1 column of type NUMBER
	// prgInfo[0].wType 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 bytes for precision, scale, and sign
	buffer = new BYTE[ prgInfo[0].ulColumnSize + 3 ];
	ULONG valbufferlen = ( ULONG ) prgInfo[0].ulColumnSize;

	memset( buffer, 0, prgInfo[0].ulColumnSize + 3 );

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

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

	while( DB_S_ENDOFROWSET != pIRowset->GetNextRows( NULL, 0, 1, & cRowsObtained, & prghRows ) )
	{

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

		int prec = ( int ) ( ( DB_VARNUMERIC * ) buffer )->precision;
		int scale = ( int ) ( ( DB_VARNUMERIC * ) buffer )->scale;
		int sign = ( ( int ) ( ( DB_VARNUMERIC * ) buffer )->sign > 0 ) ? 1 : -1;

		//the following code will move the data bytes of the DB_VARNUMERIC to a double
		double dValue = 0;
		BYTE hi, lo;
		double multiplier = 1;
		double adjust = 1;

		for( ULONG i = 0, j = 0 ; i < valbufferlen ; i++, j+=2 )
		{
			hi = lo = ( ( DB_VARNUMERIC * ) buffer )->val[ i ];

			lo <<= 4;
			lo >>= 4;

			dValue += ( ( ( ULONG ) lo ) * multiplier );

			multiplier *= 16;

			hi >>= 4;

			dValue += ( ( ( ULONG ) hi ) * multiplier );

			multiplier *= 16;

		}

		for( int k = 0 ; k < scale ; k++ )
			adjust *= 10;

		adjust *= sign;

		dValue /= adjust;

		printf( "%f\n", dValue );

		memset( buffer, 0, prgInfo[0].ulColumnSize + 3 );
	}

	pIAccessor->ReleaseAccessor( hAccessor, NULL );

	pIAccessor->Release();

	pIMalloc->Free( pStringsBuffer );

	pIMalloc->Free( prgInfo );

	pIMalloc->Release();

	pIRowset->Release();

	pIColumnsInfo->Release();

	delete buffer;

	if( pIDBInitialize )
		if( SUCCEEDED( pIDBInitialize->Uninitialize() ) )
			pIDBInitialize->Release();
		else
			printf( "Uninitialize failed.\n Something didn't get released.\n" );
}