c++操作数据库(增删改查)------otl库----c++

包含头文件：#include <otl/otlv4.h>

一, insert 插入数据库

 	#include <iostream>
#include <otl/otlv4.h> // 请确保正确包含 OTL 头文件

void insertDataIntoTable(const std::string& server, const std::string& database, const std::string& uid, const std::string& pwd, int field1, const std::string& field2_str)
{
    otl_connect db; // 创建 otl_connect 对象，并连接到数据库

    try
    {
        // 连接数据库
        db.rlogon(("Driver={SQL Server};Server=" + server + ";Port:1433;Database=" + database + ";UID=" + uid + ";PWD=" + pwd).c_str());

        // 打开插入流
        otl_stream insert_stream;
        insert_stream.open(256, "insert into data_name.dba.Table_name (字段1, 时间字段2，时间字段3) values (:f1<int>, :f2<timestamp>，GETDATE())", db);

        // 准备变量值
        otl_value<int> a(field1);
        
        otl_datetime b;
        b.from<otl_datetime::Timestamp>(field2_str.c_str()); // 将字符串转化为时间格式

        // 将数据写入
        otl_write_row(insert_stream, a, b);

        insert_stream.flush();
        insert_stream.close();
    }
    catch (otl_exception& e)
    {
        std::cout << "insert error" << std::endl;
        LOG_F(ERROR, "insert error, \nsql:%s \nmessage:%s \n state:%s", e.stm_text, e.msg, e.sqlstate);
    }
    catch (const std::exception& p)
    {
        LOG_F(ERROR, "%s", p.what());
    }
    // 断开数据库连接
    db.logoff();
}

int main()
{
    // 调用函数插入数据
    insertDataIntoTable("****", "***", "sa", "****", 4, "2023-11-30 12:34:56");
    return 0;
}

二, select 查询

select 写法一

#include <iostream>
#include <otl/otlv4.h> // 请确保正确包含 OTL 头文件

bool fetchDataFromDatabase(const std::string& server, const std::string& database, const std::string& uid, const std::string& pwd, std::map<std::string, std::string>& adcd2adnm)
{
    otl_connect db; // 创建 otl_connect 对象，并连接到数据库

    try
    {
        // 连接数据库
        db.rlogon(("Driver={SQL Server};Server=" + server + ";Port:1433;Database=" + database + ";UID=" + uid + ";PWD=" + pwd).c_str());

        // 查询 WarningObject 表数据
        otl_stream warningObjectStream;
        warningObjectStream.open(512, "SELECT NAME, WARNINGGRADEID, BEGINTIME FROM 库名", db);

        for (auto& warn : warningObjectStream)
        {
            otl_value<std::string> name;
            otl_value<int> warninggradeid;
            otl_value<otl_datetime> begintime;

            otl_read_row(warn, name, warninggradeid, begintime);

            std::string nameStr = name.v;
            int warninggradeidValue = warninggradeid.v;
            std::string begintimeStr = otl_datetime_to_string(begintime.v);  // 将otl_datetime类型转为string类型

        }
        warningObjectStream.close(); // 关闭查询流

        // 断开数据库连接
        db.logoff();

        return true;
    }
    catch (otl_exception& e)
    {
        std::cout << "select error" << std::endl;
        LOG_F(ERROR, "select error, \n sql:%s \nmessage:%s \n state:%s", e.stm_text, e.msg, e.sqlstate);
        db.logoff();
        return false;
    }
    catch (const std::exception& p)
    {
        LOG_F(ERROR, "%s", p.what());
        db.logoff();
        return false;
    }
}

int main()
{
    std::map<std::string, std::string> adcd2adnm;
    // 调用函数获取数据
    if (fetchDataFromDatabase("****", "***", "sa", "****", adcd2adnm))
    {
        // 已成功获取
    }

    return 0;
}

bool select(const std::string& odbc, const std::string& sql, const std::string& btm, const std::string& etm, std::vector<RainfallData>& VecData)
{
    bool status = false;
    otl_connect db;
    try
    {
        db.rlogon(odbc.c_str());
        std::string select_sql = TFormat::Format(sql, btm, etm);
        otl_stream rainfallStream;
        std::cout << select_sql << std::endl;
        Stream.open(512, select_sql.c_str(), db);
        for (auto& data : rainfallStream)
        {
            Data rainfallData;
            otl_value<std::string> stcd, time;
            otl_value<double> result;
            otl_read_row(data, stcd, time, result);
            Data .stcd = stcd.v;
            Data .value = result.v;
            Data .data_time = time.v;
            VecData.push_back(rainfallData);
        }
        Stream.close();
        db.logoff();
        int a = 0;
        status = true;
    }
    catch (otl_exception& e)
    {
        std::string linksql = TFormat::Format(sql, btm, etm);
        SU_ERROR_PRINT("select() odbc: "); std::cout  << odbc << std::endl;
        SU_ERROR_PRINT("select_all() sql: ");  std::cout <<  linksql << std::endl;
        std::cout << "select(): " << e.msg << std::endl;
        std::cout << "select(): " << e.code << std::endl;
        std::cout << "select(): " << e.sqlstate << std::endl;
        db.logoff();
    }
    catch (std::exception& ex)
    {
        std::string linkodbc = odbc;
        std::string linksql = TFormat::Format(sql, btm, etm);
        SU_ERROR_PRINT("select() odbc: ");  std::cout << linkodbc << std::endl;
        SU_ERROR_PRINT("select() sql: ");   std::cout << linksql << std::endl;
        std::cout << "select() exception: " << ex.what() << std::endl;
        db.logoff();
    }

    return status;
}

select 写法二

#include <iostream>
#include <otl/otlv4.h> // Make sure to include the correct OTL header

void fetchDataAndPopulateMap(double fieldValue1, double fieldValue2, std::map<int, std::map<int, int>>& lng_lat_pid)
{
    otl_connect db; // 创建otl_connect对象并连接到数据库
    try
    {
        otl_stream query_stream;
        db.rlogon("Driver={SQL Server};Server=****;Port:1433;Database=***;UID=sa;PWD=****");
        query_stream.open(256, "select 查询字段 form 库名.dbo.表名 where 表字段1=:f1<float> and 表字段2=:f2<float>", db, otl_implicit_select);

        // Bind the first input parameter
        query_stream << fieldValue1;
        // Bind the second input parameter
        query_stream << fieldValue2;

        otl_value<int> pid;
        otl_value<double> lat;
        otl_value<double> lon;

        while (!query_stream.eof())
        {
            otl_read_row(query_stream, pid, lat, lon);
            int x = std::round(lat.v * 1e4);
            int y = std::round(lon.v * 1e4);
            lng_lat_pid[x][y] = pid.v;
        }

        query_stream.close();
    }
    catch (otl_exception& e)
    {
        std::cout << "select error" << std::endl;
        LOG_F(ERROR, "select error, \n sql:%s \nmessage:%s \n state:%s", e.stm_text, e.msg, e.sqlstate);
    }
    catch (const std::exception& p)
    {
        LOG_F(ERROR, "%s", p.what());
    }
    db.logoff();
}

int main()
{
    std::map<int, std::map<int, int>> lng_lat_pid;
    // 调用函数以获取数据并填充地图
    fetchDataAndPopulateMap(db, 10.0, lng_lat_pid);

    return 0;
}

三, update 修改

#include <iostream>
#include <otl/otlv4.h> 

bool updateWarnRecord(const std::string& dbConnectionString)
{
    otl_connect db;  // Database connection object
    try
    {
        // Connect to the database using the provided connection string
        db.rlogon(dbConnectionString.c_str());

        std::string update_sql = "UPDATE WarnRecord SET WarnETM =:f1<timestamp>, StatusID = 30 WHERE WarnID > 100;";
        otl_value<otl_datetime> wetm = "YYYY-MM-DD";
        otl_stream update_query;

        // Execute the update operation
        update_query.open(512, update_sql.c_str(), db);
        update_query << wetm;
        update_query.flush();
        update_query.close();

        db.logoff(); // Disconnect from the database
        return true;
    }
    catch (otl_exception& ex)
    {
        // Handle exceptions
        std::cout << "OTL Exception: " << ex.msg << std::endl;
        std::cout << "Oracle code: " << ex.code << std::endl;
        std::cout << "Oracle message: " << ex.sqlstate << std::endl;
        db.logoff(); // Disconnect from the database
        return false;
    }
}

int main()
{
    std::string dbConnectionString = "Replace with your database connection string";
    
    // Call the function to update the WarnRecord
    bool success = updateWarnRecord(dbConnectionString);

    if (success)
        std::cout << "Update successful." << std::endl;
    else
        std::cout << "Update failed." << std::endl;

    return 0;
}

四, delete 删除

#include <iostream>
#include <otl/otlv4.h> // Make sure to include the correct OTL header

bool deleteRecords(const std::string& dbConnectionString)
{
    otl_connect db;  // Database connection object
    try
    {
        // Connect to the database using the provided connection string
        db.rlogon(dbConnectionString.c_str());

        std::string delete_sql = "DELETE FROM Warn WHERE ADCD > 10";
        otl_stream delete_query;

        // Execute the delete operation
        delete_query.open(128, delete_sql.c_str(), db);
        delete_query.flush();
        delete_query.close();

        db.logoff(); // Disconnect from the database
        return true;
    }
    catch (otl_exception& ex)
    {
        // Handle exceptions
        std::cout << "OTL Exception: " << ex.msg << std::endl;
        std::cout << "Oracle code: " << ex.code << std::endl;
        std::cout << "Oracle message: " << ex.sqlstate << std::endl;
        db.logoff(); // Disconnect from the database
        return false;
    }
}

int main()
{
    std::string dbConnectionString = "Replace with your database connection string";
    
    // Call the function to delete records
    bool success = deleteRecords(dbConnectionString);

    if (success)
        std::cout << "Deletion successful." << std::endl;
    else
        std::cout << "Deletion failed." << std::endl;

    return 0;
}