1.odbc调用mysql,sqlserver
1.1配置odbc
2.控制面板->管理工具->ODBC数据源管理程序->驱动配置与测试
1.2程序实现步骤
分配环境句柄 (SQLAllocHandle)
分配连接句柄 (SQLAllocHandle)
设置连接属性 (可选)
建立数据库连接 (SQLConnect/SQLDriverConnect)
分配语句句柄 (SQLAllocHandle)
执行SQL语句 (SQLExecDirect/SQLPrepare+SQLExecute)
处理结果集 (SQLBindCol+SQLFetch)
释放语句句柄 (SQLFreeHandle)
断开数据库连接 (SQLDisconnect)
释放连接句柄 (SQLFreeHandle)
释放环境句柄 (SQLFreeHandle)
1.3odbc-mysql实例代码
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>void MySQL_ODBC_Example() {SQLHENV henv = NULL;SQLHDBC hdbc = NULL;SQLHSTMT hstmt = NULL;SQLRETURN retcode;// 1. 分配环境句柄retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {printf("分配环境句柄失败\n");return;}// 设置ODBC版本SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);// 2. 分配连接句柄retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {printf("分配连接句柄失败\n");SQLFreeHandle(SQL_HANDLE_ENV, henv);return;}// 3. 连接MySQL数据库retcode = SQLConnect(hdbc, (SQLCHAR*)"MySQL_DSN", SQL_NTS, // 数据源名称(SQLCHAR*)"username", SQL_NTS, // 用户名(SQLCHAR*)"password", SQL_NTS); // 密码if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {printf("连接MySQL数据库失败\n");SQLFreeHandle(SQL_HANDLE_DBC, hdbc);SQLFreeHandle(SQL_HANDLE_ENV, henv);return;}// 3. 连接MySQL数据库SQLCHAR connStr[] = "DRIVER={MySQL ODBC 8.0 Unicode Driver};""SERVER=127.0.0.1;""PORT=3306;""DATABASE=mydb;""USER=root;""PASSWORD=123456;""OPTION=3;";SQLDriverConnect(hdbc, NULL, connStr, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);// 4. 分配语句句柄retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {printf("分配语句句柄失败\n");SQLDisconnect(hdbc);SQLFreeHandle(SQL_HANDLE_DBC, hdbc);SQLFreeHandle(SQL_HANDLE_ENV, henv);return;}// 5. 执行查询retcode = SQLExecDirect(hstmt, (SQLCHAR*)"SELECT * FROM users", SQL_NTS);if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {printf("执行查询失败\n");SQLFreeHandle(SQL_HANDLE_STMT, hstmt);SQLDisconnect(hdbc);SQLFreeHandle(SQL_HANDLE_DBC, hdbc);SQLFreeHandle(SQL_HANDLE_ENV, henv);return;}// 6. 处理结果集SQLCHAR name[256];SQLLEN age;SQLBindCol(hstmt, 1, SQL_C_CHAR, name, sizeof(name), NULL);SQLBindCol(hstmt, 2, SQL_C_LONG, &age, 0, NULL);while (SQLFetch(hstmt) == SQL_SUCCESS) {printf("Name: %s, Age: %d\n", name, age);}// 7. 清理资源SQLFreeHandle(SQL_HANDLE_STMT, hstmt);SQLDisconnect(hdbc);SQLFreeHandle(SQL_HANDLE_DBC, hdbc);SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
1.4odbc-sqlserver 实例代码
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#include <stdio.h>void SQLServer_ODBC_Example() {SQLHENV henv = NULL;SQLHDBC hdbc = NULL;SQLHSTMT hstmt = NULL;SQLRETURN retcode;// 1. 分配环境句柄retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {printf("分配环境句柄失败\n");return;}// 设置ODBC版本SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);// 2. 分配连接句柄retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {printf("分配连接句柄失败\n");SQLFreeHandle(SQL_HANDLE_ENV, henv);return;}// 3. 连接SQL Server数据库 (使用连接字符串)SQLCHAR connStr[1024] = "DRIVER={ODBC Driver 17 for SQL Server};""SERVER=your_server_name;""DATABASE=your_database_name;""UID=your_username;""PWD=your_password;";SQLCHAR outConnStr[1024];SQLSMALLINT outConnStrLen;retcode = SQLDriverConnect(hdbc, NULL, connStr, SQL_NTS, outConnStr, sizeof(outConnStr), &outConnStrLen, SQL_DRIVER_NOPROMPT);if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {printf("连接SQL Server数据库失败\n");SQLFreeHandle(SQL_HANDLE_DBC, hdbc);SQLFreeHandle(SQL_HANDLE_ENV, henv);return;}// 4. 分配语句句柄retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {printf("分配语句句柄失败\n");SQLDisconnect(hdbc);SQLFreeHandle(SQL_HANDLE_DBC, hdbc);SQLFreeHandle(SQL_HANDLE_ENV, henv);return;}// 5. 执行存储过程retcode = SQLExecDirect(hstmt, (SQLCHAR*)"EXEC sp_GetUserData @UserId=123", SQL_NTS);if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO) {printf("执行存储过程失败\n");SQLFreeHandle(SQL_HANDLE_STMT, hstmt);SQLDisconnect(hdbc);SQLFreeHandle(SQL_HANDLE_DBC, hdbc);SQLFreeHandle(SQL_HANDLE_ENV, henv);return;}// 6. 处理结果集SQLCHAR userName[256];SQLCHAR email[256];SQLBindCol(hstmt, 1, SQL_C_CHAR, userName, sizeof(userName), NULL);SQLBindCol(hstmt, 2, SQL_C_CHAR, email, sizeof(email), NULL);while (SQLFetch(hstmt) == SQL_SUCCESS) {printf("User: %s, Email: %s\n", userName, email);}// 7. 清理资源SQLFreeHandle(SQL_HANDLE_STMT, hstmt);SQLDisconnect(hdbc);SQLFreeHandle(SQL_HANDLE_DBC, hdbc);SQLFreeHandle(SQL_HANDLE_ENV, henv);
}
1.5odbc-mysql-sqlserver区别
1.连接字符串格式不同：MySQL: "DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=localhost;DATABASE=testdb;..."
SQL Server: "DRIVER={ODBC Driver 17 for SQL Server};SERVER=your_server;DATABASE=your_db;..."2.认证方式：MySQL通常使用用户名/密码
SQL Server可能使用Windows集成认证(Trusted_Connection=yes)3.特殊功能：SQL Server支持存储过程、分布式事务等高级功能2.ado调用mysql,sqlserver
2.1程序实现步骤
/*1.初始化COM库
创建连接对象
设置连接字符串
打开数据库连接
执行SQL命令
处理结果集
关闭连接
释放COM资源
*/2.2ado-mysql实例代码
#import "msado15.dll" no_namespace rename("EOF", "adoEOF") rename("BOF", "adoBOF")BOOL ExecuteMySQLQuery()
{// 1. 初始化COM::CoInitialize(NULL);_ConnectionPtr pConn = NULL;_RecordsetPtr pRs = NULL;_ConnectionPtr pConn(__uuidof(Connection));_RecordsetPtr pRst(__uuidof(Recordset));try {// 2. 创建连接对象HRESULT hr = pConn.CreateInstance(__uuidof(Connection));if (FAILED(hr)) throw _com_error(hr);// 3. 设置连接字符串_bstr_t strConn = "Provider=MSDASQL;" //使用 MSDASQL Provider 桥接ODBC驱动。"DRIVER={MySQL ODBC 8.0 Unicode Driver};" // 根据实际驱动版本调整"SERVER=localhost;"//驱动名称需与系统安装的MySQL ODBC驱动一致（如MySQL ODBC 8.0 Unicode Driver）。"DATABASE=testdb;""UID=username;""PWD=password;""OPTION=3;";// 4. 打开连接pConn->Open(strConn, "", "", adConnectUnspecified);// 5. 执行查询// 示例1：执行SELECT查询pRst = pConn->Execute("SELECT * FROM users", NULL, adCmdText);// 遍历结果集while (!pRst->adoEOF) {//处理数据..._bstr_t name = pRst->Fields->GetItem("name")->Value;long age = pRst->Fields->GetItem("age")->Value;pRst->MoveNext();}// 示例2：执行INSERT操作（参数化查询）_CommandPtr pCmd(__uuidof(Command));pCmd->ActiveConnection = pConn;pCmd->CommandText = "INSERT INTO products (name, price) VALUES (?, ?)";// 添加参数ParametersPtr params = pCmd->Parameters;params->Append(pCmd->CreateParameter("name", adVarChar, adParamInput, 255, "New Product"));params->Append(pCmd->CreateParameter("price", adDecimal, adParamInput, , 19.99));pCmd->Execute(NULL, NULL, adCmdText);// 6. 关闭连接pRs->Close();pConn->Close();}catch (_com_error &e) {printf("Error: %s\n", (char*)e.Description());return FALSE;}// 7. 释放资源if (pRs) pRs.Release();if (pConn) pConn.Release();::CoUninitialize();return TRUE;
}2.3ado-sqlserver实例代码
只是sql 语句不同
3.ADO-ODBC-区别
- 1.检查头文件- 有 #include <afxdb.h> → ODBC#include <sql.h>#include <sqlext.h>include <QSqlDatabase> // Qt的ODBC封装
- 有 #import "msado15.dll" → ADO#import "msado15.dll" no_namespace rename("EOF", "adoEOF") → ADO COM组件接口2. 依赖库- ODBC 依赖 odbc32.lib
- ADO 依赖 msado15.lib- 3.查看连接字符串- 包含 DRIVER= → ODBC
"DRIVER={SQL Server};SERVER=myserver;DATABASE=mydb;" // 使用驱动- 包含 Provider= → ADO
"Provider=SQLOLEDB;Data Source=myserver;Initial Catalog=mydb;User ID=user;Password=pass;" // SQL Server
"Provider=MSDASQL;Driver={MySQL ODBC 5.3 Driver};Server=myserver;Database=mydb;User=user;Password=pass;" // 通过ODBC的ADO- 4.观察对象类型特征 ODBC ADO
初始化 SQLAllocHandle, SQLConnect CoCreateInstance, Connection.Open
执行查询 SQLExecDirect Recordset.Open
错误处理 SQLGetDiagRec Errors集合
事务控制 SQLTransact Connection.BeginTrans- CDatabase / CRecordset → ODBC- _ConnectionPtr / _RecordsetPtr → ADOWindows: 依赖odbc32.dll
Linux: 依赖libodbc.so （通过unixODBC）
Qt: 需启用QODBC插件- 5.看错误处理- catch (CDBException* e) → ODBC
- catch (_com_error &e) → ADO