vc实现对远程SQL Server数据库的访问

雨点打透心脏的1/2处 2022-08-27 02:26 34阅读 0赞

vc实现对远程SQL Server数据库的访问  
  
1、远程数据库  
设远程数据库的ip地址为192.168.0.1，其中testdb数据库中有student表，student表包含两列：name和age。name为char类型，长度为10；age为int类型，长度为4。  
  
  
2、源代码  
HENV hEnv = NULL; // Env Handle from SQLAllocEnv()  
HDBC hDBC = NULL; // Connection handle  
  
  
RETCODE retcode;  
  
  
// Allocate memory for ODBC Environment handle  
SQLAllocEnv (&hEnv);  
  
  
// Allocate memory for the connection handle  
SQLAllocConnect (hEnv, &hDBC);  
  
  
// Connect to the data source "test" using userid and password.  
SQLCHAR szConnect\[\] = "DRIVER=\{SQL Server\};SERVER=192.168.0.1;UID=sa;PWD=Goncely;DATABASE=testdb";  
SQLCHAR szOutConn\[1024\];  
SQLSMALLINT n(0);  
retcode = SQLDriverConnect(hDBC, m\_hWnd, szConnect, strlen((char\*)szConnect),  
szOutConn, 1024, &n, SQL\_DRIVER\_NOPROMPT);  
if (retcode == SQL\_SUCCESS || SQL\_SUCCESS\_WITH\_INFO)  
\{  
TRACE("connect to sql server success ");  
  
  
HSTMT hStmt = NULL;// Statement handle  
  
  
// Allocate memory for the statement handle  
retcode = SQLAllocStmt (hDBC, &hStmt);  
  
UCHAR szSqlStr\[128\]= "SELECT \* from student" ;  
  
  
// Prepare the SQL statement by assigning it to the statement handle  
retcode = SQLPrepare (hStmt, szSqlStr, sizeof (szSqlStr));  
  
  
// Execute the SQL statement handle  
retcode = SQLExecute (hStmt);  
  
  
SQLSMALLINT ColumnCount(0);  
retcode = SQLNumResultCols(hStmt, &ColumnCount);  
TRACE("total %d cols in db. ", ColumnCount);  
  
  
char name\[11\];  
int age;  
SQLINTEGER StrLen\_or\_Ind;  
retcode = SQLBindCol(hStmt, 1, SQL\_C\_CHAR, name, 24, &StrLen\_or\_Ind);  
retcode = SQLBindCol(hStmt, 2, SQL\_C\_SLONG, &age, 0, &StrLen\_or\_Ind);  
  
  
do  
\{  
retcode = SQLFetch(hStmt);  
TRACE("name: %s, age: %d. ", name, age);  
\}  
while(retcode == SQL\_SUCCESS);  
  
  
// Free the allocated statement handle  
SQLFreeStmt (hStmt, SQL\_DROP);  
  
  
// Disconnect from datasource  
SQLDisconnect(hDBC);  
\}  
else if(/\*retcode == SQL\_SUCCESS\_WITH\_INFO ||\*/ retcode == SQL\_ERROR)  
\{  
TRACE("ai ");  
SQLCHAR Sqlstate\[6\], MessageText\[1024\];  
SQLINTEGER NativeError;  
SQLSMALLINT TextLength;  
SQLGetDiagRec(SQL\_HANDLE\_DBC, hDBC, 1, Sqlstate, &NativeError,  
MessageText, 1024, &TextLength);  
TRACE("%s ", MessageText);  
\}  
else  
\{  
TRACE("failed for others ");  
\}  
  
  
// Free the allocated connection handle  
SQLFreeConnect(hDBC);  
  
  
// Free the allocated ODBC environment handle  
SQLFreeEnv(hEnv);  
  
  
3、代码分析  
代码以sa账号建立到192.168.0.1服务器中testdb数据库的连接。远程连接的关键函数为SQLDriverConnect，函数的最后一个参数使用了SQL\_DRIVER\_NOPROMPT，如果将参数改为SQL\_DRIVER\_PROMPT，则每当此函数被调用后，会弹出一个数据库访问的配置对话框。此对话框的初始信息由szConnect初始化，用户进行更改后，最后的信息会返回到szOutConn，并以此为参数建立远程连接。对于SQL\_DRIVER\_NOPROMPT标记，函数直接将szConnect的内容拷贝到szOutConn。  
  
  
成功建立连接后，上述代码的中间部分顺序读取student表中的内容，并trace输出。最后几行代码用于释放资源。  
  
本篇文章来源于：开发学院 http://edu.codepub.com 原文链接：file:///E:/实例/VC++实现对远程SQL+Server数据库的访问.mht