C/C++通过ODBC连接SqlServer进行查询、插入与删除

C/C++通过ODBC连接SqlServer进行查询、插入与删除
需要注意不同编译器，尤其在VC6.0和VS2013字符类型支持方式有所不同，而在Dev-C++则直接无法连接数据库。

• VC6.0版本
• VS2013或更高版本
• Dev-C++ 5.11 编译器通过ODBC访问数据库情况

VC6.0环境下可使用如下示例：
SqlServer数据库中测试样例可具体参看另一篇文章:
C/C++连接SqlServer数据库

//VC6.0环境
#include<iostream>
#include<string>
#include<windows.h>
#include<sql.h>
#include<stdio.h>
#include<windows.h>
#include<sql.h>
#include<sqlext.h>
#include<sqltypes.h>
using namespace std;
SQLRETURN ret;
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
//错误处理
void handleResult(SQLHANDLE hHandle,SQLSMALLINT hType,RETCODE  RetCode) {
	SQLSMALLINT iRec = 0;
	SQLINTEGER iError;
	WCHAR wszMessage[1000];
	WCHAR wszState[SQL_SQLSTATE_SIZE + 1];
	//处理无效
	if (RetCode == SQL_INVALID_HANDLE) {
		fwprintf(stderr, L"Invalid handle!\n");
		return;
	}
}

// 错误输出
void error(SQLRETURN err) {
	printf("结果输出：");
	switch (err) {
		case	SQL_SUCCESS:
			puts("****SQL_SUCCESS*****");
			break;
		case	SQL_SUCCESS_WITH_INFO:
			puts("SQL_SUCCESS_WITH_INFO");
			break;
		case	SQL_ERROR:
			puts("SQL_ERROR");
			break;
		case	SQL_INVALID_HANDLE:
			puts("SQL_INVALID_HANDLE");
			break;
		case	SQL_NO_DATA_FOUND:
			puts("SQL_NO_DATA_FOUND");
			break;
		case	SQL_NEED_DATA:
			puts("SQL_NEED_DATA");
			break;
		default:
			puts("err");
	}
}

//初始化连接 
void init() {
//	SQLRETURN ret;
//	SQLHENV henv;
//	SQLHDBC hdbc;
//	SQLHSTMT hstmt;

	ret=SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv);//申请环境句柄
	ret=SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,SQL_IS_INTEGER);
	ret=SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);//申请数据库连接句柄

	//vc 6.0 使用： SQLCHAR*
	ret=SQLConnect(hdbc,(SQLCHAR*)"test_db",SQL_NTS,(SQLCHAR*)"sa",SQL_NTS,(SQLCHAR*)"666666",SQL_NTS);

	if(!(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO)) {
		printf("连接数据库失败!\n");
		return ;
	} else {
		printf("连接数据成功!\n");
	}
	//释放连接句柄
	SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
	//释放环境句柄
	SQLFreeHandle(SQL_HANDLE_ENV,henv);
}
//查询
void query() {
	ret=SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt);

	SQLCHAR sql1[] = "use test_db";
	SQLCHAR sql2[]="select num,name,addr,age from test_db.dbo.test";
	ret = SQLExecDirect(hstmt, sql1, SQL_NTS);
	ret = SQLExecDirect(hstmt, sql2, SQL_NTS);

	if(ret==SQL_SUCCESS || ret==SQL_SUCCESS_WITH_INFO) {
		SQLCHAR str1[20],str2[20],str3[20],str4[20];
		//	VC 6.0
		SQLINTEGER len_str1, len_str2, len_str3,len_str4;
		//	SQLLEN len_str1, len_str2, len_str3;
		printf("编号\t姓名\t地址\t年龄\n");
		while(SQLFetch(hstmt)!=SQL_NO_DATA) {
			SQLGetData(hstmt,1,SQL_C_CHAR,str1,50,&len_str1);   //获取第一列数据
			SQLGetData(hstmt,2,SQL_C_CHAR,str2,50,&len_str2);
			SQLGetData(hstmt,3,SQL_C_CHAR,str3,50,&len_str3);
			SQLGetData(hstmt,4,SQL_C_CHAR,str4,50,&len_str4);
			printf("%s\t%s\t%s\t%s\n",str1,str2,str3,str4);
		}
	}
	ret = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);//释放连接句柄
	//SQLDisconnect(hdbc);
	ret = SQLFreeHandle(SQL_HANDLE_ENV, henv);//释放环境句柄
	//	return 0;
}

//插入记录
void insert() {
	//DB_61.dbo.sc_61 如果不指定具体操作时报错“表名无效”
	ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

	unsigned char str[100] = "insert into test_db.dbo.test(num,name,addr,age) values(12,'李莉','深州',21)";

	//执行
	ret = SQLExecDirect(hstmt, str, SQL_NTS);
	if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
		cout << "插入成功！" << endl;
	} else {
		handleResult(hstmt, SQL_HANDLE_STMT, ret);
		cout<<"插入失败！"<<endl;
		handleResult(hstmt, SQL_HANDLE_STMT, ret);
	}
	error(ret);

	//	输出：77 ****SQL_SUCCESS*****
	//	表示插入成功
}

//删除记录
void delRecord() {
	//	传入要删除的 id,带参数查询可用字符拼接完成
	//	假如是 12
	int num = 12;

	string sql =  "delete from test_db.dbo.test where  num=";
	char ch[10];
	sprintf(ch,"%d",num);

	sql.append(ch);

	ret = SQLExecDirect(hstmt, (unsigned char*)sql.c_str(), SQL_NTS);

	if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO) {
		cout << "删除成功！" << endl;
	} else {
		handleResult(hstmt, SQL_HANDLE_STMT, ret);
		cout<<"删除失败！"<<endl;
	}

}
int main() {
	init();
	query();
	insert();
	delRecord();
}

VS2013中下可使用如下示例：

//	VS2013 环境
#include<iostream>
#include<string>
#include<windows.h>
#include<sql.h>
#include<sqlext.h>
#include<sqltypes.h>
#define sno_length 11
#define sname_length 10
#define sdepart_length 30
#define ssex_length 5
using namespace std;
SQLRETURN ret;
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
typedef struct {
	SQLCHAR sno[sno_length], sname[sname_length], sdept[sdepart_length], ssex[ssex_length];
	SQLSMALLINT sage, grade;
	SQLINTEGER csno, cbsname, cbdepart, cbsage, cbsex;
}SC;
SC sc;
//字符转为宽字符
wchar_t* trstring2wchar(const  char *str)
{
	int mystringsize = (int)(strlen(str) + 1);
	WCHAR* wchart = new wchar_t[mystringsize];
	MultiByteToWideChar(CP_ACP, 0, str, -1, wchart, mystringsize);
	return wchart;

}
//宽字符转换为字符串
void wchar2strstring(string & szDst, WCHAR * wchart)
{
	wchar_t * wtext = wchart;
	DWORD dwNmu = WideCharToMultiByte(CP_OEMCP, NULL, wtext, -1, NULL, 0, NULL, FALSE);
	char * psTest;
	psTest = new char[dwNmu];
	WideCharToMultiByte(CP_OEMCP, NULL, wtext, -1, psTest, dwNmu, NULL, FALSE);
	szDst = psTest;
	delete[]psTest;
}

//字符转换为wstring
wstring CharToWchar(const char* c, size_t m_encode = CP_ACP)
{
	std::wstring str;
	int len = MultiByteToWideChar(m_encode, 0, c, strlen(c), NULL, 0);
	wchar_t*	m_wchar = new wchar_t[len + 1];
	MultiByteToWideChar(m_encode, 0, c, strlen(c), m_wchar, len);
	m_wchar[len] = '\0';
	str = m_wchar;
	delete m_wchar;
	return str;
}

//错误处理
void handleResult(SQLHANDLE hHandle, SQLSMALLINT hType, RETCODE  RetCode)
{
	SQLSMALLINT iRec = 0;
	SQLINTEGER iError;
	WCHAR wszMessage[1000];
	WCHAR wszState[SQL_SQLSTATE_SIZE + 1];
	//处理无效
	if (RetCode == SQL_INVALID_HANDLE)
	{
		fwprintf(stderr, L"Invalid handle!\n");
		return;
	}
	//	对于此处版本 vc 6.0 报错
	while (SQLGetDiagRec(hType, hHandle, ++iRec, wszState, &iError, wszMessage, (SQLSMALLINT)(sizeof(wszMessage) / sizeof(WCHAR)),
		(SQLSMALLINT *)NULL) == SQL_SUCCESS){
		// Hide data truncated.. 
		if (wcsncmp(wszState, L"01004", 5))
		{
			fwprintf(stderr, L"[%5.5s]---%s---(%d)\n", wszState, wszMessage, iError);
		}
	}
}
//输出错误
void error(SQLRETURN err) {
	printf("错误相关信息输出： ");
	switch (err) {
	case	SQL_SUCCESS:puts("****SQL_SUCCESS*****"); break;
	case	SQL_SUCCESS_WITH_INFO:puts("SQL_SUCCESS_WITH_INFO"); break;
	case	SQL_ERROR:puts("SQL_ERROR"); break;
	case	SQL_INVALID_HANDLE:puts("SQL_INVALID_HANDLE"); break;
	case	SQL_NO_DATA_FOUND:puts("SQL_NO_DATA_FOUND"); break;
	case	SQL_NEED_DATA:puts("SQL_NEED_DATA"); break;
	default:puts("err");
	}
}
//宽字符转字符指针
char* wideCharToMultiByte(wchar_t* pWCStrKey)
{
	//第一次调用确认转换后单字节字符串的长度，用于开辟空间
	int pSize = WideCharToMultiByte(CP_OEMCP, 0, pWCStrKey, wcslen(pWCStrKey), NULL, 0, NULL, NULL);
	char* pCStrKey = new char[pSize + 1];
	//第二次调用将双字节字符串转换成单字节字符串
	WideCharToMultiByte(CP_OEMCP, 0, pWCStrKey, wcslen(pWCStrKey), pCStrKey, pSize, NULL, NULL);
	pCStrKey[pSize] = '\0';
	return pCStrKey;
}

//连接
void init(){
	ret = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &henv);//申请环境句柄
	ret = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, SQL_IS_INTEGER);
	ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);//申请数据库连接句柄
	//vs 2013 改为以下

	ret = SQLConnect(hdbc, (SQLWCHAR*)L"test_db", SQL_NTS, (SQLWCHAR*)L"sa", SQL_NTS, (SQLWCHAR*)L"666666", SQL_NTS);


	//VC6.0中用	ret = SQLConnect(hdbc, (unsigned char*)"test_db", SQL_NTS, (unsigned char*)"sa" , SQL_NTS, (unsigned char*)"666666", SQL_NTS);
	//	与上等价
	//	ret=SQLConnect(hdbc,(SQLCHAR*)"DB_61",SQL_NTS,(SQLCHAR*)"sa",SQL_NTS,(SQLCHAR*)"666666",SQL_NTS);
	//	DB_61为配置的ODBC数据源名称，这里根据自己的配置进行修改
	if (!(ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)){
		printf("连接数据库失败!\n");
		//		cout<"连接数据库失败!"<<endl;
		//		return -1;
	}
	else{
		printf("连接成功！\n");
	}
	//ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
}
//查询
void query(){
	//	VS 2013版本中
	ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
	SQLWCHAR sql1[] = L"use test_db";
	SQLWCHAR sql2[] = L"select * from test";
	ret = SQLExecDirect(hstmt, sql1, SQL_NTS);
	ret = SQLExecDirect(hstmt, sql2, SQL_NTS);


	if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO)
	{
		//	SQLWCHAR str1[50], str2[50], str3[50];
		SQLCHAR str1[50], str2[50], str3[50];
		SQLINTEGER len_str1, len_str2, len_str3;
		//	SQLLEN len_str1, len_str2, len_str3;
		/*SQLFetch函数的功能是将结果集的当前记录指针移至下一个记录；*/
		while (SQLFetch(hstmt) != SQL_NO_DATA)
		{
			/*SQLGetData函数的功能是提取结果集中当前记录的某个字段值*/
			SQLGetData(hstmt, 1, SQL_C_CHAR, str1, sizeof(str1), &len_str1);   //获取第一列数据
			SQLGetData(hstmt, 2, SQL_C_CHAR, str2, sizeof(str2), &len_str2);
			SQLGetData(hstmt, 3, SQL_C_CHAR, str3, sizeof(str3), &len_str3);
			//	retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, sCustID, 100, &cbCustID);
			printf(" %s \t %s \t %s \n", str1, str2, str3);
		}
	}
	ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
	//ret = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);//释放连接句柄
	//SQLDisconnect(hdbc);
	//ret = SQLFreeHandle(SQL_HANDLE_ENV, henv);//释放环境句柄
	//	return 0;
}

//插入记录
void insert(){
	ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
	SQLWCHAR str[100] = L"insert into test_db.dbo.test(num,name,addr,age) values(12,'李莉','深州',21)";
	ret = SQLExecDirect(hstmt, str, SQL_NTS);
	if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO){
		cout << "插入成功！" << endl;
	}
	else{
		handleResult(hstmt, SQL_HANDLE_STMT, ret);
		cout << "插入失败！" << endl;
	}
	error(ret);
	//	输出：****SQL_SUCCESS*****
	//	表示插入成功
}

//删除记录
void delRecord(){
	ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
	//	传入要删除的 id,带参数查询可用字符拼接完成
	//	假如是 12
	int num = 12;

	//	WCHAR* wchart = trstring2wchar(num.c_str());
	string sql = "delete from test_db.dbo.test where  num=";
	char ch[10];
	sprintf(ch, "%d", num);
	sql.append(ch);

	//	wcscat(sql, wchart);

	cout << "111" << endl;
	//wprintf(L"%S", sql);
	//	wcout << sql << endl;

	ret = SQLExecDirect(hstmt, trstring2wchar(sql.c_str()), SQL_NTS);

	if (ret == SQL_SUCCESS || ret == SQL_SUCCESS_WITH_INFO){
		cout << "删除成功！" << endl;
	}
	else{
		handleResult(hstmt, SQL_HANDLE_STMT, ret);
		cout << "删除失败！" << endl;
	}

}

int main(){
	init();
	insert();
	query();
	delRecord();
	query();
}

运行结果：

Dev-C++ 5.11 编译器通过ODBC访问数据库情况
需要注意的是， Dev-C++ 5.11 编译器对ODBC似乎并不支持，下面是编译提示的错误：