compute.cpp

关于ODBC的操作接口的使用例子

/***********************************************************************
Copyright (c) 2000, Microsoft Corporation
All Rights Reserved.
***********************************************************************/
// Compute.cpp -- Use ODBC to execute a Transact-SQL SELECT statement
// containing a COMPUTE clause.
//
// Illustrates fast retrieve on a default ODBC statement handle and
// determining the properties of a result set using SQL Server ODBC
// driver-specific column attributes.
//
// This file is part of Microsoft SQL Server online documentation.
//
// This source code is an intended supplement to the Microsoft SQL
// Server online references and related electronic documentation.
#include <windows.h>
#include <stdio.h>
#include <tchar.h>

#include "sql.h"
#include "sqlext.h"
#include "odbcss.h"

// Macros
#define NUMROWS_CHUNK               20
#define CBCOLNAME_MAX               64

// Types
typedef struct tagDRIVERBYLIST
    {
    SQLSMALLINT     nBys;
    SQLSMALLINT     aByList[1];
    } DRIVERBYLIST;
typedef DRIVERBYLIST* PDRIVERBYLIST;

typedef struct tagLOCALBYLIST
    {
    SQLSMALLINT     nBys;
    SQLSMALLINT*    pBys;
    } LOCALBYLIST;
typedef LOCALBYLIST* PLOCALBYLIST;

typedef struct tagODBCCOLINFO
    {
    SQLTCHAR        szColName[CBCOLNAME_MAX + 1];
    SQLSMALLINT     cbColName;
    SQLSMALLINT     fSQLType;
    SQLUINTEGER     cbColData;
    SQLSMALLINT     cbScale;
    SQLSMALLINT     fNullable;
    SQLSMALLINT     fBindType;
    UINT            obValue;
    UINT            obIndicator;
    } ODBCCOLINFO;
typedef ODBCCOLINFO*    PODBCCOLINFO;

typedef struct tagODBCSETINFO
    {
    SQLUSMALLINT    nCols;
    SQLINTEGER      nRows;
    SQLINTEGER      cbResultSet;
    PODBCCOLINFO    pODBCColInfo;
    PBYTE           pRows;
    PLOCALBYLIST    pByList;
    } ODBCSETINFO;
typedef ODBCSETINFO*    PODBCSETINFO;

// Function prototypes
SQLRETURN GetColumnsInfo(SQLHSTMT hstmt, SWORD nCols, ODBCCOLINFO** ppODBCColInfo);
SQLRETURN BindCols(SQLHSTMT hstmt, SQLUSMALLINT nCols, PODBCCOLINFO pODBCColInfo,
                   PBYTE pRows);
void CreateDBBindings(PODBCSETINFO pODBCSetInfo);
void GetData(SQLHSTMT hstmt);

void DumpError(PTSTR pErrorText);
void DumpError(SQLSMALLINT eHandleType, SQLHANDLE hodbc);

// AdjustLen supports binding on four-byte boundaries.
_inline SQLUINTEGER AdjustLen(SQLUINTEGER cb)
    {
    return ((cb + 3) & ~3);
    }

int main()
    {
    // ODBC handles
    SQLHENV     henv = NULL;
    SQLHDBC     hdbc = NULL;
    SQLHSTMT    hstmt = NULL;

 	//These must be modified for your specific DSN
    PTSTR       szDataSource = _T("MyDatasource");
    PTSTR       szUID = _T("MyUID");
    PTSTR       szPWD = _T("MyPWD");

	PTSTR       szSQLSelect = 
        _T("SELECT")
        _T(" O.EmployeeID, O.OrderID,")
        _T(" FullPrice = (UnitPrice * Quantity), Discount,")
        _T(" Discounted = UnitPrice * (1 - Discount) * Quantity")
        _T(" FROM Orders O, [Order Details] OD")
        _T(" WHERE O.OrderID = OD.OrderID")
        _T(" ORDER BY EmployeeID, O.OrderID")
        _T(" COMPUTE")
        _T(" SUM(UnitPrice * Quantity),")
        _T(" SUM(UnitPrice * (1 - Discount) * Quantity)")
        _T(" BY EmployeeID, O.OrderID")
        _T(" COMPUTE")
        _T(" SUM(UnitPrice * Quantity),")
        _T(" SUM(UnitPrice * (1 - Discount) * Quantity)")
        _T(" BY EmployeeID")
        _T(" COMPUTE")
        _T(" SUM(UnitPrice * Quantity),")
        _T(" SUM(UnitPrice * (1 - Discount) * Quantity)");

    // Initialize the ODBC environment.
    if (SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv) == SQL_ERROR)
        goto EXIT;
    SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*) SQL_OV_ODBC3,
        SQL_IS_INTEGER);

    // Allocate a connection handle and connect to the data source.
    if (SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc) == SQL_ERROR)
        {
        DumpError(_T("AllocHandle on DBC failed."));
        goto EXIT;
        }
    if (SQLConnect(hdbc, (SQLTCHAR*) szDataSource, SQL_NTS,
        (SQLTCHAR*) szUID, SQL_NTS, (SQLTCHAR*) szPWD, SQL_NTS) == SQL_ERROR)
        {
        DumpError(SQL_HANDLE_DBC, hdbc);
        goto EXIT;
        }

    // Get a statement handle and execute a Transact-SQL SELECT statement
    //  containing a COMPUTE clause.
    if (SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt) == SQL_ERROR)
        {
        DumpError(SQL_HANDLE_DBC, hdbc);
        goto EXIT;
        }
    if (SQLExecDirect(hstmt, (SQLTCHAR*) szSQLSelect, SQL_NTS) == SQL_ERROR)
        {
        DumpError(SQL_HANDLE_STMT, hstmt);
        goto EXIT;
        }

    // Retrieve data from multiple result sets.
    GetData(hstmt);

EXIT:
    if (hstmt != NULL)
        {
        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
        }

    if (hdbc != NULL)
        {
        SQLDisconnect(hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
        }

    if (henv != NULL)
        {
        SQLFreeHandle(SQL_HANDLE_ENV, henv);
        }

    return (0);
    }

// GetData(...) -- retrieve data for all result sets.
void GetData ( SQLHSTMT hstmt ) 
    {
    SQLUSMALLINT    nCols;
    SQLUSMALLINT    nCol;
    PODBCSETINFO    pODBCSetInfo = NULL;
    SQLRETURN       sRet;
    SQLINTEGER      nRowsFetched = 0;
    UINT            nRow;
    SQLINTEGER      nComputes = 0;
    SQLINTEGER      nSets = 1;
    SQLINTEGER      nSet;
    BYTE*           pValue;
    SQLINTEGER*     pIndicator;

    // If SQLNumResultCols failed, then some error occured in statement
    //  execution. Exit.
    if (!SQL_SUCCEEDED(sRet = SQLNumResultCols(hstmt, (SQLSMALLINT*) &nCols)))
        {
        DumpError(SQL_HANDLE_STMT, hstmt);
        goto EXIT;
        }

    // If SQLNumResultCols succeeded, but no columns are indicated, then
    //  the statement did not return a results set.
    if (nCols == 0)
        {
        DumpError(_T("Invalid statement."));
        goto EXIT;
        }

    // Determine the presence of COMPUTE clause result sets. The SQL Server
    //  driver uses column attributes to report multiple sets.
    SQLColAttribute(hstmt, 1, SQL_CA_SS_NUM_COMPUTES,
        NULL, 0, NULL, (SQLPOINTER) &nComputes);

    // The number of result sets is 1 (for the normal rows) + nComputes.
    nSets += nComputes;

    // Create a column info structure pointer array, one element for each
    //  result set.
    pODBCSetInfo = new ODBCSETINFO[1 + nComputes];
    for (nSet = 0; nSet < 1 + nComputes; nSet++)
        {
        pODBCSetInfo[nSet].nCols = 0;
        pODBCSetInfo[nSet].nRows = 1;
        pODBCSetInfo[nSet].cbResultSet = 0;
        pODBCSetInfo[nSet].pODBCColInfo = NULL;
        pODBCSetInfo[nSet].pRows = NULL;
        pODBCSetInfo[nSet].pByList = NULL;
        }

    // Set up info structure for normal result set. The normal result set
    //  can contain multiple rows. All COMPUTE clause sets will have only
    //  a single row. We can optimize retrieval of rows for the SQL Server
    //  driver by using row array binding for the normal set (0).
    pODBCSetInfo[0].nCols = nCols;
    pODBCSetInfo[0].nRows = NUMROWS_CHUNK;
    nSet = 0;

    while (TRUE)
        {
        // If required, get the column information for the result set.
        if (pODBCSetInfo[nSet].pODBCColInfo == NULL)
            {
            if (pODBCSetInfo[nSet].nCols == 0)
                {
                SQLNumResultCols(hstmt, (SQLSMALLINT*) &nCols);
                pODBCSetInfo[nSet].nCols = nCols;
                }

            if (GetColumnsInfo(hstmt, pODBCSetInfo[nSet].nCols,
                &(pODBCSetInfo[nSet].pODBCColInfo)) == SQL_ERROR)
                {
                goto EXIT;
                }
            }

        // If this is a COMPUTE clause result sets, get the ordering columns
        //  (if any) for the set and display them.
        if (nSet > 0)
            {
            SQLSMALLINT     nBy;
            SQLSMALLINT     nBys;
            PLOCALBYLIST    pLocalByList;

            if (pODBCSetInfo[nSet].pByList == NULL)
                {
                PDRIVERBYLIST  pDriverByList;
                pLocalByList = new LOCALBYLIST;

                SQLColAttribute(hstmt, 1, SQL_CA_SS_COMPUTE_BYLIST, NULL,
                    0, NULL, (SQLPOINTER) &pDriverByList);

                if (pDriverByList)
                    {
                    pLocalByList->nBys = pDriverByList->nBys;
                    nBys = pLocalByList->nBys;
                    pLocalByList->pBys = new SQLSMALLINT[nBys];
                    for (nBy = 0; nBy < nBys; nBy++)
                        {
                        pLocalByList->pBys[nBy] = 
                            pDriverByList->aByList[nBy]; 
                        }
                    }
                else
                    {
                    nBys = pLocalByList->nBys = 0;
                    pLocalByList->pBys = NULL;
                    }

                pODBCSetInfo[nSet].pByList = pLocalByList;
                }
            else
                {
                pLocalByList = pODBCSetInfo[nSet].pByList;
                nBys = pLocalByList->nBys;
                }

            for (nBy = 0; nBy < nBys; nBy++)
                {
                _tprintf(_T("This compute clause ordered by columns: "));
                for (nBy = 0; nBy < pLocalByList->nBys; )
                    {
                    _tprintf(_T("%u"), pLocalByList->pBys[nBy]);
                    nBy++;

                    if (nBy == pLocalByList->nBys)
                        {
                        _tprintf(_T("\n"));
                        }
                    else
                        {
                        _tprintf(_T(", "));
                        }