达梦数据库批量插入及文件存入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; }
