编程示例

此示例显示使用描述符拷贝实现表和表之间的数据复制。示例在表SOURCE上执行SELECT，然后用获取的数据作为执行参数在表TARGET上执行INSERT。

表SOURCE和TARGET的结构如下：

//CREATE TABLE SOURCE (ID int, UserName char(64));
//CREATE TABLE TARGET (ID int, UserName char(64));
#include "stdafx.h"
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <sqltypes.h>
#include <odbcss.h>
#include <stdio.h>
#define MAX_STR_LEN 64 + 1
#define ROW_ARRAY_SIZE 10
typedef struct tagUserStruct {
    SQLINTEGER ID;
    SQLINTEGER IDInd;
    SQLCHAR UserName[MAX_STR_LEN];
    SQLINTEGER UserNameInd;
}UserStruct;
UserStruct UserArray[ROW_ARRAY_SIZE];
SQLUSMALLINT StatusArray[ROW_ARRAY_SIZE];
RETCODE retcode;
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc1 = SQL_NULL_HDBC;
SQLHSTMT hstmt0 = SQL_NULL_HSTMT,hstmt1 = SQL_NULL_HSTMT;
SQLCHAR errmsg[10];
SQLINTEGER errint;
SQLSMALLINT errind;
SQLCHAR errdesc[512];
int errLineNum = 0 ;
SQLHDESC hArd0, hIrd0, hApd1, hIpd1;
int main()
{
// 分配ODBC环境句柄
    retcode = SQLAllocHandle (SQL_HANDLE_ENV, NULL, &henv);
// 指定应用程序ODBC版本
    retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,(SQLPOINTER)
                            SQL_OV_ODBC3, SQL_IS_INTEGER);
// 分配ODBC连接句柄并连接
    retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
    retcode = SQLConnect(hdbc1,(SQLCHAR*)"OSRDBSource",
                         SQL_NTS,(SQLCHAR*)"sysdba", SQL_NTS,
                         (SQLCHAR*)"szoscar55",SQL_NTS);
// 分配语句句柄
    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt0);
    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc1, &hstmt1);
// hstmt0的ARD 和IRD
    retcode = SQLGetStmtAttr(hstmt0, SQL_ATTR_APP_ROW_DESC, &hArd0, 0,
                             NULL);
    retcode = SQLGetStmtAttr(hstmt0, SQL_ATTR_IMP_ROW_DESC, &hIrd0, 0,
                             NULL);
// hstmt1的APD和IPD
    retcode = SQLGetStmtAttr(hstmt1, SQL_ATTR_APP_PARAM_DESC, &hApd1, 0,
                             NULL);
    retcode = SQLGetStmtAttr(hstmt1, SQL_ATTR_IMP_PARAM_DESC, &hIpd1, 0,
                             NULL);
// 设置hstmt0的结果集绑定模式
// 设置hstmt0的行集大小
    retcode = SQLSetStmtAttr(hstmt0, SQL_ATTR_ROW_BIND_TYPE,
                             (SQLPOINTER)sizeof(UserStruct), 0);
    retcode = SQLSetStmtAttr(hstmt0, SQL_ATTR_ROW_ARRAY_SIZE,
                             (SQLPOINTER)ROW_ARRAY_SIZE, 0);
// 绑定hstmt0的结果集
    retcode = SQLBindCol(hstmt0, 1, SQL_C_SLONG,
                         (SQLPOINTER)&UserArray[0].ID, 0, &UserArray[0].IDInd);
    retcode = SQLBindCol(hstmt0, 2, SQL_C_CHAR, UserArray[0].UserName,
                         MAX_STR_LEN, &UserArray[0].UserNameInd);
// 执行SELECT查询
    retcode = SQLExecDirect(hstmt0, (SQLCHAR*)"SELECT ID, USERNAME FROM
                            SOURCE",SQL_NTS);
// 用拷贝的方式绑定hstmt1
    retcode = SQLCopyDesc(hArd0, hApd1);
    retcode = SQLCopyDesc(hIrd0, hIpd1);
// 设置hstmt0的行集状态矩阵和hstmt1的参数状态矩阵为同一个矩阵
    retcode = SQLSetStmtAttr(hstmt0, SQL_ATTR_ROW_STATUS_PTR,
                             StatusArray, SQL_IS_POINTER);
    retcode = SQLSetStmtAttr(hstmt1, SQL_ATTR_PARAM_OPERATION_PTR,
                             StatusArray, SQL_IS_POINTER);
// 准备执行插入语句
    retcode = SQLPrepare(hstmt1, (SQLCHAR*)"INSERT INTO TARGET VALUES
                         (?,?)", SQL_NTS);
// 循环：获取一个行集，然后用获取的数据作为参数执行插入
    while (SQLFetchScroll(hstmt0, SQL_FETCH_NEXT, 0) != SQL_NO_DATA)
    {
        retcode = SQLExecute(hstmt1);
        if (SQL_NO_DATA!=SQLGetDiagRec(SQL_HANDLE_STMT, hstmt1, ++errLineNum,
                                       errmsg, &errint, (SQLCHAR *)errdesc, (SQLSMALLINT)500, &errind))
        {
            printf("%s: %sn", errmsg, errdesc);
            printf("n");
            char c =getchar();
        }
    } // while
// 释放语句句柄
// 断开连接
// 释放连接句柄和环境句柄
    SQLFreeHandle(SQL_HANDLE_STMT, hstmt1);
    SQLDisconnect(hdbc1);
    SQLFreeHandle(SQL_HANDLE_DBC, hdbc1);
    SQLFreeHandle(SQL_HANDLE_ENV, henv);
    return 0;
}