VS2012通过ODBC与SQLSever2012连接

      在做数据库接口实验时，身边的童鞋一路坦途，而自家的电脑太矫情了，一路波折不断。先是微软的SQLSever怎么都装不上，后来下载了一个大家都说装不上的版本，竟然就装上了！！！可是，问题又来了，VS2012通过ODBC怎么也与SQLSever2012连接不上，折腾整整两天，才弄出来。希望可以帮到和我一样可怜的孩子。

实验环境

1.操作系统：windows7

2.采用SQL SERVER 2012数据库管理系统作为实验平台 

3.使用 microsoft Visual Studio 2012 作为应用程序编写的平台

实验步骤

1.修改SQL Sever中管理员sa的密码，默认数据库

（1）先以windows身份验证登陆SQL Sever2012

 

（2）修改sa密码，因为该密码在建立应用程序与ODBC数据源的连接中用到，所以要获取sa的密码，sa的默认数据库改为在应用程序中使用的数据库。

 

（3）然后右键点击实例属性，修改安全性为SQL Server和Window身份验证模式

 

（4）点击确定之后，一定要重启实例，否则之前的修改没办法生效。

 

（5）重启之后，即可选择SQL Sever 身份验证登陆，假如登陆成功，这说明已经修改成功了。假如无法登陆，则说明不正确，需要重新以windows身份验证登陆，重新修改sa密码。

 

2.建立QDBC数据源

（1）打开ODBC

 

 

（2）添加用户DSN选择驱动程序

 

由于SQL Sever端是由SQL Sever 2012充当，在VS2012中编写的是客户端查询数据库数据的程序，所以选择的是客户端。

（3）配置数据源

 

点击下一步，输入之前已经修改过的sa密码

 

  

（4）测试数据源

假如测试失败的话，可以让sa先在SQL Sever2012上登陆成功再测试，应该就会成功。

 

3.在VS2012上编写的数据库访问应用程序

（1）新建空项目

建立win32控制台应用程序在电脑上运行不成功，SQLConnect函数老是连接不上，在空项目里编写控制台程序则可以运行（感谢球儿的提醒与帮忙~）。但是我身边有的同学的电脑貌似win32控制台应用程序就可以

 

（2）编写代码

#include<windows.h>
#include<stdio.h>
#include<stdlib.h>
#include<string>
#include<iostream>
#include "sql.h"
#include "sqltypes.h"
#include "sqlext.h"
using namespace std;

RETCODE retcode;  //结果返回集
SQLHDBC hdbc;  //定义连接句柄

void SQL(char* );  //执行SQL语句子程序
void print();  //打印子程序
void print2();
void print3();
void partprint();  //打印部分查询子程序
void partprint2();
void partprint3();

int main()
{
	char str[200], pstr[200];
	int dbchoice;
	int opchoice;
	SQLHANDLE henv;  //定义环境句柄
	unsigned char SY[] = "experiment";   //数据源名称
	unsigned char db2[] = "sa";  //用户名
	unsigned char pass[] = "123456";  //密码

	//分配ODBC环境
	retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
	if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
		retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);  //设置环境属性

	 if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
		 retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);  //分配连接句柄

	 if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
		 retcode = SQLConnect(hdbc, SY, SQL_NTS, db2, SQL_NTS,pass, SQL_NTS);  //连接

	 if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
		 while (1){
			 cout << "请选择数据库类型（1：course, 2:student, 3:sc）：" ;
			 cin >> dbchoice;
			 if (dbchoice >=4 || dbchoice <= 0)
				 cout << "Error：不存在该数据库！\n";
			 else{
			     cout << "请选择操作类型（1：查询，2：update, drop,insert）：";
			     cin >> opchoice;
			    if (opchoice == 1){
				    if (dbchoice == 1)
					   partprint();
				    else if (dbchoice == 2)
							    partprint2();
					else if (dbchoice == 3)
							  partprint3();
				 }
				else if (opchoice ==2){
					cout << "请输入操作：" <<endl;
					getchar();
					cin.getline(str,200);
					SQL(str);
					 if (dbchoice == 1)
					   print();
				    else if (dbchoice == 2)
							    print2();
					else if (dbchoice == 3)
							  print3();
				}
	           else
				   cout << "错误操作！" <<endl;
			 }
//			 SQL(str);
//			 print3();
		 }
	 }
	SQLFreeConnect(hdbc);  //释放连接句柄
	SQLFreeEnv(henv);  //释放ODBC环境句柄

	system("pause");
 	return 0;	
}

void print()
{
	unsigned char yuju[] = "select * from course";
	SQLHSTMT hstmt;  //定义语句句柄
	int i =1;

	char L1[20], L2[20], L3[20], L4[20], L5[20];
	long lenOut1, lenOut2, lenOut3, lenOut4, lenOut5;

	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
	 if (retcode == SQL_SUCCESS){
		 retcode = SQLExecDirect(hstmt, yuju, SQL_NTS);
		  if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
			  retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, L1, sizeof(L1), &lenOut1);
			  retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, L2, sizeof(L2), &lenOut2);
			  retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, L3, sizeof(L3), &lenOut3);
			  retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR, L4, sizeof(L4), &lenOut4);
			  retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR, L5, sizeof(L5), &lenOut5);  //把所有捆绑过的数据字段的数据拷贝到相应的缓冲区

			  retcode = SQLFetch(hstmt);  //该函数用于将记录集的下一行变成当前行，并把所有捆绑过的数据字段的数据拷贝到相应的缓冲区

			  while (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
				  printf("%d\t%s\t%s\t%s\t%s\t%s\t\n", i, L1, L2, L3, L4, L5);
				  retcode = SQLFetch(hstmt);
				  i ++;
			  }
		  }
	 }
	 SQLFreeStmt(hstmt, SQL_DROP);
}

void SQL(char* aaa)
{
	SQLHSTMT hstmt;
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    if (retcode == SQL_SUCCESS)
		retcode = SQLExecDirect(hstmt, (SQLCHAR *)aaa, SQL_NTS);

	SQLFreeStmt(hstmt, SQL_DROP);  //释放语句句柄
}

void print2()
{
	unsigned char yuju[] = "select * from student";
	int i = 1;

	SQLHSTMT hstmt;

	char  L1[50], L2[50], L3[50], L4[50], L5[20], L6[50];
	long lenOut1, lenOut2, lenOut3, lenOut4, lenOut5, lenOut6;

	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

	if (retcode == SQL_SUCCESS){
		retcode = SQLExecDirect(hstmt, yuju, SQL_NTS);
		if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
			retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, L1, sizeof(L1), &lenOut1);
			  retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, L2, sizeof(L2), &lenOut2);
			  retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, L3, sizeof(L3), &lenOut3);
			  retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR, L4, sizeof(L4), &lenOut4);
			  retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR, L5, sizeof(L5), &lenOut5); 
			  retcode = SQLBindCol(hstmt, 6, SQL_C_CHAR, L6, sizeof(L6), &lenOut6); 
			  retcode = SQLFetch(hstmt);

			  while (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
				  printf("%d\t%s\t%s\t%s\t%s\t%s\t%s\t\n", i, L1, L2, L3, L4, L5, L6);
				  retcode = SQLFetch(hstmt);
				  i ++;
			  }
		}
	}
	SQLFreeStmt(hstmt, SQL_DROP);
}

void print3()
{
	unsigned char yuju[] = "select * from sc";
	int i = 1;
	SQLHSTMT hstmt;

	char  L1[50], L2[50], L3[50];
	long lenOut1, lenOut2, lenOut3;

	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

	if (retcode == SQL_SUCCESS){
		retcode = SQLExecDirect(hstmt, yuju, SQL_NTS);
		if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
			retcode = SQLBindCol(hstmt, 1, SQL_C_CHAR, L1, sizeof(L1), &lenOut1);
			  retcode = SQLBindCol(hstmt, 2, SQL_C_CHAR, L2, sizeof(L2), &lenOut2);
			  retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, L3, sizeof(L3), &lenOut3);
			  retcode = SQLFetch(hstmt);

			  while (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
				  printf("%d\t%s\t%s\t%s\n", i, L1, L2, L3);
				  retcode = SQLFetch(hstmt);
				  i ++;
			  }
		}
	}
	SQLFreeStmt(hstmt, SQL_DROP);
}


void partprint()
{
	unsigned char yuju[] = "select cno,cname from course where semester = '春'";
	SQLHSTMT hstmt;  //定义语句句柄
	int i = 1;

	char L1[50], L2[50], L3[50], L4[50], L5[50];
	long lenOut1, lenOut2, lenOut3, lenOut4, lenOut5;

	cout <<  "select cno,cname from course where semester = '春'" << endl;
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
	 if (retcode == SQL_SUCCESS){
		 retcode = SQLExecDirect(hstmt, yuju, SQL_NTS);
		 retcode = SQLFetch(hstmt);
		  while (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){			  
//		      retcode = SQLFetch(hstmt);
			  retcode = SQLGetData(hstmt, 1, SQL_C_CHAR, L1, sizeof(L1), &lenOut1);
			  retcode = SQLGetData(hstmt, 2, SQL_C_CHAR, L2, sizeof(L2), &lenOut2);
//			  retcode = SQLBindCol(hstmt, 3, SQL_C_CHAR, L3, sizeof(L3), &lenOut3);
//			  retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR, L4, sizeof(L4), &lenOut4);
//			  retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR, L5, sizeof(L5), &lenOut5);  //把所有捆绑过的数据字段的数据拷贝到相应的缓冲区
			  retcode = SQLFetch(hstmt);
			  printf("%i\t%s\t%s\n", i, L1, L2);
			  i ++;
		  }
	 }
	 SQLFreeStmt(hstmt, SQL_DROP);
}


void partprint2()
{
	unsigned char yuju[] = "select sno, sname, classno from student where dept = '计算机' and sex = '女'";
	int i = 1;

	SQLHSTMT hstmt;

	char  L1[50], L2[50], L3[50], L4[50], L5[20], L6[50];
	long lenOut1, lenOut2, lenOut3, lenOut4, lenOut5, lenOut6;

	cout << "select sno, sname, classno from student where dept = '计算机' and sex = '女'" << endl;
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

	if (retcode == SQL_SUCCESS){
		retcode = SQLExecDirect(hstmt, yuju, SQL_NTS);
		retcode = SQLFetch(hstmt);
		while(retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
//		    retcode = SQLFetch(hstmt);
			retcode = SQLGetData(hstmt, 1, SQL_C_CHAR, L1, sizeof(L1), &lenOut1);
			retcode = SQLGetData(hstmt, 2, SQL_C_CHAR, L2, sizeof(L2), &lenOut2);
			retcode = SQLGetData(hstmt, 3, SQL_C_CHAR, L3, sizeof(L3), &lenOut3);
//			  retcode = SQLBindCol(hstmt, 4, SQL_C_CHAR, L4, sizeof(L4), &lenOut4);
//			  retcode = SQLBindCol(hstmt, 5, SQL_C_CHAR, L5, sizeof(L5), &lenOut5); 
//			  retcode = SQLBindCol(hstmt, 6, SQL_C_CHAR, L6, sizeof(L6), &lenOut6); 
			retcode = SQLFetch(hstmt);	  
			printf("%d\t%s\t%s\t%s\n", i, L1, L2, L3);
			i ++;
		}
	}
	SQLFreeStmt(hstmt, SQL_DROP);
}

void partprint3()
{
	unsigned char yuju[] = "select  student.sno, student.sname,cname from sc, student,course where sc.sno = student.sno and sc.cno = course.cno and  grade >= 90";
	int i = 1;
	SQLHSTMT hstmt;

	char  L1[50], L2[50], L3[50];
	long lenOut1, lenOut2, lenOut3;

	cout << "select  student.sno, student.sname,cname from sc, student,course where sc.sno = student.sno and sc.cno = course.cno and  grade >= 90" <<endl;
	retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);

	if (retcode == SQL_SUCCESS){
		retcode = SQLExecDirect(hstmt, yuju, SQL_NTS);
		retcode = SQLFetch(hstmt);
		while (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO){
//		    retcode = SQLFetch(hstmt);
			retcode = SQLGetData(hstmt, 1, SQL_C_CHAR, L1, sizeof(L1), &lenOut1);
		    retcode = SQLGetData(hstmt, 2, SQL_C_CHAR, L2, sizeof(L2), &lenOut2);
			retcode = SQLGetData(hstmt, 3, SQL_C_CHAR, L3, sizeof(L3), &lenOut3);
		    retcode = SQLFetch(hstmt);
			printf("%d\t%s\t%s\t%s\n", i, L1, L2, L3);
			i ++;
		}
	}
	SQLFreeStmt(hstmt, SQL_DROP);
}

运行效果：