ODBC连接SQL SERVER数据库

#include <windows.h>#include <stdio.h>#include <stdlib.h>#include <string.h>#include <sql.h>#include <sqlext.h>#include <iostream>#include <odbcinst.h>#include <cstring>using namespace std;//char hosts[] = {"192.168.100.54"};#define isSuc(result) ((result) == SQL_SUCCESS || (result) == SQL_SUCCESS_WITH_INFO)#define istr(a,b) (b?0:(a) == L"te" || (a) == L"ha")#define nm L"te"#define nm_1 L"hj"#define nm_2 L"tee"int main(void){short sret; //返回代码HENV henv;  //环境句柄HDBC hdbc;  //连接句柄HSTMT hstmt;//sql语句执行句柄，相当于游标if (ISSBDevice(L"SB")){printf("te");}else{printf("tee");}if (istr(nm,TRUE)){printf("ok");}if (istr(nm,FALSE)){printf("ok2");}if (istr(nm_1,FALSE)){printf("ok3");}//动态创建系统DSN数据源，需要包含odbcinst.h，SQL Server不需要添加用户名密码//在使用某个数据库的ODBC管理器之前，首先要查看是否安装了这个数据库的ODBC驱动，如果没有先安装再使用，比如DB2就没有默认安装    //bool secess = 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=SK\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 *)"1234567",SQL_NTS); if(!isSuc(sret))printf("连接出错\n");else printf("连接成功\n");/** 申请执行语句句柄**/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 t_test 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 t_test 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 t_test where id=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("服务器执行查询出错\n");elseprintf("服务器执行查询成功\n");SQLExecDirect(hstmt,(SQLCHAR*)"select * from t_test",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);SQLExecDirect(hstmt,(SQLCHAR*)"delete from t_test where id=200",SQL_NTS);SQLRowCount(hstmt,&row_count);printf("affcted count is: %d\n",row_count);sret = SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_COMMIT);//查询必须要要这一句SQLExecDirect(hstmt,(SQLCHAR*)"delete from t_test",SQL_NTS);SQLRowCount(hstmt,&row_count);printf("affcted count is: %d\n",row_count);SQLExecDirect(hstmt,(SQLCHAR*)"select * from t_test",SQL_NTS);//要遍历所有的结果集可以利用下面的方法：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);SQLExecDirect(hstmt,(SQLCHAR*)"delete from t_test",SQL_NTS);SQLRowCount(hstmt,&row_count);printf("affcted count is: %d\n",row_count);/** 释放各个句柄，注意顺序，不要改变**/SQLFreeHandle(SQL_HANDLE_STMT, hstmt);SQLDisconnect(hdbc);SQLFreeHandle(SQL_HANDLE_DBC, hdbc);SQLFreeHandle(SQL_HANDLE_ENV, henv);char ch;scanf("%c",&ch);return 0;}