vc数据库开发案例

#include <iostream>
#include <windows.h>
#include <sqltypes.h>
#include <sql.h>
#include <sqlext.h>

void CheckSqlError(SQLRETURN ret, SQLSMALLINT handleType, SQLHANDLE handle) {
    SQLCHAR sqlState[6], messageText[SQL_MAX_MESSAGE_LENGTH];
    SQLSMALLINT textLength;
    SQLINTEGER nativeError;
    SQLSMALLINT i = 1;

    while (SQLGetDiagRec(handleType, handle, i, sqlState, &nativeError, messageText, sizeof(messageText), &textLength) == SQL_SUCCESS) {
        std::cerr << "SQL Error: " << sqlState << " - " << messageText << std::endl;
        i++;
    }
}

int main() {
    SQLHENV env;
    SQLHDBC dbc;
    SQLRETURN ret;

    // Allocate environment handle
    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    if (!SQL_SUCCEEDED(ret)) {
        CheckSqlError(ret, SQL_HANDLE_ENV, env);
        return 1;
    }

    // Set the ODBC version environment attribute
    ret = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
    if (!SQL_SUCCEEDED(ret)) {
        CheckSqlError(ret, SQL_HANDLE_ENV, env);
        SQLFreeHandle(SQL_HANDLE_ENV, env);
        return 1;
    }

    // Allocate connection handle
    ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    if (!SQL_SUCCEEDED(ret)) {
        CheckSqlError(ret, SQL_HANDLE_DBC, dbc);
        SQLFreeHandle(SQL_HANDLE_ENV, env);
        return 1;
    }

    // Connect to the database
    ret = SQLConnect(dbc, (SQLCHAR*)"YourDatabaseName", SQL_NTS, (SQLCHAR*)"YourUsername", SQL_NTS, (SQLCHAR*)"YourPassword", SQL_NTS);
    if (!SQL_SUCCEEDED(ret)) {
        CheckSqlError(ret, SQL_HANDLE_DBC, dbc);
        SQLFreeHandle(SQL_HANDLE_DBC, dbc);
        SQLFreeHandle(SQL_HANDLE_ENV, env);
        return 1;
    }

    std::cout << "Connected to the database successfully!" << std::endl;

    // Free handles
    SQLDisconnect(dbc);
    SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    SQLFreeHandle(SQL_HANDLE_ENV, env);

    return 0;
}

// 假设有一个名为"Customers"的表，包含"CustomerID"和"CompanyName"字段
std::string query = "SELECT CustomerID, CompanyName FROM Customers WHERE CompanyName LIKE 'A%'";
ret = SQLExecDirect(dbc, (SQLCHAR*)query.c_str(), SQL_NTS);
if (!SQL_SUCCEEDED(ret)) {
    CheckSqlError(ret, SQL_HANDLE_STMT, stmt);
    return 1;
}

// 处理查询结果
SQLCHAR customerID[10];
SQLCHAR companyName[50];
while (SQLFetch(stmt) == SQL_SUCCESS) {
    SQLGetData(stmt, 1, SQL_C_CHAR, customerID, sizeof(customerID), NULL);
    SQLGetData(stmt, 2, SQL_C_CHAR, companyName, sizeof(companyName), NULL);
    std::cout << "CustomerID: " << customerID << ", CompanyName: " << companyName << std::endl;
}