You are currently offline, waiting for your internet to reconnect

HOWTO: Retrieving Numeric Data with SQL_NUMERIC_STRUCT

This article was previously published under Q222831
This article has been archived. It is offered "as is" and will no longer be updated.
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		16typedef 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;				

The precision and scale fields of the numeric structure are never used for input from an application, only for output from the driver to the application.

The driver uses the default precision (driver-defined) and default scale (0) whenever returning data to the application. Unless the application specifies values for precision and scale, the driver assumes the default and truncates the decimal portion of the numeric data.

This article shows you how to set the precision, scale, and how to retrieve the correct values.

Microsoft provides this code "as is" without warranty of any kind,either expressed or implied, including but not limited to the impliedwarranties 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	255SQLHENV	        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: passwordUCHAR 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) ; // Check for null indicator.   if ( SQL_NULL_DATA == a )   {   printf( "The final value: NULL\n" );   continue;   }// 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				

The val field in the numeric structure is a character array of 16 elements. For example, 25.212 is scaled to 25212 and the scale is 3. This corresponds to 627C in the hexadecimal format.

The driver returns the equivalent character of 7C which is '|'(pipe) in the first element of the character array, equivalent of 62 which is 'b' in the second element and the rest of the array elements contain zeroes. So the buffer contains '|b\0'.

The challenge is to construct the scaled integer out of this string array. Each character in the string corresponds to two hexadecimal digits, say least significant digit (LSD) and most significant digit (MSD). The scaled integer value could be generated by multiplying each digit (LSD & MSD) with a multiple of 16 starting with 1.

// 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;}				

Article ID: 222831 - Last Review: 12/05/2015 13:27:35 - Revision: 4.1

  • Microsoft ODBC Driver for Microsoft SQL Server 3.7
  • Microsoft Data Access Components 2.1
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
  • kbnosurvey kbarchive kbdatabase kbhowto KB222831
  • © 2015 Microsoft