DOCUMENT:Q181254  27-AUG-1999  [odbc]
TITLE   :INF: How to Use SQL_C_NUMERIC Data Type with Numeric Data
PRODUCT :Open Database Connectivity (ODBC)
PROD/VER:WINDOWS:3.0
OPER/SYS:
KEYWORDS:

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Open Database Connectivity, version 3.0 
-------------------------------------------------------------------------------

SUMMARY
=======

ODBC 3.0 introduces a new ODBC C data type, identified by SQL_C_NUMERIC, which
allows applications to directly handle numeric data. The C buffer used has the
type definition of SQL_NUMERIC_STRUCT, and is a structure with 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.

Additional information regarding the format and use of SQL_NUMERIC_STRUCT is
available in the ODBC 3.0 Programmer's Reference, in Appendix D (Data Types) in
the "C Data Types" section.

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
express or implied, including but not limited to the implied warranties of
merchantability and/or fitness for a particular purpose.

The following sample program illustrates the use of SQL_C_NUMERIC by inserting
123.45 into a table defined with a column whose data type is numeric or decimal,
precision 5 and scale 2. Comments are embedded in the code to explain the key
points. The ODBC driver used 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);
   }

Additional query words:

======================================================================
Keywords          :  
Technology        : kbAudDeveloper kbODBCSearch kbODBC300
Version           : WINDOWS:3.0
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 1999.