void MergedAuthorDao::add(vector<int>& froms, int to) { Connection* conn = NULL; PreparedStatement* stat = NULL; try { conn = ConnectionPool::getInstance()->getConnection(); conn->setAutoCommit(false); stat = conn->prepareStatement("insert into na_author_map(from_naid,to_naid) values(?,?)"); stat->setInt(2, to); for (int from : froms) { stat->setInt(1, from); stat->executeUpdate(); } conn->commit(); conn->setAutoCommit(true); ConnectionPool::close(conn, stat, NULL); } catch (sql::SQLException &e) { LOG(ERROR) << boost::str(boost::format("# ERR: SQLException in %1% %2%") % __FILE__ %__FUNCTION__); LOG(ERROR) << boost::str(boost::format("%1% error code %2%") %e.what() % e.getErrorCode()); } }
void JDBCTemplate::executeUpdate(std::string sql, int type, object *obj) { Connection *conn = 0; try { //调用钩子方法 conn = this->getConnection(); PreparedStatement *pstmt = conn->prepareStatement(sql); //调用原语操作 this->setUpdateSqlValue(type,pstmt,obj); pstmt->executeUpdate(); pstmt->close(); } catch(Exception *err) { err->printStackTrace(); } //JAVA TO C++ CONVERTER TODO TASK: There is no native C++ equivalent to the exception 'finally' clause: finally { try { conn->close(); } catch (SQLException *e) { e->printStackTrace(); } } }
/** * Insert the sheet into the database */ int SheetController::AddSheet(Sheet& sheet) { PreparedStatement* stmt = conn->prepareStatement("INSERT INTO sheets (name, username, layoutid) VALUES (?,?,?)"); //Populate the query with the values from the passed sheet stmt->setString(1, sheet.name); stmt->setString(2, sheet.username); stmt->setInt(3, sheet.layoutId); //Insert stmt->executeUpdate(); delete stmt; //Create another query to get the ID of the inserted sheet Statement* lastStmt = conn->createStatement(); ResultSet* rs = lastStmt->executeQuery("SELECT LAST_INSERT_ID()"); if(rs != NULL) { while(rs->next()) { int lastId = rs->getInt("LAST_INSERT_ID()"); delete rs; delete lastStmt; return lastId; } } else { delete lastStmt; return -1; } return -1; }
/** * Insert the stat into the database */ int StatController::AddStat(Stat& stat) { PreparedStatement* stmt = conn->prepareStatement("INSERT INTO stats (users, sheets, feeds, items, comments) VALUES (?,?,?,?,?)"); //Populate the query based on the passed stat stmt->setInt(1, stat.users); stmt->setInt(2, stat.sheets); stmt->setInt(3, stat.feeds); stmt->setInt(4, stat.items); stmt->setInt(5, stat.comments); //Insert stmt->executeUpdate(); delete stmt; //Create another query to get the ID of the inserted stat Statement* lastStmt = conn->createStatement(); ResultSet* rs = lastStmt->executeQuery("SELECT LAST_INSERT_ID()"); if(rs != NULL) { while(rs->next()) { int lastId = rs->getInt("LAST_INSERT_ID()"); delete rs; delete lastStmt; return lastId; } } else { delete lastStmt; return -1; } return -1; }
int Call::DBwrite(Connection *sqlCon) { if (!sqlCon || !id) return -1; PreparedStatement *pstmt = sqlCon->prepareStatement( "UPDATE calls SET phone=(?), client=(?), translator=(?), client_country=(?), translator_country=(?), lang=(?), price=(?), start_time=(?), accounted=(?), cost=(?), error=(?) WHERE id=(?)"); pstmt->setInt(2, client); pstmt->setInt(3, translator); pstmt->setInt(1, false); pstmt->setString(4, COUNTRY_UNKNOWN); pstmt->setString(5, COUNTRY_UNKNOWN); pstmt->setString(6, translateLang.c_str()); pstmt->setInt(7, price); char *time = asctime(localtime(&start_time)); if (start_time) pstmt->setDateTime(8, time); else pstmt->setNull(8, 0); pstmt->setInt(9, accounted); pstmt->setInt(10, cost); pstmt->setInt(11, getState() == ERROR); pstmt->setInt(12, id); int ret; try { ret = pstmt->executeUpdate(); } catch (SQLException &ex) { log(LOG_ERROR, "[%s] MySQL error(%d): %s", __func__, ex.getErrorCode(), ex.what()); delete pstmt; return 0; } delete pstmt; return ret == 1; }
/** * Delete the layout from the database */ void LayoutController::RemoveLayout(Layout& layout) { PreparedStatement* stmt = conn->prepareStatement("DELETE FROM layouts WHERE id = ?"); stmt->setInt(1, layout.id); //Delete by ID stmt->executeUpdate(); }
int nuodb_statement_execute(struct nuodb *db, struct nuodb_statement *st, int64_t *rows_affected, int64_t *last_insert_id) { PreparedStatement *stmt = reinterpret_cast<PreparedStatement *>(st); try { stmt->executeUpdate(); return fetchExecuteResult(db, stmt, rows_affected, last_insert_id); } catch (SQLException &e) { return setError(db, e); } }
/** * Delete the content placeholder from the database */ void ContentPlaceholderController::RemoveContentPlaceholder(int id) { PreparedStatement* stmt = conn->prepareStatement("DELETE FROM cph WHERE id = ?"); stmt->setInt(1, id); //Remove the record by ID stmt->executeUpdate(); delete stmt; }
/** * Delete the stat from the database */ void StatController::RemoveStat(int id) { PreparedStatement* stmt = conn->prepareStatement("DELETE FROM stats WHERE id = ?"); stmt->setInt(1, id); //Delete by ID stmt->executeUpdate(); delete stmt; }
int MySQL::Update(StringList* _list){ assert(NULL != _list); try{ if(conn_->isClosed()){ Connect(); } conn_->setAutoCommit(0); StringList::iterator it = _list->begin(); for(; it != _list->end(); it++){ PreparedStatement* pstmt = conn_->prepareStatement(*it); assert(pstmt); pstmt->executeUpdate(); } conn_->commit(); } catch(sql::SQLException &e){ conn_->rollback(); Error("MySQL exception: func=%s, line=%d, expno=%d, sqlstate=%s, expstr=%s", __func__, __LINE__, e.getErrorCode(), e.getSQLState().c_str(), e.what()); return -1; } catch (std::runtime_error &e) { conn_->rollback(); Error("Update runtime error: errstr=%s", e.what()); return -1; } return 1; } //Update List
string generateToken(string login) { try { PreparedStatement * prepstmt; prepstmt = conn -> prepareStatement("delete from tokens where userid=(select id from users where login=?);"); prepstmt->setString(1, login); prepstmt->executeUpdate(); prepstmt = conn->prepareStatement("insert into tokens (userid, token, expiry) values ((select id from users where login=?), ?, now()+interval ? hour);"); string token = genRandomAlphanumericStr(TOKEN_SIZE); prepstmt->setString(1, login); prepstmt->setString(2, token); prepstmt->setInt(3, TOKEN_VALID_TIME); prepstmt->executeUpdate(); conn->commit(); return token; } catch(exception e) { throw 2; } }
/** * Update the relevant content placeholder in the database */ void ContentPlaceholderController::UpdateContentPlaceholder(ContentPlaceholder& cph) { PreparedStatement* stmt = conn->prepareStatement("UPDATE cphs SET sheetid = ?, type = ?, column = ?, order = ? WHERE id = ?"); //Update with all parameters stmt->setInt(1, cph.sheetId); stmt->setInt(2, cph.type); stmt->setInt(3, cph.column); stmt->setInt(4, cph.order); stmt->setInt(5, cph.id); stmt->executeUpdate(); delete stmt; }
/** * Update the relevant sheet in the database */ void SheetController::UpdateSheet(Sheet& sheet) { PreparedStatement* stmt = conn->prepareStatement("UPDATE Sheets SET name = ?, username = ?, updated = ?, layoutid = ? WHERE id = ?"); //Populate the query with the values from the passed sheet stmt->setString(1, sheet.name); stmt->setString(2, sheet.username); stmt->setString(3, sheet.updated.ExportToMySQL()); stmt->setInt(4, sheet.layoutId); stmt->setInt(5, sheet.id); stmt->executeUpdate(); delete stmt; }
/** * Update the relevant stat in the database */ void StatController::UpdateStat(Stat& stat) { PreparedStatement* stmt = conn->prepareStatement("UPDATE stats SET users = ?, sheets = ?, feeds = ?, items = ?, comments = ? WHERE id = ?"); //Populate the query values using the passed stat stmt->setInt(1, stat.users); stmt->setInt(2, stat.sheets); stmt->setInt(3, stat.feeds); stmt->setInt(4, stat.items); stmt->setInt(5, stat.comments); stmt->setInt(6, stat.id); stmt->executeUpdate(); delete stmt; }
int PhoneCall::DBwrite(Connection *sqlCon) { if (!sqlCon || !id) return -1; PreparedStatement *pstmt = sqlCon->prepareStatement( "UPDATE calls SET phone=(?), client=(?), translator=(?), client_country=(?), translator_country=(?), lang=(?), price=(?), start_time=(?), accounted=(?), cost=(?), error=(?), request_time=(?), confirm_time=(?), accepted=(?) WHERE id=(?)"); pstmt->setInt(1, true); pstmt->setInt(2, client); pstmt->setInt(3, translator); pstmt->setString(4, ((PhoneCall *)this)->getClientCountry().c_str()); pstmt->setString(5, ((PhoneCall *)this)->getTranslatorCountry().c_str()); pstmt->setString(6, translateLang.c_str()); pstmt->setInt(7, price); char time[512]; strftime(time, 512, SQLTIME_FMT, localtime(&start_time)); if (start_time) pstmt->setDateTime(8, time); else pstmt->setNull(8, 0); pstmt->setInt(9, getAccountedTime()); pstmt->setInt(10, cost); pstmt->setInt(11, getState() == ERROR); strftime(time, 512, SQLTIME_FMT, localtime(&request_time)); if (request_time) pstmt->setString(12, time); else pstmt->setNull(12, 0); strftime(time, 512, SQLTIME_FMT, localtime(&confirm_time)); if (confirm_time) pstmt->setDateTime(13, time); else pstmt->setNull(13, 0); pstmt->setBoolean(14, accepted); pstmt->setInt(15, id); int ret; try { ret = pstmt->executeUpdate(); } catch (SQLException &ex) { log(LOG_ERROR, "[%s] MySQL error(%d): %s", __func__, ex.getErrorCode(), ex.what()); delete pstmt; return 0; } delete pstmt; return ret == 1; }
void commitNewMessage(Document & request) { validateTokenByName(request["data"][0]["sender"].GetString(), request["usertoken"].GetString()); try { PreparedStatement * prepstmt; prepstmt = conn -> prepareStatement("insert into msgs (senderuid, receiveruid, msg, datesent) values ((select id from users where (login = ?)), (select id from users where (login = ?)), ?, now());"); prepstmt->setString(1, request["data"][0]["sender"].GetString()); prepstmt->setString(2, request["data"][0]["receiver"].GetString()); prepstmt->setString(3, request["data"][0]["message"].GetString()); prepstmt->executeUpdate(); conn->commit(); return; throw 3; } catch(exception e) { throw 2; } }
vector<map<string, string>> retrievePendingMessages(Document & request) { int recvid= validateToken(request["usertoken"].GetString()); try { PreparedStatement * prepstmt; prepstmt = conn->prepareStatement("select senderuid, receiveruid, msg, datesent from msgs where (receiveruid=? and status=0);"); prepstmt->setInt(1, recvid); ResultSet * rs; rs = prepstmt->executeQuery(); prepstmt = conn->prepareStatement("update msgs set status=1 where receiveruid=?;"); //ATTENTION! Threading error can occur! prepstmt->setInt(1, recvid); prepstmt->executeUpdate(); conn->commit(); ResultSet * namers; prepstmt = conn->prepareStatement("select login from users where id=?;"); prepstmt->setInt(1, recvid); namers = prepstmt->executeQuery(); namers->next(); string receiver = namers->getString("login"); vector<map<string, string>> msgs; while(rs->next()){ map<string, string> onemsg; prepstmt = conn->prepareStatement("select login from users where id=?;"); prepstmt->setInt(1, rs->getInt("senderuid")); namers = prepstmt->executeQuery(); namers->next(); onemsg.insert(make_pair("sender", namers->getString("login"))); onemsg.insert(make_pair("receiver", receiver)); onemsg.insert(make_pair("message", rs->getString("msg"))); onemsg.insert(make_pair("datesent", rs->getString("datesent"))); //cout << namers->getString("login") << " " << rs->getString("msg") << endl; msgs.push_back(onemsg); } return msgs; } catch(exception e) { throw 2; } }
bool registerUser(Document & request) { try { PreparedStatement * prepstmt; string login, pwd, email; login = request["data"][0]["login"].GetString(); pwd = request["data"][0]["password"].GetString(); email = request["data"][0]["email"].GetString(); prepstmt = conn->prepareStatement("insert into users (login, pwdhash, email, dateregister) values (?, sha1(?), ?, now());"); prepstmt->setString(1, login); prepstmt->setString(2, pwd); prepstmt->setString(3, email); prepstmt->executeUpdate(); conn->commit(); return true; } catch(exception e) { throw 2; //todo make different exceptions processing } }
/** * Insert the content placeholder into the database */ int ContentPlaceholderController::AddContentPlaceholder(ContentPlaceholder& cph) { PreparedStatement* stmt = conn->prepareStatement("INSERT INTO cphs (sheetid, type, column, order) VALUES (?,?,?,?)"); //Set provided parameters stmt->setInt(1, cph.sheetId); stmt->setInt(2, cph.type); stmt->setInt(3, cph.column); stmt->setInt(4, cph.order); //Insert stmt->executeUpdate(); delete stmt; //Perform another query to get the index of the inserted content placeholder Statement* lastStmt = conn->createStatement(); ResultSet* rs = lastStmt->executeQuery("SELECT LAST_INSERT_ID()"); if(rs != NULL) { while(rs->next()) { int lastId = rs->getInt("LAST_INSERT_ID()"); delete rs; delete lastStmt; return lastId; } } else { delete lastStmt; return -1; } return -1; }
/** * Update the relevant content placeholder in the database, NULLs passed if that parameter is not to be updated */ void ContentPlaceholderController::UpdateContentPlaceholder(int id, int* sheetId, int* wid, bool* column, int* order) { string query = "UPDATE cph SET "; //For each parameter, check if it is provided, if it is add to the UPDATE statement if(sheetId != NULL) { query += "sheetid = ?"; } else { query += "sheetid = sheetid"; } if(wid != NULL) { query += ", wid = ?"; } else { query += ", wid = wid"; } if(column != NULL) { query += ", column = ?"; } else { query += ", column = column"; } if(order != NULL) { query += ", order = ?"; } else { query += ", order = order"; } query += " WHERE id = ?"; PreparedStatement* stmt = conn->prepareStatement(query); int param = 1; //Populate the parameters in the statement if(sheetId != NULL) { stmt->setInt(param, *sheetId); param++; } if(wid != NULL) { stmt->setInt(param, *wid); param++; } if(column != NULL) { stmt->setInt(param, *column); param++; } if(order != NULL) { stmt->setInt(param, *order); param++; } stmt->setInt(param, id); stmt->executeUpdate(); delete stmt; }
/** * Update the relevant sheet in the database, NULLs passed if that parameter is not to be updated */ void SheetController::UpdateSheet(int id, string* name, string* username, nfrd::misc::DateTime* updated, int* layoutId) { //Generate the update query string query = "UPDATE Sheets SET "; //If the value is null, set it to the existing value if(name != NULL) { query += "name = ?"; } else { query += "name = name"; } if(username != NULL) { query += ", username = ?"; } else { query += ", username = username"; } if(updated != NULL) { query += ", updated = ?"; } else { query += ", updated = updated"; } if(layoutId != NULL) { query += ", layoutid = ?"; } else { query += ", layoutid = layoutid"; } query += " WHERE id = ?"; PreparedStatement* stmt = conn->prepareStatement(query); int param = 1; //Add the values to the query if(name != NULL) { stmt->setString(param, *name); param++; } if(username != NULL) { stmt->setString(param, *username); param++; } if(updated != NULL) { stmt->setString(param, updated->ExportToMySQL()); param++; } if(layoutId != NULL) { stmt->setInt(param, *layoutId); param++; } stmt->setInt(param, id); stmt->executeUpdate(); delete stmt; }
/** * Update the relevant stat in the database, NULLs passed if that parameter is not to be updated */ void StatController::UpdateStat(int id, int* users, int* sheets, int* feeds, int* items, int* comments) { //Generate the update query string query = "UPDATE stats SET "; //If values are null, set the columns to equal their existing values if(users != NULL) { query += "users = ?"; } else { query += "users = users"; } if(sheets != NULL) { query += ", sheets = ?"; } else { query += ", sheets = sheets"; } if(feeds != NULL) { query += ", feeds = ?"; } else { query += ", feeds = feeds"; } if(items != NULL) { query += ", items = ?"; } else { query += ", items = items"; } if(comments != NULL) { query += ", comments = ?"; } else { query += ", comments = comments"; } query += " WHERE id = ?"; //Add the values to the statement PreparedStatement* stmt = conn->prepareStatement(query); int param = 1; if(users != NULL) { stmt->setInt(param, *users); param++; } if(sheets != NULL) { stmt->setInt(param, *sheets); param++; } if(feeds != NULL) { stmt->setInt(param, *feeds); param++; } if(items != NULL) { stmt->setInt(param, *items); param++; } if(comments != NULL) { stmt->setInt(param, *comments); param++; } stmt->setInt(param, id); stmt->executeUpdate(); delete stmt; }