HOWTO: Retrieving Numeric Data with SQL_NUMERIC_STRUCT

• Microsoft ODBC Driver for SQL Server, version 3.7

SUMMARY

This article describes how to retrieve numeric data from the SQL Server ODBC driver into a numeric structure and how to get the correct values using specific precision and scale values.

The SQL_NUMERIC_STRUCT is defined in the sqltypes.h header file as follows:

#define SQL_MAX_NUMERIC_LEN		16
typedef struct tagSQL_NUMERIC_STRUCT
{
	SQLCHAR		precision;
	SQLSCHAR	scale;
	SQLCHAR		sign;	/* 1 if positive, 0 if negative */ 
	SQLCHAR		val[SQL_MAX_NUMERIC_LEN];
} SQL_NUMERIC_STRUCT; 

MORE INFORMATION

WARNING: ANY USE BY YOU OF THE CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK.

Microsoft provides this code "as is" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

#include <stdio.h>
#include <string.h>
#include <conio.h>
#include <stdlib.h>
#include <ctype.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>

#define MAXDSN		25
#define MAXUID		25
#define MAXAUTHSTR	25
#define MAXBUFLEN	255

SQLHENV	        henv = SQL_NULL_HENV;
SQLHDBC	        hdbc1 = SQL_NULL_HDBC;     
SQLHSTMT        hstmt1 = SQL_NULL_HSTMT;
SQLHDESC        hdesc = NULL;


SQL_NUMERIC_STRUCT NumStr;

int main()
{
	RETCODE retcode;

//Change the values below as appropriate to make a successful connection. //szDSN: DataSourceName, szUID=userid, szAuthStr: password

UCHAR szDSN[MAXDSN+1] = "sql33",szUID[MAXUID+1]="sa", szAuthStr[MAXAUTHSTR+1] = "";
SQLINTEGER strlen1;
SQLINTEGER a;
int i,sign =1;
long myvalue, divisor;
float final_val;

	
// Allocate the Environment handle. Set the Env attribute, allocate the //connection handle, connect to the database and allocate the statement //handle.

retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
retcode = SQLConnect(hdbc1, szDSN,SQL_NTS,szUID,SQL_NTS,szAuthStr,SQL_NTS);
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);

// Execute the select statement. Here it is assumed that numeric_test
//table is created using the following statements:

// Create table numeric_test (col1 numeric(5,3))
//insert into numeric_test values (25.212)

retcode = SQLExecDirect(hstmt1,(UCHAR *)"select * from numeric_test",SQL_NTS);

// Use SQLBindCol to bind the NumStr to the column that is being retrieved.

retcode = SQLBindCol(hstmt1,1,SQL_C_NUMERIC,&NumStr,19,&strlen1);

// Get the application row descriptor for the statement handle using
//SQLGetStmtAttr.

retcode = SQLGetStmtAttr(hstmt1, SQL_ATTR_APP_ROW_DESC,&hdesc, 0, NULL);

// You can either use SQLSetDescRec or SQLSetDescField when using
// SQLBindCol. However, if you prefer to call SQLGetData, you have to
// call SQLSetDescField instead of SQLSetDescRec. For more information on
// descriptors, please refer to the ODBC 3.0 Programmers reference or
// your Online documentation.

//Used when using SQLSetDescRec
//a=b=sizeof(NumStr);

// Set the datatype, precision and scale fields of the descriptor for the 
//numeric column. Otherwise the default precision (driver defined) and 
//scale (0) are returned.

// In this case, the table contains only one column, hence the second 
//parameter contains one. Zero applies to bookmark columns. Please check 
//the programmers guide for more information.

//retcode=SQLSetDescRec(hdesc,1,SQL_NUMERIC,NULL,sizeof(NumStr),5,3,&NumStr,&a,&b);

 retcode = SQLSetDescField (hdesc,1,SQL_DESC_TYPE,(VOID*)SQL_C_NUMERIC,0);
 retcode = SQLSetDescField (hdesc,1,SQL_DESC_PRECISION,(VOID*) 5,0);
 retcode = SQLSetDescField (hdesc,1,SQL_DESC_SCALE,(VOID*) 3,0);
 	
// Initialize the val array in the numeric structure.

memset(NumStr.val,0,16);
	
// Call SQLFetch to fetch the first record.

while((retcode =SQLFetch(hstmt1)) != SQL_NO_DATA)
  {
// Notice that the TargetType (3rd Parameter) is SQL_ARD_TYPE, which  
//forces the driver to use the Application Row Descriptor with the 
//specified scale and precision.

   retcode = SQLGetData(hstmt1, 1, SQL_ARD_TYPE, &NumStr, 19, &a) ; 

// Call to convert the little endian mode data into numeric data.

   myvalue = strtohextoval();

// The returned value in the above code is scaled to the value specified
//in the scale field of the numeric structure. For example 25.212 would
//be returned as 25212. The scale in this case is 3 hence the integer 
//value needs to be divided by 1000.

divisor = 1;
   if(NumStr.scale > 0)
     {
	 for (i=0;i< NumStr.scale; i++)	
         divisor = divisor * 10;
     }
   final_val =  (float) myvalue /(float) divisor;

// Examine the sign value returned in the sign field for the numeric
//structure.
//NOTE: The ODBC 3.0 spec required drivers to return the sign as 
//1 for positive numbers and 2 for negative number. This was changed in the
//ODBC 3.5 spec to return 0 for negative instead of 2.

        if(!NumStr.sign) sign = -1;
	   else sign  =1;

	final_val *= sign;
	printf("The final value: %f\n",final_val);
    }


	while ( ( retcode = SQLMoreResults(hstmt1) ) != SQL_NO_DATA_FOUND)
		;

	/* clean up */ 
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
	SQLDisconnect(hdbc1);
	SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
	SQLFreeHandle(SQL_HANDLE_ENV, henv);
	return(0);

} 

//  C   ==> 12 * 1    =     12
//  7   ==> 07 * 16   =    112
//  2   ==> 02 * 256  =    512
//  6   ==> 06 * 4096 =  24576
=================================
                  Sum =  25212 

// Code that implements the conversion from little endian mode to the
//scaled integer. 

// Please note that it is up to the application developer to implement this
//functionality. The example here is just one of the many possible ways.

long strtohextoval()
{
    long val=0,value=0;
    int i=1,last=1,current;
    int a=0,b=0;

        for(i=0;i<=15;i++)
             {
		        current = (int) NumStr.val[i];
			a= current % 16; //Obtain LSD
			b= current / 16; //Obtain MSD
				
			value += last* a;	
			last = last * 16;	
			value += last* b;
			last = last * 16;	
  		}
 	 return value;
} 

Additional query words:

Keywords : kbDatabase kbODBC odbcSQL
Version : WINDOWS:3.7
Platform : WINDOWS
Issue type : kbhowto