Q222831: HOWTO: Retrieving Numeric Data with SQL_NUMERIC_STRUCT
Article: Q222831
Product(s): Open Database Connectivity (ODBC)
Version(s): 2.1,2.5,2.6,3.7
Operating System(s):
Keyword(s): kbDatabase kbODBC kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC250 kbMDAC260 kbmdac270
Last Modified: 23-AUG-2001
-------------------------------------------------------------------------------
The information in this article applies to:
- Microsoft ODBC Driver for SQL Server, version 3.7
- Microsoft Data Access Components versions 2.1, 2.5, 2.6, 2.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;
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.
#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) ;
// 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;
}
Additional query words:
======================================================================
Keywords : kbDatabase kbODBC kbGrpDSVCDB kbGrpDSMDAC kbDSupport kbMDAC250 kbMDAC260 kbmdac270
Technology : kbSQLServSearch kbAudDeveloper kbODBCSearch kbMDACSearch kbMDAC210 kbMDAC250 kbMDAC260 kbODBCSQLServ370 kbMDAC270
Version : :2.1,2.5,2.6,3.7
Issue type : kbhowto
=============================================================================
THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.
Copyright Microsoft Corporation 1986-2002.