#include<windows.h>#include<stdio.h>#include<stdlib.h>#include<string.h>#include <sql.h>#include <sqlext.h>#include <iostream>#include <odbcinst.h>using namespace std;char hosts[] = {"192.168.100.54"};#define isSuc(result) ((result) == SQL_SUCCESS || (result) == SQL_SUCCESS_WITH_INFO)int main(void){short sret; //返回代码HENV henv; //环境句柄HDBC hdbc; //连接句柄HSTMT hstmt;//sql语句执行句柄，相当于游标//动态创建系统DSN数据源，需要包含odbcinst.h，SQL Server不需要添加用户名密码//在使用某个数据库的ODBC管理器之前，首先要查看是否安装了这个数据库的ODBC驱动，如果没有先安装再使用，比如DB2就没有默认安装 //SQLConfigDataSource(NULL,ODBC_ADD_SYS_DSN,"SQL Server","DSN=trantest\0 UID=sa\0 PWD=123456\0 SERVER=192.168.100.54\0 DATABASE=warehouse\0");bool sucess = SQLConfigDataSource(NULL,ODBC_ADD_SYS_DSN,"SQL Server","DSN=trantest\0 SERVER=192.168.100.54\0 DATABASE=warehouse\0");if (sucess)printf("DSN created sucessfully!!\n");elseexit(0);/** 申请环境句柄**/sret = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&henv);if(!isSuc(sret)){printf("申请环境句柄出错\n");SQLFreeHandle(SQL_HANDLE_ENV, henv);}/** 设置环境属性,注册ODBC版本**/sret = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(void*)SQL_OV_ODBC3,0);if(!isSuc(sret)){printf("注册ODBC版本出错\n");}/** 申请连接句柄**/sret = SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);if(!isSuc(sret)){printf("申请连接句柄出错\n");SQLFreeHandle(SQL_HANDLE_DBC, hdbc);}/** 设置连接属性,此处设置为手动提交**/sret = SQLSetConnectAttr(hdbc,SQL_ATTR_AUTOCOMMIT,(void*)SQL_AUTOCOMMIT_OFF,SQL_IS_POINTER);if(!isSuc(sret))printf("设置连接属性出错\n");/** 连接数据源，这里的数据源使用的是动态创建的数据源**/sret = SQLConnect(hdbc,(SQLCHAR *)"trantest",SQL_NTS,(SQLCHAR *)"sa",SQL_NTS,(SQLCHAR *)"123456",SQL_NTS); if(!isSuc(sret))printf("连接%s出错\n", hosts);else printf("连接%s成功\n", hosts);/** 申请执行语句句柄**/sret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);if(!isSuc(sret)){printf("申请执行语句句柄时出错\n");SQLFreeHandle(SQL_HANDLE_STMT, hstmt);}//记录更新语句影响的行数SQLINTEGER row_count;SQLExecDirect(hstmt,(SQLCHAR*)"insert into odbctest values(100,'name','2011-11-30')",SQL_NTS);//执行sql语句后影响的行数，select查询语句返回-1SQLRowCount(hstmt,&row_count);printf("affcted count is: %d\n",row_count);SQLExecDirect(hstmt,(SQLCHAR*)"insert into odbctest values(200,'name','2011-11-30')",SQL_NTS);SQLRowCount(hstmt,&row_count);printf("affcted count is: %d\n",row_count);SQLExecDirect(hstmt,(SQLCHAR*)"delete from odbctest where a=200",SQL_NTS);SQLRowCount(hstmt,&row_count);printf("affcted count is: %d\n",row_count);//对更新类的sql执行后进行提交或者回滚sret = SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_COMMIT);//sret = SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_ROLLBACK);if (sret != SQL_SUCCESS)printf("服务器%s执行查询出错\n", hosts);elseprintf("服务器%s执行查询成功\n", hosts);SQLExecDirect(hstmt,(SQLCHAR*)"select * from odbctest",SQL_NTS);int set_num = 0;SQLINTEGER ID;SQLSMALLINT col_num;SQLCHAR szId[50],szName[50],szTime[50];SQLINTEGER cbID,cbName,cbTime;//得到结果集中列数SQLNumResultCols(hstmt,&col_num);printf("column number is: %d\n",col_num);//要遍历所有的结果集可以利用下面的方法：while(SQL_NO_DATA != SQLFetch(hstmt)) //移动光标，一直到集合末尾{//得到光标处某列的值SQLGetData(hstmt,1,SQL_C_ULONG,&ID,0,&cbID);SQLGetData(hstmt,2,SQL_C_CHAR,szName,50,&cbName);SQLGetData(hstmt,3,SQL_C_CHAR,szTime,50,&cbTime);//有与ODBC没有MoveNext和MoveLast API所以这样获取数据集行数set_num++;printf("%d, %s, %s\n",ID,szName,szTime);}printf("the numbers of set is: %d\n",set_num);/** 释放各个句柄，注意顺序，不要改变**/SQLFreeHandle(SQL_HANDLE_STMT, hstmt);SQLDisconnect(hdbc);SQLFreeHandle(SQL_HANDLE_DBC, hdbc);SQLFreeHandle(SQL_HANDLE_ENV, henv);return 0;}
