Retrieve numeric data with SQL_NUMERIC_STRUCT
This article describes how to retrieve numeric data from the SQL Server ODBC driver into a numeric structure. It also describes how to get the correct values using specific precision and scale values.
This data type allows applications to directly handle numeric data. Around the year 2003, ODBC 3.0 introduced a new ODBC C data type, identified by SQL_C_NUMERIC. This data type is still relevant as of 2017.
The C buffer that is used has the type definition of SQL_NUMERIC_STRUCT. This structure has fields for storing the precision, scale, sign, and value of the numeric data. The value itself is stored as a scaled integer with the least significant byte beginning in the leftmost position.
The article C Data Types provides more information about the format and use of SQL_NUMERIC_STRUCT. Generally the Appendix D of the ODBC 3.0 Programmer's Reference discusses data types.
SQL_NUMERIC_STRUCT overview
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.
SQL_NUMERIC_STRUCT code sample
This code sample shows you how to:
- Set the precision.
- Set the scale.
- Retrieve the correct values.
Note
ANY USE BY YOU OF THE CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK.
Microsoft provides these code samples "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);
}
Interim results:
// C ==> 12 * 1 = 12
// 7 ==> 07 * 16 = 112
// 2 ==> 02 * 256 = 512
// 6 ==> 06 * 4096 = 24576
===============================
Sum = 25212
In the numeric structure, the val field is a character array of 16 elements. For example, 25.212 is scaled to 25212 and the scale is 3. In hexadecimal format this number would be 627C.
The driver returns the following items:
- The equivalent character of 7C, which is '|'(pipe) in the first element of the character array.
- The equivalent of 62, which is 'b' in the second element.
- The remainders of the array elements contain zeroes, so the buffer contains '|b\0'.
Now 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. It is up to the application developer to implement this functionality. The following code example 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;
}
Applies to versions
The preceding information about SQL_NUMERIC_STRUCT applies to the following product versions:
- 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
SQL_C_NUMERIC overview
The following sample program illustrates the use of SQL_C_NUMERIC, by inserting 123.45 into a table. In the table, the column is defined as a numeric or a decimal, with precision 5, and with scale 2.
The ODBC driver you use to run this program must support ODBC 3.0 functionality.
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
void main() {
SQLHENV henv = NULL;
SQLHDBC hdbc = NULL;
SQLHSTMT hstmt = NULL;
SQL_NUMERIC_STRUCT NumStr;
SQLINTEGER cbNumStr = sizeof (NumStr);
SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);
/* Set the ODBC behavior version. */
SQLSetEnvAttr(henv,
SQL_ATTR_ODBC_VERSION,
(SQLPOINTER) SQL_OV_ODBC3,
SQL_IS_INTEGER);
SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
/* Substitute your own connection information */
SQLConnect(hdbc,
(SQLCHAR *) "MyDSN", 5,
(SQLCHAR *) "UserID", 6,
(SQLCHAR *) "Password", 8);
SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
/*
Set up the SQL_NUMERIC_STRUCT, NumStr, to hold "123.45".
First, we need to scale 123.45 to an integer: 12345
One way to switch the bytes is to convert 12345 to Hex: 0x3039
Since the least significant byte will be stored starting from the
leftmost byte, "0x3039" will be stored as "0x3930".
The precision and scale fields are not used for input to the driver,
only for output from the driver. The precision and scale will be set
in the application parameter descriptor later.
*/
NumStr.sign = 1; /* 1 if positive, 2 if negative */
memset (NumStr.val, 0, 16);
NumStr.val [0] = 0x39;
NumStr.val [1] = 0x30;
/* SQLBindParameter needs to be called before SQLSetDescField */
SQLBindParameter(hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_NUMERIC,
SQL_NUMERIC,
5,
2,
&NumStr,
0,
(SQLINTEGER *) &cbNumStr);
/* Modify the fields in the implicit application parameter descriptor */
SQLHDESC hdesc = NULL;
SQLGetStmtAttr(hstmt, SQL_ATTR_APP_PARAM_DESC, &hdesc, 0, NULL);
SQLSetDescField(hdesc, 1, SQL_DESC_TYPE, (SQLPOINTER) SQL_C_NUMERIC, 0);
SQLSetDescField(hdesc, 1, SQL_DESC_PRECISION, (SQLPOINTER) 5, 0);
SQLSetDescField(hdesc, 1, SQL_DESC_SCALE, (SQLPOINTER) 2, 0);
SQLSetDescField(hdesc, 1, SQL_DESC_DATA_PTR, (SQLPOINTER) &NumStr, 0);
SQLExecDirect(hstmt,
(SQLCHAR *) "INSERT INTO table (numeric_column) VALUES (?)",
SQL_NTS);
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
SQLDisconnect (hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, henv);
}