Microsoft KB Archive/222831

= HOWTO: Retrieving Numeric Data with SQL_NUMERIC_STRUCT =

Article ID: 222831

Article Last Modified on 12/5/2003

-

APPLIES TO


 * 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

-



This article was previously published under Q222831



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:

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;
 * 1) define SQL_MAX_NUMERIC_LEN    16

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.



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.


 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 
 * 5) include 
 * 6) include 
 * 7) include 
 * 8) include 


 * 1) define MAXDSN     25
 * 2) define MAXUID     25
 * 3) define MAXAUTHSTR 25
 * 4) 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) ;

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

Keywords: kbhowto kbdatabase KB222831

-

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

© Microsoft Corporation. All rights reserved.