Microsoft KB Archive/229884

= How To Use OLE DB DBTYPE_VARNUMERIC =

Article ID: 229884

Article Last Modified on 8/30/2004

-

APPLIES TO


 * Microsoft Data Access Components 1.5
 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6
 * Microsoft Data Access Components 2.7

-



This article was previously published under Q229884



SUMMARY
When you use OLE DB with databases that support very large numeric types (for example, Oracle's NUMBER data type which supports up to 38 digits), OLE DB returns DBTYPE_VARNUMERIC as the column's data type. These large numbers can be stored and retrieved by using the OLE DB DB_VARNUMERIC structure, which is provided in the Oledb.h header file.



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:
 * 1) define UNICODE
 * 2) define _UNICODE
 * 3) define DBINITCONSTANTS
 * 4) define INITGUID
 * 5) define NUMROWS_CHUNK 5


 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 


 * 1) include 
 * 2) include 

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" ); }

Additional query words: OLEDB C++ DECIMAL

Keywords: kbhowto kboracle KB229884

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.