void DataBase::insertTweetsToDatabase(QList<tweets> *dataToInsert) { connect(); QSqlQuery insertTweetsToDatabaseQuery; QString queryString = ""; insertTweetsToDatabaseQuery.prepare("INSERT INTO tweets (tweetID, tweetTime, username, text, userID) "\ " VALUES(:tweetID,:tweetTime,:username,:text,:twitterUserID)" ); QVariantList tweetID,tweetTime,username,text,twitterUserID; for (int i=0; i< dataToInsert->count();i++) { tweetID.append(dataToInsert->at(i).tweetID); tweetTime.append(dataToInsert->at(i).tweetTime); username.append(dataToInsert->at(i).username); text.append(dataToInsert->at(i).text); twitterUserID.append(dataToInsert->at(i).twitterUserID); } insertTweetsToDatabaseQuery.addBindValue(tweetID); insertTweetsToDatabaseQuery.addBindValue(tweetTime); insertTweetsToDatabaseQuery.addBindValue(username); insertTweetsToDatabaseQuery.addBindValue(text); insertTweetsToDatabaseQuery.addBindValue(twitterUserID); if(!insertTweetsToDatabaseQuery.execBatch()) qDebug() << insertTweetsToDatabaseQuery.lastError(); twitterDB.commit(); disconnect(); }
// 操作model_img表 bool modelImgInsert(int empId, AutoType& photo) { Log log(__LOGARG__,1); QSqlQuery query; query.exec("INSERT INTO model_img (emp_id, emp_photo) VALUES(?,?)"); QVariantList emp_id; emp_id << empId; query.addBindValue(emp_id); QVariantList emp_photo; QByteArray data(photo.getStr(),photo.getLen()); emp_photo << data; query.addBindValue(emp_photo); try { if (!query.execBatch()) { log << ((query.lastQuery()).toUtf8()).constData() << Log::endl; log << (((query.lastError()).text()).toUtf8()).constData() << Log::endl; return false; } } catch(...) { log << "Add New Node error! Unable to add a new Node!" << Log::endl; } return true; }
// 操作worktime表 // 插入数据 bool worktimeInsert(int empId, const char* time, const char* photo) { Log log(__LOGARG__,1); QSqlQuery query; query.exec("INSERT INTO work_time (emp_id, check_time, check_photo) VALUES(?,?,?)"); QVariantList emp_id; emp_id << empId; query.addBindValue(emp_id); QVariantList check_time; check_time << time; query.addBindValue(check_time); QVariantList check_photo; check_photo << photo; query.addBindValue(check_photo); try { if (!query.execBatch()) { log << ((query.lastQuery()).toUtf8()).constData() << Log::endl; log << (((query.lastError()).text()).toUtf8()).constData() << Log::endl; return false; } } catch(...) { log << "Add New Node error! Unable to add a new Node!" << Log::endl; } return true; }
// 修改empId bool worktimeAltEmpId(int worktimeId, int empId) { Log log(__LOGARG__,1); QSqlQuery query; query.exec("UPDATE work_time SET emp_id=? WHERE work_time_id=?"); QVariantList emp_id; emp_id << empId; query.addBindValue(emp_id); QVariantList work_time_id; work_time_id << worktimeId; query.addBindValue(work_time_id); try { if (!query.execBatch()) { log << ((query.lastQuery()).toUtf8()).constData() << Log::endl; log << (((query.lastError()).text()).toUtf8()).constData() << Log::endl; return false; } } catch(...) { log << "Add New Node error! Unable to add a new Node!" << Log::endl; } return true; }
void Widget::selects() { QSqlQuery query; query.prepare("INSERT INTO Student (name ,age ) VALUES (?,?)");//query.exec();准备输出 QVariantList names;//链表 names <<"Tom "<< "Jacks" << "Jane" << "Jerry"; query.addBindValue(names);//添加绑定链表 QVariantList ages; ages << 20 << 23 << 22 << 25; query.addBindValue(ages);//按顺序添加绑定age if(!query.execBatch()){//批处理函数值 QMessageBox::critical(0,QObject::tr("Databases Error"),query.lastError().text()); } query.finish(); QString janes = "jane"; QString jane =QString("SELECT sex FROM Student WHERE name = '%1'").arg(janes); query.exec(jane);//执行完这句话, QString name,sex; while (query.next()) { //name = query.value(0).toString(); sex = query.value(0).toString(); // cout<<name.toStdString() <<":"<<age.toStdString()<<endl; cout<<sex.toStdString()<<endl; } // cout<<name.toStdString() <<":"<<age.toStdString()<<endl; }
int DBimpl::addStudentsToProject(const int& projectID, QList<Student*>* students){ if (projectID > 0) { int tempInt; QSqlQuery query; QVariantList projectIDList; QVariantList studentIDList; query.prepare(DatabaseQueries::addStudentToProject); for (int i = 0; i< students->count();i++) { tempInt = students->at(i)->getID(); if (tempInt > 0) { projectIDList << projectID; studentIDList << tempInt ; } else return 0; } query.bindValue(":projectID",projectIDList); query.bindValue(":studentID",studentIDList); if(!query.execBatch()) { qDebug() << query.lastError(); qDebug() << query.lastQuery(); return 0; } else return query.lastInsertId().toInt(); } else return 0; }
bool CoreData::createDBFile(QString dbname) { if (QFile::exists(dbname)) { QFile::remove(dbname); } db.setDatabaseName(dbname); if (!db.open()) { QMessageBox::critical(0, QString::fromUtf8("出错了!"), QString::fromUtf8("无法打开数据库文件")); return false; } QSqlQuery query; query.exec("drop table people "); query.exec("create table people (id int primary key, name varchar(64), gender char(8), join_date date, status tinyint, comment text)"); qDebug() << db.lastError().type() << " + " << db.lastError().text(); query.exec("drop table date"); query.exec("create table date (id int primary key, date date)"); query.exec("pragma synchronous=0"); qDebug() << db.lastError().type() << " + " << db.lastError().text(); QDate date = QDate::currentDate(); date = date.addYears(-1); query.prepare("insert into date (id, date) values (?,?)"); int i = 0; QVariantList ids; QStringList dateStrings; for (i=0; i<3000; ++i) { ids<<i; date = date.addDays(1); dateStrings<<date.toString("yyyy-MM-dd"); } query.addBindValue(ids); query.addBindValue(dateStrings); if (!query.execBatch()) qDebug() << query.lastError(); query.exec("pragma synchronous=2"); qDebug()<<query.lastError(); query.exec("create table person_date (id int primary key, person_id integer, date_id integer, foreign key(person_id) references people(id), foreign key(date_id) references date(id))"); qDebug() << query.lastError(); personModel = new QSqlTableModel(0, db); setPersonModel(); signInModel = new SignInModel(0); setSignInModel(QDate::currentDate(), db); query.exec("create table contact (id int primary key, person_id integer, method varchar(64), value varchar(128), foreign key(person_id) references people(id))"); qDebug() << query.lastError(); return true; }
int DBimpl::storeTeamsByProject (const QList<Team*>& teams, const int& projectID){ if (projectID > 0) { int tempInt; QSqlQuery query; QVariantList studentIDList; QVariantList teamIDList; query.prepare(DatabaseQueries::storeTeamsByProject); for (int i = 0; i< teams.count();i++) { tempInt = createTeam(projectID,teams.at(i)->getResultDisplay()); if (tempInt > 0) { for (int j = 0; j < teams.at(i)->getTeamMembers().count();j++) { studentIDList << teams.at(i)->getTeamMembers().at(j); teamIDList << tempInt ; } } else return 0; } query.bindValue(":studentID",studentIDList); query.bindValue(":teamID",teamIDList); if(!query.execBatch()) { qDebug() << query.lastError(); qDebug() << query.lastQuery(); return 0; } else return query.lastInsertId().toInt(); } else return 0; }
void MainWindow::on_pushButton_clicked() { QSqlQuery query; QString attend = "yes"; QString person = QString::fromStdString(to_string(m_selectedPerson)); QString pose[] = { person, ui->lineEdit->text(), ui->lineEdit_2->text(), attend }; //从表单中获取数据,执行插入数据语句 query.prepare("INSERT INTO students VALUES(?,?,?,?)"); QVariantList Person; //1 Person << pose[0]; query.addBindValue(Person); QVariantList student_name; //2 student_name << pose[1]; query.addBindValue(student_name); QVariantList student_id; //3 student_id << pose[2]; query.addBindValue(student_id); QVariantList attend_class; //4 attend_class << pose[3]; query.addBindValue(attend_class); if(!query.execBatch()){ QMessageBox::critical(0,QObject::tr("Error"),query.lastError().text()); } query.finish();//插入错误时报错 QMessageBox::about(NULL, "提示", "插入成功"); m_selectedPerson++; m_numPersons++; ui->lineEdit->setEnabled(false); ui->lineEdit_2->setEnabled(false); ui->pushButton->hide(); ui->label_6->setText("请点击菜单"); }
bool ClientDB::addUsers(const QVector<User> *users) { QSqlQuery q; q.prepare("insert into fx_user(id, name, true_name, department, gmt_create, gmt_modify, status) " "values (?, ?, ?, ?, date(), date(), 1)"); QVariantList ids, names, true_names, departments; QVector<User>::const_iterator i = users->begin(); for (QVector<User>::const_iterator i = users->begin(); i != users->end(); i++) { ids << i->id(); names << i->name(); true_names << i->trueName(); departments << i->department(); } q.addBindValue(ids); q.addBindValue(names); q.addBindValue(true_names); q.addBindValue(departments); bool r; if (!(r = q.execBatch())) qDebug() << "ClientDB addUsers FAIL!" << q.lastError(); return r; }
void Client::AddClient() { this->setWindowFlags(Qt::WindowStaysOnTopHint); QSqlQuery * query = new QSqlQuery(sdb); if (!sdb.open()) { qDebug()<<"Something wrong with db openning!"; } else { query->prepare("INSERT INTO client (surname, name, middlename) VALUES (:surname,:name,:middlename);"); query->bindValue(":surname",ui->surname->text()); query->bindValue(":name", ui->name->text()); query->bindValue(":middlename", ui->middlename->text()); if (query->exec()) this->deleteLater(); else { ui->middlename->clear(); ui->surname->clear(); ui->name->clear(); } } if (!query->execBatch()) qDebug() << query->lastError(); this->deleteLater(); }
void postinput::on_pbOK_clicked() { databaseconnection::setdatabase(); databaseconnection::Open(); //获取输入数据 QString number =ui->leID->text().trimmed(); QString tel =ui->leTel->text().trimmed(); PostNumber = number; if(number.isEmpty()) { QMessageBox::information(this,tr("提示"),tr("请输入快递单号!"),QMessageBox::Ok); } if(tel.isEmpty()) { QMessageBox::information(this,tr("提示"),tr("请输入手机号码!"),QMessageBox::Ok); } //向post、receive、box表插入数据 if(!number.isEmpty()&&!tel.isEmpty()) { //插入数据到box表,首先需要选择快递箱类型 //选取大号快递箱 if(ui->cbBig->isChecked()) { QSqlQuery query; //判断所选箱子类的数量是否为0 QString BStatus = "available"; QString BType = "Big"; query.exec("select count(*) from box where BoxStatus = '"+BStatus+"' and Type = '"+BType+"'"); int Boxnumber = 0; QString BoxID = ""; if(query.next()) { Boxnumber = query.value(0).toInt(); } //选取投放快递箱子的编号 if(Boxnumber!=0) { //插入数据到post表 query.prepare("INSERT INTO post (Number, Tel, UserID) VALUES (?, ?, ?)"); QVariantList Number; Number << number; query.addBindValue(Number); QVariantList Tel; Tel << tel; query.addBindValue(Tel); QVariantList UserID; UserID <<Username; query.addBindValue(UserID); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("插入数据到post表失败!"),query.lastError().text()); } if(!query.isActive()) { QMessageBox::warning(this,tr("错误"),tr("插入数据到post表失败!"),QMessageBox::Ok); } //插入数据到receive表 RPwd = createpassword::createpwd(); QString Rstatus2 = "arrived"; query.prepare("INSERT INTO receive (Number, Password, Tel, ReceiveStatus) VALUES (?, ?, ?, ?)"); QVariantList RNumber; RNumber << number; query.addBindValue(RNumber); QVariantList Password; Password << RPwd; query.addBindValue(Password); QVariantList RTel; RTel << tel; query.addBindValue(RTel); QVariantList RStatus; RStatus << Rstatus2; query.addBindValue(RStatus); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("插入数据到receive表失败!"),query.lastError().text()); } if(!query.isActive()) { QMessageBox::warning(this,tr("错误"),tr("插入数据到receive表失败!"),QMessageBox::Ok); } //插入box表 query.exec("select BoxID from box where BoxStatus = '"+BStatus+"' and Type = '"+BType+"'"); if(query.next()) { BoxID = query.value(0).toString(); } QString status = "using"; query.prepare("UPDATE box SET BoxStatus = ?,PostID = ? WHERE BoxID = ? "); QVariantList BStatus; BStatus << status; query.addBindValue(BStatus); QVariantList BPostID; BPostID << number; query.addBindValue(BPostID); QVariantList BID; BID << BoxID; query.addBindValue(BID); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("更新数据到box表失败!"),query.lastError().text()); } //更新post表中的Postbox query.prepare("UPDATE post SET PostBox = ? WHERE Number = ? "); QVariantList PBox; PBox << BoxID; query.addBindValue(PBox); QVariantList PNumber; PNumber << number; query.addBindValue(PNumber); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("更新box数据到post表失败!"),query.lastError().text()); } } else { QMessageBox::warning(this,tr("错误"),tr("大号快递箱已经用完,请重新选择!"),QMessageBox::Ok); } } //选中中号快递箱 else if(ui->cbMiddle->isChecked()) { QSqlQuery query; QString BStatus = "available"; QString BType = "Middle"; query.exec("select count(*) from box where BoxStatus = '"+BStatus+"' and Type = '"+BType+"'"); int Boxnumber = 0; QString BoxID = ""; if(query.next()) { Boxnumber = query.value(0).toInt(); } //选取投放快递箱子的编号 if(Boxnumber!=0) { //插入数据到post表 query.prepare("INSERT INTO post (Number, Tel, UserID) VALUES (?, ?, ?)"); QVariantList Number; Number << number; query.addBindValue(Number); QVariantList Tel; Tel << tel; query.addBindValue(Tel); QVariantList UserID; UserID <<Username; query.addBindValue(UserID); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("插入数据到post表失败!"),query.lastError().text()); } if(!query.isActive()) { QMessageBox::warning(this,tr("错误"),tr("插入数据到post表失败!"),QMessageBox::Ok); } //插入数据到receive表 RPwd = createpassword::createpwd(); QString Rstatus2 = "arrived"; query.prepare("INSERT INTO receive (Number, Password, Tel, ReceiveStatus) VALUES (?, ?, ?, ?)"); QVariantList RNumber; RNumber << number; query.addBindValue(RNumber); QVariantList Password; Password << RPwd; query.addBindValue(Password); QVariantList RTel; RTel << tel; query.addBindValue(RTel); QVariantList RStatus; RStatus << Rstatus2; query.addBindValue(RStatus); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("插入数据到receive表失败!"),query.lastError().text()); } if(!query.isActive()) { QMessageBox::warning(this,tr("错误"),tr("插入数据到receive表失败!"),QMessageBox::Ok); } //插入数据到box表 query.exec("select BoxID from box where BoxStatus = '"+BStatus+"' and Type = '"+BType+"'"); if(query.next()) { BoxID = query.value(0).toString(); } QString status = "using"; query.prepare("UPDATE box SET BoxStatus = ?,PostID = ? WHERE BoxID = ? "); QVariantList BStatus; BStatus << status; query.addBindValue(BStatus); QVariantList BPostID; BPostID << number; query.addBindValue(BPostID); QVariantList BID; BID << BoxID; query.addBindValue(BID); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("更新数据到box表失败!"),query.lastError().text()); } //更新post表中的Postbox query.prepare("UPDATE post SET PostBox = ? WHERE Number = ? "); QVariantList PBox; PBox << BoxID; query.addBindValue(PBox); QVariantList PNumber; PNumber << number; query.addBindValue(PNumber); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("更新box数据到post表失败!"),query.lastError().text()); } } else { QMessageBox::warning(this,tr("错误"),tr("中号快递箱已经用完,请重新选择!"),QMessageBox::Ok); } } //选中小号快递箱 else if(ui->cbSmall->isChecked()) { QSqlQuery query; QString BStatus = "available"; QString BType = "Small"; query.exec("select count(*) from box where BoxStatus = '"+BStatus+"' and Type = '"+BType+"'"); int Boxnumber = 0; QString BoxID = ""; if(query.next()) { Boxnumber = query.value(0).toInt(); } //选取投放快递箱子的编号 if(Boxnumber!=0) { //插入数据到post表 query.prepare("INSERT INTO post (Number, Tel, UserID) VALUES (?, ?, ?)"); QVariantList Number; Number << number; query.addBindValue(Number); QVariantList Tel; Tel << tel; query.addBindValue(Tel); QVariantList UserID; UserID <<Username; query.addBindValue(UserID); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("插入数据到post表失败!"),query.lastError().text()); } if(!query.isActive()) { QMessageBox::warning(this,tr("错误"),tr("插入数据到post表失败!"),QMessageBox::Ok); } //插入数据到receive表 RPwd = createpassword::createpwd(); QString Rstatus2 = "arrived"; query.prepare("INSERT INTO receive (Number, Password, Tel, ReceiveStatus) VALUES (?, ?, ?, ?)"); QVariantList RNumber; RNumber << number; query.addBindValue(RNumber); QVariantList Password; Password << RPwd; query.addBindValue(Password); QVariantList RTel; RTel << tel; query.addBindValue(RTel); QVariantList RStatus; RStatus << Rstatus2; query.addBindValue(RStatus); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("插入数据到receive表失败!"),query.lastError().text()); } if(!query.isActive()) { QMessageBox::warning(this,tr("错误"),tr("插入数据到receive表失败!"),QMessageBox::Ok); } //插入数据到box表 query.exec("select BoxID from box where BoxStatus = '"+BStatus+"' and Type = '"+BType+"'"); if(query.next()) { BoxID = query.value(0).toString(); } QString status = "using"; query.prepare("UPDATE box SET BoxStatus = ?,PostID = ? WHERE BoxID = ? "); QVariantList BStatus; BStatus << status; query.addBindValue(BStatus); QVariantList BPostID; BPostID << number; query.addBindValue(BPostID); QVariantList BID; BID << BoxID; query.addBindValue(BID); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("更新数据到box表失败!"),query.lastError().text()); } //更新post表中的Postbox query.prepare("UPDATE post SET PostBox = ? WHERE Number = ? "); QVariantList PBox; PBox << BoxID; query.addBindValue(PBox); QVariantList PNumber; PNumber << number; query.addBindValue(PNumber); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("更新box数据到post表失败!"),query.lastError().text()); } } else { QMessageBox::warning(this,tr("错误"),tr("小号快递箱已经用完,请重新选择!"),QMessageBox::Ok); } } else { QMessageBox::information(this,tr("提示"),tr("无法发送短信,原因:没有选择快递箱类型!"),QMessageBox::Ok); } QMessageBox::information(this,tr("提示"),tr("数据写入成功!"),QMessageBox::Ok); } else { QMessageBox::information(this,tr("提示"),tr("无法发送短信,原因:输入信息为空或不完整!"),QMessageBox::Ok); } //发送短信 sms send; if(send.setport()) { QString smsBoxID; QSqlQuery query; query.exec("select BoxID from box where PostID = '"+number+"'"); if(query.next()) { smsBoxID = query.value(0).toString(); } //转换收件人手机号码 QString sms1 = send.convertPhone(tel); //生成短信内容 QString sms2 = send.stringToUnicode(RPwd,smsBoxID); //发送短信字符串 QString message = sms1+sms2; //发送短信长度 QString smslen = send.smslength(sms2,tel); if(send.sendsms(message,smslen)) { QMessageBox::information(this,tr("提示"),tr("投递快递成功!短信已经发送!"),QMessageBox::Ok); send.closeport(); //PostStatus+PostTime QString status = "Posted"; QDateTime time=QDateTime::currentDateTime(); query.prepare("UPDATE post SET PostStatus = ?,PostTime = ? WHERE Number = ? "); QVariantList PStatus; PStatus << status; query.addBindValue(PStatus); QVariantList PTime; PTime << time; query.addBindValue(PTime); QVariantList PNumber; PNumber << number; query.addBindValue(PNumber); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("Database Error"), query.lastError().text()); } } else { QMessageBox::information(this,tr("提示"),tr("短信发送失败!"),QMessageBox::Ok); QString status = "SMS Failed"; query.prepare("UPDATE post SET PostStatus = ? WHERE Number = ? "); QVariantList PStatus; PStatus << status; query.addBindValue(PStatus); QVariantList PNumber; PNumber << number; query.addBindValue(PNumber); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("Database Error"), query.lastError().text()); } } } else { QMessageBox::information(this,tr("提示"),tr("短信发送设备无法打开!"),QMessageBox::Ok); QString status = "SMS Failed"; QSqlQuery query; query.prepare("UPDATE post SET PostStatus = ? WHERE Number = ? "); QVariantList PStatus; PStatus << status; query.addBindValue(PStatus); QVariantList PNumber; PNumber << number; query.addBindValue(PNumber); if (!query.execBatch()) { QMessageBox::critical(0, QObject::tr("Database Error"), query.lastError().text()); } } //清除输入信息 ui->leID->clear(); ui->leTel->clear(); ui->leID->setFocus(); databaseconnection::Close(); }
bool DatabaseManager::insertInvariants(int type, int number, std::vector< std::vector<float> > invariants) { //bool ret = false; if (db.isOpen()) { QSqlQuery query; // First check, if a bubble has already entered to the table /* bool exists = query.exec(QString("select * from invariant where type = %1 and number = %2").arg(type).arg(number)); // If query is successfully executed if(exists) { // if there are elements received from the table, then there exists a bubble, we should delete those entries if(query.next()) { ret = query.exec(QString("delete from invariant where type = %1 and number = %2").arg(type).arg(number)); // If deletion is not successfuly executed return false if(!ret) return false; } */ //else return false; // Speed up the multiple-row insertion by using transactions //query.exec(QString("BEGIN TRANSACTION")); query.prepare(QString("replace into invariant values(?, ?, ?, ?)")); QVariantList typee; QVariantList numberr; QVariantList placeLabel; QVariantList val; db.transaction(); // Insert new bubble for(uint i = 0; i <invariants.size(); i++) { for(uint j = 0; j < invariants[i].size();j++){ val<< invariants[i][j]; typee<<type; numberr<<number; if(placeLabels.size() >= number) { placeLabel<<placeLabels.at(number-1); } else placeLabel<<-1; //query.exec(QString("replace into invariant values('%1', '%2', '%3')").arg(type).arg(number).arg(val)); } } query.addBindValue(typee); query.addBindValue(numberr); query.addBindValue(placeLabel); query.addBindValue(val); // query.exec(QString("COMMIT TRANSACTION")); if (!query.execBatch()) qDebug() << query.lastError(); db.commit(); return true; } // } return false; }
bool DatabaseManager::insertBubble(int type, int number, std::vector<bubblePoint> bubble) { // bool ret = false; if (db.isOpen()) { QSqlQuery query; // query.exec("PRAGMA journal_mode = MEMORY"); // query.exec("PRAGMA synchronous = OFF"); query.prepare(QString("replace into bubble values( ?, ?, ?, ?, ?, ?)")); // query.prepare(QString("insert into bubble values( :type, :number, :pan, :tilt, :val) ON DUPLICATE KEY update val = :val)")); // updateQuery.prepare("update bubble set val = :val where type = %type and number = %2 and pan = %3 and tilt = %4"); QVariantList typee; QVariantList numberr; QVariantList placeLabel; QVariantList pan; QVariantList tilt; QVariantList val; db.transaction(); // Insert new bubble for(uint i = 0; i < bubble.size(); i++) { bubblePoint pt; pt = bubble.at(i); typee<<type; numberr<<number; pan<<pt.panAng; tilt<<pt.tiltAng; val<<pt.val; if(placeLabels.size() >= number) { placeLabel<<placeLabels.at(number-1); } else placeLabel<<-1; /* query.bindValue(":type",type); query.bindValue(":number",number); query.bindValue(":pan",pt.panAng); query.bindValue(":tilt",pt.tiltAng); query.bindValue(":val",pt.val);*/ // query.exec(); //query.exec(QString("insert into bubble values('%1', '%2', '%3', '%4', %5)").arg(type).arg(number).arg(pt.panAng).arg(pt.tiltAng).arg(pt.val)); } query.addBindValue(typee); query.addBindValue(numberr); query.addBindValue(placeLabel); query.addBindValue(pan); query.addBindValue(tilt); query.addBindValue(val); if (!query.execBatch()) qDebug() << query.lastError(); db.commit(); return true; } return false; }
// 操作book_info表 int bookInfoInsert(const char* name, const char* depName, AutoType& photo) { Log log(__LOGARG__,1); QSqlQuery query; query.exec("INSERT INTO book_info (emp_name, dep_name, hire_time, photo) VALUES(?,?,now(),?)"); QVariantList emp_name; emp_name << name; query.addBindValue(emp_name); QVariantList dep_name; dep_name << depName; query.addBindValue(dep_name); QByteArray data(photo.getStr(),photo.getLen()); QVariantList emp_photo; emp_photo << data; query.addBindValue(emp_photo); log << "len1=" << data.size() << Log::endl; try { if (!query.execBatch()) { log << ((query.lastQuery()).toUtf8()).constData() << Log::endl; log << (((query.lastError()).text()).toUtf8()).constData() << Log::endl; return -1; } } catch(...) { log << "Add New Node error! Unable to add a new Node!" << Log::endl; } //query.prepare("select emp_id from book_info where emp_id = (select max(emp_id) from book_info)"); //query.exec(); QVariant qvid = query.lastInsertId(); int empId = qvid.toInt(); query.prepare("select * from book_info"); query.exec(); QSqlRecord rec = query.record(); int photoCol = rec.indexOf("photo"); int empIdCol = rec.indexOf("emp_id"); log << "empId=" << empId << Log::endl; int i=0; while (query.next()) { int id = (query.value(empIdCol)).toInt(); log << "id=" << id << Log::endl; if (id==empId) { QByteArray data2 = (query.value(photoCol)).toByteArray(); log << "len2=" << data2.size() << Log::endl; for (int j=0; j<data2.size(); j++) { char a=data[j]; char b=data2[j]; if (a!=b) { //log << "不相等" << j << ":" << a <<" , " << b << Log::endl; } } cv::Mat mat = QByteArray2Mat(data2,data2.size()); std::string s; num2str(i, s); imwrite("tmp"+s+".jpg",mat); mat = AutoType2Mat(photo); imwrite("tmp2.jpg",mat); i++; } } return empId; }
/* * Зберігаємо шаблон кросворду після редагування решітки */ void tableTemplateWidget::saveToDB() { int previewSizeCell = 20, val; int W = numCol*previewSizeCell; int H = numRow*previewSizeCell; QPainter *pngPainter = new QPainter(); QImage *image = new QImage(QSize(W, H), QImage::Format_ARGB32_Premultiplied); float t, l; QRectF src(0, 0, W, H); QRectF r; QTableWidgetItem *cell; QSqlQuery query; query.prepare("DELETE FROM crossword.grids WHERE _template = ?;"); query.addBindValue(QVariant(templateId)); query.exec(); QSqlError le = query.lastError(); if (le.type() != QSqlError::NoError) qDebug() << "saveToDB: " << le.text(); query.prepare("INSERT INTO crossword.grids (_template, _row, _column, _value) " "VALUES (?, ?, ?, ?)"); pb = new QProgressBar(this); pb->setRange(0, numRow*numCol); sb->addWidget(pb); QVariantList tmp, row, col, value; QString text; pngPainter->begin(image); int i, j, nw; for (i = 0; i < numRow; i++) { t = src.top() + i * previewSizeCell; for (j = 0; j < numCol; j++) { l = src.left() + j * previewSizeCell; r.setTop(t); r.setLeft(l); r.setRight(src.left() + l + previewSizeCell); r.setBottom(src.top() + t + previewSizeCell); cell = this->item(i, j); val = cell->data(Qt::UserRole).toInt(); if (val) pngPainter->fillRect(r, fullCell); else pngPainter->fillRect(r, emptyCell); // прямокутник для ячейки pngPainter->drawRect(r); // виводимо номер слова nw = findWordByRC(i, j) + 1; if ( nw >= 1 ) { text = QVariant(nw).toString(); pngPainter->drawText(r, Qt::AlignLeft | Qt::AlignTop, text); } tmp << templateId; row << i; col << j; value << val; } pb->setValue(i*numRow + j*numCol); } pngPainter->end(); query.addBindValue(tmp); query.addBindValue(row); query.addBindValue(col); query.addBindValue(value); QSqlDriver *drv = db->driver(); drv->beginTransaction(); query.execBatch(QSqlQuery::ValuesAsRows); drv->commitTransaction(); le = query.lastError(); if (le.type() != QSqlError::NoError) qDebug() << "saveToDB: " << le.text(); QByteArray ba; QBuffer blob(&ba); blob.open(QIODevice::ReadWrite | QIODevice::Unbuffered); image->save(&blob, "PNG"); blob.close(); /* * ====== Run before update DB ====== */ scanTemplate(); savePrivateData(); query.prepare("UPDATE crossword.templates SET _rows = ?, _columns = ?, " "_preview = ?, _count_words = ? WHERE _id = ?;"); query.addBindValue(QVariant(numRow)); query.addBindValue(QVariant(numCol)); query.addBindValue(QVariant(blob.data())); query.addBindValue(QVariant(wi.count())); query.addBindValue(QVariant(templateId)); query.exec(); le = query.lastError(); if (le.type() != QSqlError::NoError) qDebug() << "saveToDB: " << le.text(); delete image; delete pngPainter; sb->removeWidget(pb); sb->showMessage(tr("Template saved"), 2000); isDirty = false; // need for templateListWidget emit savedToDB(templateId); }
void Servatrice_DatabaseInterface::storeGameInformation(const QString &roomName, const QStringList &roomGameTypes, const ServerInfo_Game &gameInfo, const QSet<QString> &allPlayersEver, const QSet<QString> &allSpectatorsEver, const QList<GameReplay *> &replayList) { if (!checkSql()) return; if (!settingsCache->value("game/store_replays", 1).toBool() ) return; QVariantList gameIds1, playerNames, gameIds2, userIds, replayNames; QSetIterator<QString> playerIterator(allPlayersEver); while (playerIterator.hasNext()) { gameIds1.append(gameInfo.game_id()); const QString &playerName = playerIterator.next(); playerNames.append(playerName); } QSet<QString> allUsersInGame = allPlayersEver + allSpectatorsEver; QSetIterator<QString> allUsersIterator(allUsersInGame); while (allUsersIterator.hasNext()) { int id = getUserIdInDB(allUsersIterator.next()); if (id == -1) continue; gameIds2.append(gameInfo.game_id()); userIds.append(id); replayNames.append(QString::fromStdString(gameInfo.description())); } QVariantList replayIds, replayGameIds, replayDurations, replayBlobs; for (int i = 0; i < replayList.size(); ++i) { QByteArray blob; const unsigned int size = replayList[i]->ByteSize(); blob.resize(size); replayList[i]->SerializeToArray(blob.data(), size); replayIds.append(QVariant((qulonglong) replayList[i]->replay_id())); replayGameIds.append(gameInfo.game_id()); replayDurations.append(replayList[i]->duration_seconds()); replayBlobs.append(blob); } { QSqlQuery *query = prepareQuery("update {prefix}_games set room_name=:room_name, descr=:descr, creator_name=:creator_name, password=:password, game_types=:game_types, player_count=:player_count, time_finished=now() where id=:id_game"); query->bindValue(":room_name", roomName); query->bindValue(":id_game", gameInfo.game_id()); query->bindValue(":descr", QString::fromStdString(gameInfo.description())); query->bindValue(":creator_name", QString::fromStdString(gameInfo.creator_info().name())); query->bindValue(":password", gameInfo.with_password() ? 1 : 0); query->bindValue(":game_types", roomGameTypes.isEmpty() ? QString("") : roomGameTypes.join(", ")); query->bindValue(":player_count", gameInfo.max_players()); if (!execSqlQuery(query)) return; } { QSqlQuery *query = prepareQuery("insert into {prefix}_games_players (id_game, player_name) values (:id_game, :player_name)"); query->bindValue(":id_game", gameIds1); query->bindValue(":player_name", playerNames); query->execBatch(); } { QSqlQuery *query = prepareQuery("update {prefix}_replays set id_game=:id_game, duration=:duration, replay=:replay where id=:id_replay"); query->bindValue(":id_replay", replayIds); query->bindValue(":id_game", replayGameIds); query->bindValue(":duration", replayDurations); query->bindValue(":replay", replayBlobs); query->execBatch(); } { QSqlQuery *query = prepareQuery("insert into {prefix}_replays_access (id_game, id_player, replay_name) values (:id_game, :id_player, :replay_name)"); query->bindValue(":id_game", gameIds2); query->bindValue(":id_player", userIds); query->bindValue(":replay_name", replayNames); query->execBatch(); } }
int CSqliteDbOper::_AddDataArray(LstHistoryDataT* pLstData) { int nFunRes = 0; bool bExecRes = false; std::string strDBTableName; std::string strSQL; LstHistoryDataIterT iterLst; CHistoryData* pDataTmp = NULL; //QVariantList lstInstrumentID; QVariantList lst_COLUMN_DATE; QVariantList lst_COLUMN_OPEN; QVariantList lst_COLUMN_HIGH; QVariantList lst_COLUMN_LOW; QVariantList lst_COLUMN_CLOSE; QVariantList lst_COLUMN_VOLUME; QVariantList lst_COLUMN_ADJCLOSE; QSqlQuery* pQSqlQueryForInseert = NULL; if (pLstData->empty()) { nFunRes = 0; return nFunRes; } pQSqlQueryForInseert = new QSqlQuery(*m_pQSqlDataBase); strDBTableName = str_Table_bar_data_1day; strSQL = _BuildSQLForInsert(); MYLOG4CPP_DEBUG<<" "<<m_strSqliteDbFileFullPath.toStdString() <<" "<<"exec strSQL="<<strSQL <<" "<<"LstHistoryDataT.size="<<pLstData->size(); pQSqlQueryForInseert->prepare(strSQL.c_str()); iterLst = pLstData->begin(); while (iterLst != pLstData->end()) { pDataTmp = (*iterLst); //lstInstrumentID<<m_pDBOperParam->m_nInstrumentID; lst_COLUMN_DATE << pDataTmp->m_strDate; lst_COLUMN_OPEN << pDataTmp->m_strOpen; lst_COLUMN_HIGH << pDataTmp->m_strHigh; lst_COLUMN_LOW << pDataTmp->m_strLow; lst_COLUMN_CLOSE << pDataTmp->m_strClose; lst_COLUMN_VOLUME << pDataTmp->m_strVolume; lst_COLUMN_ADJCLOSE << pDataTmp->m_strAdjClose; iterLst++; }//while //pQSqlQueryForInseert->addBindValue(lstInstrumentID); pQSqlQueryForInseert->addBindValue(lst_COLUMN_DATE); pQSqlQueryForInseert->addBindValue(lst_COLUMN_OPEN); pQSqlQueryForInseert->addBindValue(lst_COLUMN_HIGH); pQSqlQueryForInseert->addBindValue(lst_COLUMN_LOW); pQSqlQueryForInseert->addBindValue(lst_COLUMN_CLOSE); pQSqlQueryForInseert->addBindValue(lst_COLUMN_VOLUME); pQSqlQueryForInseert->addBindValue(lst_COLUMN_ADJCLOSE); bExecRes = pQSqlQueryForInseert->execBatch(); if (!bExecRes) { nFunRes = -1; MYLOG4CPP_DEBUG<<"execBatch strSQL="<<strSQL <<" "<<"LstHistoryDataT.size="<<pLstData->size() <<" "<<"error:"<<pQSqlQueryForInseert->lastError().text().toStdString(); } if (NULL != pQSqlQueryForInseert) { delete pQSqlQueryForInseert; pQSqlQueryForInseert = NULL; } return nFunRes; }
void Transaction::runBatch(QSqlQuery &query) { if(query.execBatch() == false) handleError(query); }
int DatabaseHelper::addPaper(const Paper& paper) { int bookTitleId = getBookTitleId(paper.getBookTitle()); if (bookTitleId == -1) { bookTitleId = addBookTitle(paper.getBookTitle()); } vector<int> authorsId(paper.getAuthors().size()); for (vector<int>::size_type i = 0; i < paper.getAuthors().size(); ++i) { const string& author = paper.getAuthors().at(i); int authorId = getAuthorId(author); if (authorId == -1) { authorId = addAuthor(author); } authorsId[i] = authorId; } vector<int> tagsId(paper.getTags().size()); for (vector<int>::size_type i = 0; i < paper.getTags().size(); ++i) { const string& tag = paper.getTags().at(i); int tagId = getTagId(tag); if (tagId == -1) { tagId = addTag(tag); } tagsId[i] = tagId; } QSqlQuery query; if (paper.getId() <= 0) { query.prepare("INSERT INTO pl_paper(year, book_title_id, title, path, comment) " "VALUES(:year, :book_title_id, :title, :path, :comment)"); query.bindValue(":year", paper.getYear()); query.bindValue(":book_title_id", bookTitleId); query.bindValue(":title", paper.getTitle().c_str()); query.bindValue(":path", paper.getPath().c_str()); query.bindValue(":comment", paper.getComment().c_str()); } else { query.prepare("INSERT INTO pl_paper(paper_id, year, book_title_id, title, path, comment) " "VALUES(:paper_id, :year, :book_title_id, :title, :path, :comment)"); query.bindValue(":paper_id", paper.getId()); query.bindValue(":year", paper.getYear()); query.bindValue(":book_title_id", bookTitleId); query.bindValue(":title", paper.getTitle().c_str()); query.bindValue(":path", paper.getPath().c_str()); query.bindValue(":comment", paper.getComment().c_str()); } query.exec(); int paperId = query.lastInsertId().toInt(); QVariantList paperIdList, authorIdList; for (int i = 0; i < static_cast<int>(authorsId.size()); ++i) { paperIdList.append(paperId); authorIdList.append(authorsId[i]); } query.prepare("INSERT INTO pl_paper2author(paper_id, author_id) " "VALUES(:paper_id, :author_id)"); query.bindValue(":paper_id", paperIdList); query.bindValue(":author_id", authorIdList); query.execBatch(); paperIdList.clear(); QVariantList tagIdList; for (int i = 0; i < static_cast<int>(tagsId.size()); ++i) { paperIdList.append(paperId); tagIdList.append(tagsId[i]); } query.prepare("INSERT INTO pl_paper2tag(paper_id, tag_id) " "VALUES(:paper_id, :tag_id)"); query.bindValue(":paper_id", paperIdList); query.bindValue(":tag_id", tagIdList); query.execBatch(); return paperId; }
int CSqliteDbOper::_AddDataArrayTick(LstInstrumentTickInfoT* pLstTick) { BOOST_LOG_FUNCTION(); int nFunRes = 0; bool bExecRes = false; std::string strDBTableName; std::string strSQL; std::string strTimeStr; LstInstrumentTickInfoIterT iterLst; CInstrumentTickInfo* pInstrumentTickInfo = NULL; //QVariantList lstInstrumentID; QVariantList lstTimestamp; QVariantList lstBidPx; QVariantList lstAskPx; QVariantList lstLastPx; QVariantList lstBidVol; QVariantList lstAskVol; QVariantList lstLastVol; QSqlQuery* pQSqlQueryForInseert = NULL; if (pLstTick->empty()) { nFunRes = 0; return nFunRes; } pQSqlQueryForInseert = new QSqlQuery(*m_pQSqlDataBaseTIK); strDBTableName = str_Table_tick_data; strSQL = _BuildSQLForInsertTickData(); LOG_DEBUG<<"m_pDBOperParam->m_nInstrumentID="<<m_pDBOperParam->m_nInstrumentID <<" "<<"strSQL="<<strSQL <<" "<<"pLstInstrumentTickInfo.size="<<pLstTick->size(); pQSqlQueryForInseert->prepare(strSQL.c_str()); iterLst = pLstTick->begin(); while (iterLst != pLstTick->end()) { pInstrumentTickInfo = (*iterLst); strTimeStr = m_pUtilityFun->dataTimeToStr(pInstrumentTickInfo->m_TickInfo.Time); //lstInstrumentID<<m_pDBOperParam->m_nInstrumentID; lstTimestamp << strTimeStr.c_str(); lstBidPx << pInstrumentTickInfo->m_TickInfo.BidPx; lstAskPx << pInstrumentTickInfo->m_TickInfo.AskPx; lstLastPx << pInstrumentTickInfo->m_TickInfo.LastPx; lstBidVol << pInstrumentTickInfo->m_TickInfo.BidVol; lstAskVol << pInstrumentTickInfo->m_TickInfo.AskVol; lstLastVol << pInstrumentTickInfo->m_TickInfo.LastVol; iterLst++; }//while //pQSqlQueryForInseert->addBindValue(lstInstrumentID); pQSqlQueryForInseert->addBindValue(lstTimestamp); pQSqlQueryForInseert->addBindValue(lstBidPx); pQSqlQueryForInseert->addBindValue(lstAskPx); pQSqlQueryForInseert->addBindValue(lstLastPx); pQSqlQueryForInseert->addBindValue(lstBidVol); pQSqlQueryForInseert->addBindValue(lstAskVol); pQSqlQueryForInseert->addBindValue(lstLastVol); bExecRes = pQSqlQueryForInseert->execBatch(); if (!bExecRes) { nFunRes = -1; LOG_ERROR<<"execBatch strSQL="<<strSQL<<" pLstInstrumentTickInfo.size="<<pLstTick->size() <<" "<<"error:"<<pQSqlQueryForInseert->lastError().text().toStdString(); } if (NULL != pQSqlQueryForInseert) { delete pQSqlQueryForInseert; pQSqlQueryForInseert = NULL; } return nFunRes; }
int CSqliteDbOper::_AddDataArrayBar(unsigned int nTableKey, LstInstrumentBarInfoT* pListBar) { BOOST_LOG_FUNCTION(); int nFunRes = 0; bool bExecRes = false; std::string strSQL; std::string strTimeStr; LstInstrumentBarInfoIterT iterLst; CInstrumentBarInfo* pInstrumentBarInfo = NULL; //QVariantList lstInstrumentID; QVariantList lstTimestamp; QVariantList lstOpen; QVariantList lstClose; QVariantList lstHigh; QVariantList lstLow; QVariantList lstVolume; QSqlQuery* pQSqlQueryForInseert = NULL; if (pListBar->empty()) { nFunRes = 0; return nFunRes; } pQSqlQueryForInseert = new QSqlQuery(*m_pQSqlDataBaseBAR); strSQL = _BuildSQLForInsertBarData(nTableKey); LOG_DEBUG<<"m_pDBOperParam->m_nInstrumentID="<<m_pDBOperParam->m_nInstrumentID <<" "<<"strSQL="<<strSQL <<" "<<"pLstInstrumentBarInfo.size="<<pListBar->size(); pQSqlQueryForInseert->prepare(strSQL.c_str()); iterLst = pListBar->begin(); while (iterLst != pListBar->end()) { pInstrumentBarInfo = (*iterLst); //lstInstrumentID<<m_pDBOperParam->m_nInstrumentID; strTimeStr = m_pUtilityFun->dataTimeToStr(pInstrumentBarInfo->m_BarInfo.Time); lstTimestamp << strTimeStr.c_str(); lstOpen<<pInstrumentBarInfo->m_BarInfo.Open; lstClose<<pInstrumentBarInfo->m_BarInfo.Close; lstHigh<<pInstrumentBarInfo->m_BarInfo.High; lstLow<<pInstrumentBarInfo->m_BarInfo.Low; lstVolume<<pInstrumentBarInfo->m_BarInfo.Volume; iterLst++; }//while //pQSqlQueryForInseert->addBindValue(lstInstrumentID); pQSqlQueryForInseert->addBindValue(lstTimestamp); pQSqlQueryForInseert->addBindValue(lstOpen); pQSqlQueryForInseert->addBindValue(lstClose); pQSqlQueryForInseert->addBindValue(lstHigh); pQSqlQueryForInseert->addBindValue(lstLow); pQSqlQueryForInseert->addBindValue(lstVolume); bExecRes = pQSqlQueryForInseert->execBatch(); if (!bExecRes) { nFunRes = -1; LOG_ERROR<<"execBatch strSQL="<<strSQL<<" pLstInstrumentBarInfo.size="<<pListBar->size() <<" "<<"error:"<<pQSqlQueryForInseert->lastError().text().toStdString(); } if (NULL != pQSqlQueryForInseert) { delete pQSqlQueryForInseert; pQSqlQueryForInseert = NULL; } return nFunRes; }