數(shù)據(jù)庫實驗報告6

1、數(shù)據(jù)庫原理實驗報告題目：實驗六:ODBC/JDBC數(shù)據(jù)庫編程學號姓名班級日期xxxxxxxxxxxx2016.10.29一. 實驗內(nèi)容、步驟以及結(jié)果1.ODBC配置以及程序調(diào)試：（50分）(1)配置一個ODBC數(shù)據(jù)源，要求數(shù)據(jù)源名稱：student，其中包含s(學生信息)表。1.用系統(tǒng)工具配置ODBC，點擊“添加”：2.出現(xiàn)以下對話框，選中SQL Sever，點擊完成3.在出現(xiàn)的對話框中設(shè)置屬性，如圖，點擊下一步，點擊下一步后出現(xiàn)如下，更改默認數(shù)據(jù)庫，點擊下一步。4.在出現(xiàn)的對話框中點擊測試連接后出現(xiàn)測試成功的對話框，點擊確定退出ODEC數(shù)據(jù)源管理器。(2)閱讀并運行實驗給出的例子程序，理解O

2、DBC編程，要求簡單寫出自己對這段程序的理解或者流程圖，并且請給出程序運行結(jié)果示例圖。l 定義句柄，變量l 初始化環(huán)境l 建立連接l 初始化語句句柄l 執(zhí)行語句l 結(jié)果集中處理l 輸出，釋放句柄l 結(jié)果如下實例運行結(jié)果如下：2.參考給出的ODBC編程示例，使用ODBC技術(shù)，編寫一個簡單的程序，包含對數(shù)據(jù)庫SPJ的連接，查詢，插入，修改和刪除。（50分）插入：查詢：修改：刪除：代碼：#include "stdafx.h"#include<Windows.h>#include<iostream>#include <sql.h> #includ

3、e <sqlext.h> #include<sqltypes.h>#include<string>#include<cstdio>using namespace std;int main()SQLHENV henv;/環(huán)境句柄SQLHDBC hdbc;/連接句柄SQLHSTMT hstmt; /語句句柄char sql_order100;SQLRETURN ret;SQLCHAR sno50, sname50, ssex50, sbirth50, sdept50;SQLINTEGER cbsno = SQL_NTS, cbsname = SQL_

4、NTS, cbssex = SQL_NTS, cbsbirth = SQL_NTS, cbsdept = SQL_NTS;ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);ret = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);ret = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);ret = SQLConnect(hdbc, (SQLCHAR*)"student&

5、quot;, SQL_NTS, (SQLCHAR*)"sa", SQL_NTS, (SQLCHAR*)"123", SQL_NTS);if (!SQL_SUCCEEDED(ret)return -1;ret = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);int demo;while (1)cout << "1.insert" << "2.select " << "3.delete " <<

6、"4.update " << "0.exit"<<endl;cin >> demo;if (demo = 0)break;else if (demo = 3)cout << "輸入刪除語句" << endl;getchar();gets_s(sql_order);ret = SQLExecDirect(hstmt, (SQLCHAR*)sql_order, SQL_NTS);if (ret = SQL_SUCCESS | ret = SQL_SUCCESS_WITH_INFO

7、)cout << "刪除成功" << endl;SQLFreeHandle(SQL_HANDLE_STMT, hstmt);else if (demo = 4)cout << "輸入更新語句" << endl;getchar();gets_s(sql_order);ret = SQLExecDirect(hstmt, (SQLCHAR*)sql_order, SQL_NTS);if (ret = SQL_SUCCESS | ret = SQL_SUCCESS_WITH_INFO)cout <<

8、"刪除成功" << endl;SQLFreeHandle(SQL_HANDLE_STMT, hstmt);else if (demo = 2)printf("輸入SQL查詢語句n");getchar();gets_s(sql_order);ret = SQLExecDirect(hstmt, (SQLCHAR*)sql_order, SQL_NTS);if (ret = SQL_SUCCESS | ret = SQL_SUCCESS_WITH_INFO)ret = SQLBindCol(hstmt, 1, SQL_C_CHAR, sno, 5

9、0, 0);ret = SQLBindCol(hstmt, 2, SQL_C_CHAR, sname, 50, 0);ret = SQLBindCol(hstmt, 3, SQL_C_CHAR, ssex, 50, 0);ret = SQLBindCol(hstmt, 4, SQL_C_CHAR, sbirth, 50, 0);ret = SQLBindCol(hstmt, 5, SQL_C_CHAR, sdept, 50, 0);while (ret = SQLFetch(hstmt) != SQL_NO_DATA_FOUND)printf("%s%s%s%s%sn",

10、sno, sname, ssex, sbirth, sdept);SQLFreeHandle(SQL_HANDLE_STMT, hstmt);elseprintf("依次輸入插入的學號，姓名，性別，生日，學院n");scanf("%s%s%s%s%s", sno, sname, ssex, sbirth, sdept);ret = SQLPrepare(hstmt, (SQLCHAR*)"insert into S values(?,?,?,?,?)", SQL_NTS);if (ret = SQL_SUCCESS | ret = S

11、QL_SUCCESS_WITH_INFO)ret = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 50, 0, sno, 0, &cbsno);ret = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 50, 0, sname, 0, &cbsname);ret = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 50, 0, ssex, 0, &cbssex);ret = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 50, 0, sbirth, 0, &cbsbirth);ret = SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHA