达梦数据库批量插入及文件存入blob

DPI 编程指南 | 达梦技术文档 (dameng.com)

dpi_exec_add_batch

函数

DPIRETURN
dpi_exec_add_batch(
    dhstmt          dpi_hstmt
);

功能

发送一行绑定的数据到 dpi 做数据转换及存储

参数

dpi_hstmt

输入参数，语句句柄。

返回值

DSQL_SUCCESS

DSQL_SUCCESS_WITH_INFO

DSQL_ERROR

DSQL_INVALID_HANDLE

说明

此接口函数提供单行数据绑定插入的缓存，用户绑定参数后，调用此接口进行数据缓存，结合接口 dpi_exec_batch 可以进行数据插入。接口不支持输出参数，大字段及延迟绑定数据方式。

 dpi_exec _batch

函数

DPIRETURN
dpi_exec_batch(
    dhstmt          dpi_hstmt
);

功能

发送已经缓存的数据到服务器进行插入操作

参数

dpi_hstmt

输入参数，语句句柄。

返回值

DSQL_SUCCESS

DSQL_SUCCESS_WITH_INFO

DSQL_ERROR

DSQL_INVALID_HANDLE

说明

此接口发送 dpi_exec_add_batch 接口所缓存的数据到服务器进行插入操作。

 批处理代码

#define DPIRETURN_CHECH(rt, hndl_type, hndl) if (!DSQL_SUCCEEDED(rt)){    dpi_err_msg_print(hndl_type, hndl);    return;}
void dpi_err_msg_print(sdint2 hndl_type, dhandle hndl)
{
    sdint4 err_code;
    sdint2 msg_len;
    sdbyte err_msg[SDBYTE_MAX];

    /* 获取错误信息集合 */
    dpi_get_diag_rec(hndl_type, hndl, 1, &err_code, err_msg, sizeof(err_msg), &msg_len);
    printf("err_msg = %s, err_code = %d\n", err_msg, err_code);
}

dpi_timestamp_t DMSql::iTimeToDPITimestamp(__int64 iTime)
{
    FileTimeToLocalFileTime((FILETIME*)(&iTime), (FILETIME*)(&iTime));
    SYSTEMTIME st;
    FileTimeToSystemTime((FILETIME*)(&iTime), &st);

    dpi_timestamp_t return_time;
    return_time.year = st.wYear;
    return_time.month = st.wMonth;
    return_time.day = st.wDay;
    return_time.hour = st.wHour;
    return_time.minute = st.wMinute;
    return_time.second = st.wSecond;
    return_time.fraction = st.wMilliseconds;
    return return_time;
}
std::wstring DMSql::StrToUSC(std::string str)
{
    std::wstring_convert<std::codecvt_utf8<wchar_t>> wcv;
    std::wstring wideStr = wcv.from_bytes(str);
    return wideStr;
}
void DMSql::insertMotiondataTable(std::map<int, MotionData*> m_mapMotionDatas, std::map<int,std::string> m_cgfName, __int64 time)
{
    sdint4 in_c1;
    unsigned char in_c2[255];    
    ddouble in_c3, in_c4, in_c5, in_c6, in_c7;
    std::wstring cgfName;

    dpi_timestamp_t simTime = iTimeToDPITimestamp(time);
    slength in_c1_ind_ptr, in_c2_ind_ptr, in_c3_ind_ptr, in_c4_ind_ptr, in_c5_ind_ptr, in_c6_ind_ptr, in_c7_ind_ptr, in_c8_ind_ptr=sizeof(simTime);

    rt = dpi_alloc_stmt(hcon, &hstmt);
    DPIRETURN_CHECH(rt, DSQL_HANDLE_STMT, hstmt);
    std::string  sql="insert into \""+DM_MODE+"\".\"maneuver\"(ID,NAME,LNG,LAT,ALT,VEL,COURSE,TIME) values(?,?,?,?,?,?,?,?)";
    rt = dpi_prepare(hstmt, (sdbyte*)sql.c_str());

    for (std::map<int, MotionData*>::iterator it = m_mapMotionDatas.begin(); it != m_mapMotionDatas.end(); it++)
    {
        in_c1 = it->first;
        auto fi = m_cgfName.find(in_c1);
        if (fi == m_cgfName.end())
        {
            continue;
        }
        cgfName = StrToUSC(fi->second);
        WideCharToMultiByte(CP_ACP,0,cgfName.c_str(),-1,(LPSTR)in_c2,sizeof(in_c2),NULL,NULL);

        in_c3 = it->second->lng;
        in_c4 = it->second->lat;
        in_c5 = it->second->alt;
        in_c6 = it->second->speed;
        in_c7 = it->second->course;

        in_c1_ind_ptr=sizeof(in_c1);
        in_c2_ind_ptr=strlen((char*)in_c2);
        in_c3_ind_ptr=sizeof(in_c3);
        in_c4_ind_ptr=sizeof(in_c4);
        in_c5_ind_ptr=sizeof(in_c5);
        in_c6_ind_ptr=sizeof(in_c6);
        in_c7_ind_ptr=sizeof(in_c7);

        //rt = dpi_prepare(hstmt, (sdbyte*)sql.c_str());
        rt = dpi_bind_param(hstmt, 1, DSQL_PARAM_INPUT, DSQL_C_SLONG, DSQL_INT, sizeof(it->first), 0, &in_c1, sizeof(in_c1), &in_c1_ind_ptr);
        rt = dpi_bind_param(hstmt, 2, DSQL_PARAM_INPUT, DSQL_C_NCHAR, DSQL_VARCHAR, sizeof(in_c2), 0, in_c2, sizeof(in_c2), &in_c2_ind_ptr);
        rt = dpi_bind_param(hstmt, 3, DSQL_PARAM_INPUT, DSQL_C_DOUBLE, DSQL_DOUBLE, sizeof(in_c3), 0, &in_c3, sizeof(in_c3), &in_c3_ind_ptr);
        rt = dpi_bind_param(hstmt, 4, DSQL_PARAM_INPUT, DSQL_C_DOUBLE, DSQL_DOUBLE, sizeof(in_c4), 0, &in_c4, sizeof(in_c4), &in_c4_ind_ptr);
        rt = dpi_bind_param(hstmt, 5, DSQL_PARAM_INPUT, DSQL_C_DOUBLE, DSQL_DOUBLE, sizeof(in_c5), 0, &in_c5, sizeof(in_c5), &in_c5_ind_ptr);
        rt = dpi_bind_param(hstmt, 6, DSQL_PARAM_INPUT, DSQL_C_DOUBLE, DSQL_DOUBLE, sizeof(in_c6), 0, &in_c6, sizeof(in_c6), &in_c6_ind_ptr);
        rt = dpi_bind_param(hstmt, 7, DSQL_PARAM_INPUT, DSQL_C_DOUBLE, DSQL_DOUBLE, sizeof(in_c7), 0, &in_c7, sizeof(in_c7), &in_c7_ind_ptr);
        rt = dpi_bind_param(hstmt, 8, DSQL_PARAM_INPUT, DSQL_C_TIMESTAMP, DSQL_TIMESTAMP, sizeof(simTime), 0, &simTime, sizeof(simTime), &in_c8_ind_ptr);

        rt = dpi_exec_add_batch(hstmt);
        DPIRETURN_CHECH(rt, DSQL_HANDLE_STMT, hstmt);
    }

    rt = dpi_exec_batch(hstmt);
    DPIRETURN_CHECH(rt, DSQL_HANDLE_STMT, hstmt);

    rt = dpi_free_stmt(hstmt);
}

 网上看到一些写的很好的数据库批量插入的文章

大批量数据高效插入数据库表 - myseries - 博客园 (cnblogs.com

更新语句

std::string  sql="update \""+DM_MODE+"\".\"maneuver\" set c_ID=?, NAME=?, LNG=?, LAT=?, ALT=?, VEL=?, COURSE=?, TIME=? where id=" + std::to_string(ID);

参数绑定与insert语句相同

上传文件到blob中 ，上传三个文件

bool DMSQL::upLoadScenario(std::string scenarioName, std::string purpose, std::string creator_name)
{
    std::string filePath = MaxPath + "\\Data\\Stored\\" + scenarioName + ".mission", str, tName, tfilePath, bName;
    tName = theSampleWidget.m_pTSettingsWidget->GetTFileName(filePath);
    if (tName == "")
    {
        QString log = QStringLiteral("文件打开失败%1，无法获取对应的xx库xx库").arg(eStringUtil::ToUnicode(filePath));
        theSampleWidget.updateShowLog(log);
    }
    else
    {
        upLoadT(tName);
        tfilePath = MaxPath + "\\Data\\TKB\\" + tName;
        bName = theSampleWidget.m_pTSettingsWidget->GetBFileName(tfilePath);
        bName = fileNameWithoutPrefix(bName, ".bkb");
        tName = fileNameWithoutPrefix(tName, ".tkb");
    }

    int actionID = selectFileId("action", (char*)bName.c_str());
    int equipmentID = selectFileId("equipment", (char*)tName.c_str());

    //////////////////////////////////////////////////////
    FILE*  pfile = NULL;
    sdbyte  tmpbuf[CHARS], c1[20], c3 = DSQL_DATA_AT_EXEC, c4[50], c7 = DSQL_DATA_AT_EXEC, c8 = DSQL_DATA_AT_EXEC, c9[20];

    WideCharToMultiByte(CP_ACP, 0, StrToUSC(scenarioName).c_str(), -1, (LPSTR)c1, sizeof(c1), NULL, NULL);
    WideCharToMultiByte(CP_ACP, 0, StrToUSC(purpose).c_str(), -1, (LPSTR)c4, sizeof(c4), NULL, NULL);
    WideCharToMultiByte(CP_ACP, 0, StrToUSC(creator_name).c_str(), -1, (LPSTR)c9, sizeof(c9), NULL, NULL);

    dpi_timestamp_t c2 = currentTimeToDPITimestamp();
    slength c1_ind_ptr = strlen((char*)c1), c3_ind_ptr = DSQL_DATA_AT_EXEC, c4_ind_ptr = strlen((char*)c4), c5_ind_ptr = sizeof(actionID),
        c6_ind_ptr = sizeof(equipmentID), c7_ind_ptr = DSQL_DATA_AT_EXEC, c8_ind_ptr = DSQL_DATA_AT_EXEC, c9_ind_ptr = strlen((char*)c9),
        len = 0, val_len = 0;
    dpointer c3_val_ptr;

    //上传前查看是否已存在文件
    int ID = selectFileId("scenario", (char*)c1);
    if (ID)
    {
        str = "update \"" + DM_MODE + "\".\"scenario\" set name=?, create_time=?,mission=?, purpose=?,action_id=?,equipment_id=?,entitiesinfo=?,info=?,creator_name=? where id=" + std::to_string(ID);
    }
    else
    {
        str = "insert into \"" + DM_MODE + "\".\"scenario\" (\"name\",\"create_time\",\"mission\",\"purpose\",\"action_id\",\"equipment_id\",\"entitiesinfo\",\"info\",\"creator_name\") values(?,?,?,?,?,?,?,?,?)";
    }
    rt = dpi_alloc_stmt(hcon, &hstmt);
    if (!DSQL_SUCCEEDED(rt))
    {
        dpi_err_msg_print(DSQL_HANDLE_STMT, hstmt);
        return false;
    }
    rt = dpi_prepare(hstmt, (sdbyte*)str.c_str());
    if (!DSQL_SUCCEEDED(rt))
    {
        dpi_err_msg_print(DSQL_HANDLE_STMT, hstmt);
        return false;
    }
    rt = dpi_bind_param(hstmt, 1, DSQL_PARAM_INPUT, DSQL_C_NCHAR, DSQL_CHAR, sizeof(c1), 0, &c1, sizeof(c1), &c1_ind_ptr);
    rt = dpi_bind_param(hstmt, 2, DSQL_PARAM_INPUT, DSQL_C_TIMESTAMP, DSQL_TIMESTAMP, sizeof(c2), 0, &c2, sizeof(c2), NULL);
    rt = dpi_bind_param(hstmt, 3, DSQL_PARAM_INPUT, DSQL_C_BINARY, DSQL_BLOB, sizeof(c3), 0, &c3, sizeof(c3), &c3_ind_ptr);
    rt = dpi_bind_param(hstmt, 4, DSQL_PARAM_INPUT, DSQL_C_NCHAR, DSQL_VARCHAR, sizeof(c4), 0, &c4, sizeof(c4), &c4_ind_ptr);
    rt = dpi_bind_param(hstmt, 5, DSQL_PARAM_INPUT, DSQL_C_SLONG, DSQL_INT, sizeof(actionID), 0, &actionID, sizeof(actionID), &c5_ind_ptr);
    rt = dpi_bind_param(hstmt, 6, DSQL_PARAM_INPUT, DSQL_C_SLONG, DSQL_INT, sizeof(equipmentID), 0, &equipmentID, sizeof(equipmentID), &c6_ind_ptr);
    rt = dpi_bind_param(hstmt, 7, DSQL_PARAM_INPUT, DSQL_C_BINARY, DSQL_BLOB, sizeof(c7), 0, &c7, sizeof(c7), &c7_ind_ptr);
    rt = dpi_bind_param(hstmt, 8, DSQL_PARAM_INPUT, DSQL_C_BINARY, DSQL_BLOB, sizeof(c8), 0, &c8, sizeof(c8), &c8_ind_ptr);
    rt = dpi_bind_param(hstmt, 9, DSQL_PARAM_INPUT, DSQL_C_NCHAR, DSQL_CHAR, sizeof(c9), 0, &c9, sizeof(c9), &c9_ind_ptr);

    if ((rt = dpi_exec(hstmt)) == DSQL_NEED_DATA)
    {
        if (dpi_param_data(hstmt, &c3_val_ptr) == DSQL_NEED_DATA) /* 绑定数据 */
        {
            pfile = fopen((const char *)filePath.c_str(), "rb");
            if (pfile == NULL)
            {return false;
            }
            while (!feof(pfile))
            {
                len = fread(tmpbuf, sizeof(char), CHARS, pfile);
                if (len <= 0)
                {
                    return false;
                }
                dpi_put_data(hstmt, tmpbuf, len);
            }
            fclose(pfile);
        }
        if (dpi_param_data(hstmt, &c3_val_ptr) == DSQL_NEED_DATA) /* 绑定数据 */
        {
            filePath = MaxsimPath + "\\Data\\Stored\\" + scenarioName + ".mission.EntitiesInfo";
            pfile = fopen((const char *)filePath.c_str(), "rb");
            if (pfile == NULL)
            {return false;
            }
            while (!feof(pfile))
            {
                len = fread(tmpbuf, sizeof(char), CHARS, pfile);
                if (len <= 0)
                {
                    return false;
                }
                dpi_put_data(hstmt, tmpbuf, len);
            }
            fclose(pfile);
        }
        if (dpi_param_data(hstmt, &c3_val_ptr) == DSQL_NEED_DATA) /* 绑定数据 */
        {
            filePath = MaxsimPath + "\\Data\\Stored\\" + scenarioName + ".mission.info";
            pfile = fopen((const char *)filePath.c_str(), "rb");
            if (pfile == NULL)
            {return false;
            }
            while (!feof(pfile))
            {
                len = fread(tmpbuf, sizeof(char), CHARS, pfile);
                if (len <= 0)
                {
                    return false;
                }
                dpi_put_data(hstmt, tmpbuf, len);
            }
            fclose(pfile);
        }
        rt = dpi_param_data(hstmt, &c3_val_ptr); /* 绑定数据 */
    }
    rt = dpi_free_stmt(hstmt);
    if (!DSQL_SUCCEEDED(rt))
    {
        dpi_err_msg_print(DSQL_HANDLE_STMT, hstmt);
        return false;
    }
    return true;
}

下载blob到文件中，下载三个文件

void DMSQL::downLoadScenario(ScenarioDataInfo* c_DataInfo)
{
    FILE*  pfile = NULL;
    sdbyte  tmpbuf[CHARS];
    slength len = 0;
    slength val_len = 0;
    std::string path = MaxPath + "\\Data\\Stored\\";
    std::string file;
    sdbyte  out_c1[20] = { 0 };
    slength out_c1_ind = 0;
    ulength row_num = 0;
    int dir = createDirectory(path);
    rt = dpi_alloc_stmt(hcon, &hstmt);
    if (!DSQL_SUCCEEDED(rt))
    {
        dpi_err_msg_print(DSQL_HANDLE_STMT, hstmt);
    }
    //查询数据
    std::string str = "select name,mission,entitiesinfo,info from \"" + DM_MODE + "\".\"scenario\" where id=" + std::to_string(c_DataInfo->scenarioID);
    rt = dpi_exec_direct(hstmt, (sdbyte*)str.c_str());
    if (!DSQL_SUCCEEDED(rt))
    {
        dpi_err_msg_print(DSQL_HANDLE_STMT, hstmt);
    }
    dpi_bind_col(hstmt, 1, DSQL_C_NCHAR, &out_c1, sizeof(out_c1), &out_c1_ind);
    while (dpi_fetch(hstmt, &row_num) != DSQL_NO_DATA)
    {
        file = path + (char*)out_c1 + ".mission";
        c_DataInfo->filePath = eStringUtil::ToUnicode(file);
        pfile = fopen((const char *)file.c_str(), "wb");
        if (pfile == NULL)
        {
            //文件空
        }
        while (DSQL_SUCCEEDED(dpi_get_data(hstmt, 2, DSQL_C_BINARY, tmpbuf, CHARS, &val_len)))
        {
            len = val_len > CHARS ? CHARS : val_len;
            fwrite(tmpbuf, sizeof(char), len, pfile);
        }
        fclose(pfile);
        file = file + ".EntitiesInfo";
        pfile = fopen((const char *)file.c_str(), "wb");
while (DSQL_SUCCEEDED(dpi_get_data(hstmt, 3, DSQL_C_BINARY, tmpbuf, CHARS, &val_len)))
        {
            len = val_len > CHARS ? CHARS : val_len;
            fwrite(tmpbuf, sizeof(char), len, pfile);
        }
        fclose(pfile);
        file = path + (char*)out_c1 + ".mission.info";
        pfile = fopen((const char *)file.c_str(), "wb");
while (DSQL_SUCCEEDED(dpi_get_data(hstmt, 4, DSQL_C_BINARY, tmpbuf, CHARS, &val_len)))
        {
            len = val_len > CHARS ? CHARS : val_len;
            fwrite(tmpbuf, sizeof(char), len, pfile);
        }
        fclose(pfile);
    }
}

几个杂项

QDateTime DMSQL::DPITimestampToQDateTime(dpi_timestamp_t dpiTime)
{
    QDateTime qDateTime;
    QString qYear, qMonth, qDay, qHour, qMinute, qSecond, qDT;
    qYear = QString::number(dpiTime.year);
    qMonth = QString::number(dpiTime.month);
    if (dpiTime.month < 10)
    {
        qMonth = "0" + qMonth;
    }
    qDay = QString::number(dpiTime.day);
    if (dpiTime.day < 10)
    {
        qDay = "0" + qDay;
    }
    qHour = QString::number(dpiTime.hour);
    if (dpiTime.hour < 10)
    {
        qHour = "0" + qHour;
    }
    qMinute = QString::number(dpiTime.minute);
    if (dpiTime.minute < 10)
    {
        qMinute = "0" + qMinute;
    }
    qSecond = QString::number(dpiTime.second);
    if (dpiTime.second < 10)
    {
        qSecond = "0" + qSecond;
    }
    qDT = qYear + "-" + qMonth + "-" + qDay + " " + qHour + ":" + qMinute + ":" + qSecond;
    qDateTime = QDateTime::fromString(qDT, "yyyy-MM-dd hh:mm:ss");
    return qDateTime;
}

std::string DMSQL::GetFileNameFromFilePath(std::string Path)
{
    std::string fileName;
    char *tmpStr, *token, *strc = const_cast<char*>(Path.c_str());
    // 分解字符串
    token = strtok_s(strc, "\\", &tmpStr);
    while (token)
    {
        if ((std::string)tmpStr == "")
        {
            fileName = token;
            return fileName;
        }
        token = strtok_s(NULL, "\\", &tmpStr);
    }
    return Path;
}

std::wstring DMSQL::StrToUSC(std::string str)
{
    /*std::wstring_convert<std::codecvt_utf8<wchar_t>> wcv;
    std::wstring wideStr = wcv.from_bytes(str);
    return wideStr;*/

    std::wstring result;
    int len = MultiByteToWideChar(CP_ACP, 0, str.c_str(), str.size(), NULL, 0);
    TCHAR* buffer = new TCHAR[len + 1];
    MultiByteToWideChar(CP_ACP, 0, str.c_str(), str.size(), buffer, len);
    buffer[len] = '\0';
    result.append(buffer);
    delete[] buffer;
    return result;
}

std::string DMSQL::fileNameWithoutPrefix(std::string filename, std::string prefix)
{
    int nLength = strlen(filename.c_str()) - strlen(prefix.c_str());
    return filename.substr(0, nLength);
}

int DMSQL::createDirectory(std::string path)
{
    int len = path.length();
    char tmpDirPath[256] = { 0 };
    for (int i = 0;i < len;i++)
    {
        tmpDirPath[i] = path[i];
        if (tmpDirPath[i] == '\\')
        {
            if (_access(tmpDirPath, 0) == -1)
            {
                int ret = _mkdir(tmpDirPath);
                if (ret == -1)
                {
                    return ret;
                }
            }
        }
    }
    return 0;
}

 

dpi_timestamp_t DMSQL::currentTimeToDPITimestamp()
{
    dpi_timestamp_t return_time;
    time_t now_time = time(NULL);
    tm* t_tm = localtime(&now_time);
    return_time.year = t_tm->tm_year + 1900;
    return_time.month = t_tm->tm_mon + 1;
    return_time.day = t_tm->tm_mday;
    return_time.hour = t_tm->tm_hour;
    return_time.minute = t_tm->tm_min;
    return_time.second = t_tm->tm_sec;
    return_time.fraction = 0;
    return return_time;
}