【C++SQL查询器】

#include <iostream>
#include <fstream> // 文件输入流
#include <string>
#include <windows.h>
#include <sqltypes.h>
#include <sql.h>
#include <sqlext.h>
#include <ctime>
#include <iomanip>
#include <thread>

// 全局变量，用于保存上一次查询的行数
int previousRowCount = 0;

// 函数用于获取当前时间的带前缀的字符串
std::string getTimePrefix() {
    std::time_t current_time;
    std::time(&current_time);
    std::tm current_time_info;
    localtime_s(&current_time_info, &current_time);
    char timeBuffer[22];
    int ret = strftime(timeBuffer, sizeof(timeBuffer), "[%Y-%m-%d %T]", &current_time_info);
    return std::string(timeBuffer) + " ";
}

// 函数用于执行 SQL 查询并返回结果以及行数
int executeSQLQuery(SQLHDBC dbc, const std::string& query) {
    SQLHSTMT stmt;
    SQLRETURN ret;
    SQLCHAR outstr[4096];
    SQLSMALLINT outstrlen;
    int rowCount = 0;

    SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
    ret = SQLExecDirectA(stmt, (SQLCHAR*)query.c_str(), SQL_NTS);

    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        SQLGetDiagRecA(SQL_HANDLE_STMT, stmt, 1, NULL, NULL, outstr, sizeof(outstr), &outstrlen);
        std::cerr << getTimePrefix() << "执行 SQL 查询时出错：" << outstr << std::endl;
        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        return -1;
    }

    // 获取结果集中列的数量
    SQLSMALLINT numCols;
    SQLNumResultCols(stmt, &numCols);

    while (SQLFetch(stmt) == SQL_SUCCESS) {
        rowCount++; // 每获取一行数据，行数加一

        if (rowCount > previousRowCount) {
            std::cout << getTimePrefix() << "发现新增数据：";

            // 输出每一列的名称和数据
            for (int i = 1; i <= numCols; ++i) {
                SQLCHAR columnName[256];
                SQLCHAR columnData[256];
                SQLLEN indicator;
                ret = SQLDescribeColA(stmt, i, columnName, sizeof(columnName), NULL, NULL, NULL, NULL, NULL);
                if (ret == SQL_SUCCESS) {
                    ret = SQLGetData(stmt, i, SQL_C_CHAR, columnData, sizeof(columnData), &indicator);
                    if (ret == SQL_SUCCESS) {
                        // 输出列名和数据
                        std::cout << (i > 1 ? ", " : "") << (char*)columnName << ": " << (char*)columnData;
                    }
                }
            }
            std::cout << std::endl;
        }
    }

    SQLFreeHandle(SQL_HANDLE_STMT, stmt);
    return rowCount; // 返回行数
}

// 函数用于执行检测操作
void performDetection(SQLHDBC dbc, const std::string& query) {
    int currentRowCount = executeSQLQuery(dbc, query);

    if (currentRowCount > previousRowCount) {
        std::cout << getTimePrefix() << "发现新增行数：" << currentRowCount - previousRowCount << std::endl;

        // 提示新增的 Beep 声音
        Beep(1000, 500);
        Beep(2000, 500);
        Beep(3000, 500);
        Beep(4000, 500);
    }
    else {
        std::cout << getTimePrefix() << "查询到的行数：" << currentRowCount << std::endl;
    }

    // 更新上一次查询的行数
    previousRowCount = currentRowCount;
}


int main() {
    // 从文件中读取连接字符串
    std::ifstream file("conn.txt");//DRIVER={SQL Server};SERVER=192.168.1.102;DATABASE=myserver;UID=sa;PWD=123456
    if (!file.is_open()) {
        std::cerr << "无法打开文件 conn.txt" << std::endl;
        return 1;
    }

    std::string connectionString;
    std::getline(file, connectionString);

    file.close();

    if (connectionString.empty()) {
        std::cerr << "连接字符串为空" << std::endl;
        return 1;
    }

    SQLHENV env;
    SQLHDBC dbc;
    SQLRETURN ret;

    // 初始化 SQL 环境
    ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
    ret = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);

    // 连接到数据库
    ret = SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
    ret = SQLDriverConnectA(dbc, NULL, (SQLCHAR*)connectionString.c_str(), connectionString.size(), NULL, 0, NULL, SQL_DRIVER_COMPLETE);

    if (ret != SQL_SUCCESS && ret != SQL_SUCCESS_WITH_INFO) {
        SQLCHAR outstr[4096];
        SQLSMALLINT outstrlen;
        SQLGetDiagRecA(SQL_HANDLE_DBC, dbc, 1, NULL, NULL, outstr, sizeof(outstr), &outstrlen);
        std::cerr << getTimePrefix() << "连接到数据库时出错：" << outstr << std::endl;
        return 1;
    }

    // 定义要执行的查询语句
    std::string query = "SELECT * FROM myserver.dbo.alarmevent";

    // 首次执行检测操作
    performDetection(dbc, query);

    std::cout << "\n";
    std::cout << "\t _   _    _    _   _ _  __\n";
    std::cout << "\t| | | |  / \\  | \\ | | |/ /\n";
    std::cout << "\t| |_| | / _ \\ |  \\| | ' /\n";
    std::cout << "\t|  _  |/ ___ \\| |\\  | . \\\n";
    std::cout << "\t|_| |_/_/   \\_\\_| \\_|_|\\_\\\n";
    std::cout << "\n";

    // 每小时的特定分钟执行检测操作
    while (true) {
        std::time_t current_time;
        std::time(&current_time);
        std::tm current_time_info;
        localtime_s(&current_time_info, &current_time);
        if (current_time_info.tm_min % 5 == 0) {
            performDetection(dbc, query);
            Sleep(1000*60);
        }
        Sleep(1000);
    }

    // 断开连接并清理资源
    SQLDisconnect(dbc);
    SQLFreeHandle(SQL_HANDLE_DBC, dbc);
    SQLFreeHandle(SQL_HANDLE_ENV, env);

    return 0;
}