C++ QSqlQuery::clear方法代码示例

bool SQLQueryData::UpdateCS(const QString& tempfirst, const QString& tempmid, const QString& templast, const QString& tempgender, const int& bday, const int& dday, const int& id)
{

    SQLConnect database;
    database.ConnectToDB();
    QSqlQuery query = database.GetQuery();

    query.prepare("UPDATE scientists SET first_name = ?, middle_name = ?, last_name = ?, gender =?, birth_year =?, death_year=? WHERE id=?");
    query.bindValue(0,tempfirst);
    if(tempmid != "")
    {
        query.bindValue(1,tempmid);
    }
    else
    {
        query.bindValue(1,QString());
    }
    query.bindValue(2,templast);
    query.bindValue(3,tempgender);
    query.bindValue(4,bday);
    query.bindValue(5,dday);
    query.bindValue(6,id);

    if(query.exec())
    {
        database.Disconnect();
        query.clear();
        return true;
    }
    database.Disconnect();
    query.clear();
    return false;
}

void firstDiagnostics::on_buttonBox_accepted()
{

    QSqlDatabase::database().transaction();
    QSqlQuery query;
    query.prepare("insert into diagnosis(fk_customer, fk_doctor, type, fk_partner, dateD, timeD) values(?, ?, ?, ?, ?, ?)");
    query.addBindValue(customerID);
    query.addBindValue(ui->doctorCombobox->itemData(ui->doctorCombobox->currentIndex()));
    query.addBindValue(0);
    query.addBindValue(partnerID);
    query.addBindValue(ui->dateEdit->date());
    query.addBindValue(ui->timeEdit->time());
    query.exec();
    query.clear();
    query.prepare("select fk_customerCard from Customer where id = ?");
    query.addBindValue(customerID);
    query.exec();
    int cardID = 0;
    if(query.next())
        cardID = query.value(0).toInt();
    query.clear();
    query.prepare("insert into custCardDoctor values(?, ?)");
    query.addBindValue(cardID);
    query.addBindValue(ui->doctorCombobox->itemData(ui->doctorCombobox->currentIndex()));
    query.exec();
    if(!QSqlDatabase::database().commit())
    {
        QSqlError err;
        err = QSqlDatabase::database().lastError();
        QSqlDatabase::database().rollback();
        QMessageBox::warning(this, tr("Error"), err.text());

    }
    this->deleteLater();
}

int KraftDB::processSqlCommands( const SqlCommandList& commands )
{
    int cnt = 0;

    foreach( SqlCommand cmd, commands ) {
        if( !cmd.message().isEmpty() ) {
            emit statusMessage( cmd.message() );
        }

        if( !cmd.command().isEmpty() ) {
            bool res = true;
            QSqlQuery q;
            q.clear();
            res = q.exec(cmd.command()) || cmd.mayfail();

            if ( res ) {
                kDebug() << "Successful SQL Command: " << cmd.command() << endl;
                cnt ++;
            } else {
                QSqlError err = q.lastError();
                res = false;
                kDebug() << "###### Failed SQL Command " << cmd.command() << ": " << err.text() << endl;
            }
            q.clear();
            emit processedSqlCommand( res );

        }
    }
    return cnt;
}

void login::on_login_login_clicked()
{
    if(ui->userName->text().isEmpty() || ui->password->text().isEmpty()){
        QMessageBox::information(this,tr("提示"),tr("不能为空"),QMessageBox::Ok);
        ui->userName->clear();
        ui->password->clear();
        return;
    }
    QString uid = ui->userName->text();
    QString pwd = ui->password->text();
    QSqlQuery query;
    query.prepare("select * from ADMIN where A_USER=:uid and A_KEY = :pwd");
    query.bindValue(":uid",uid);
    query.bindValue(":pwd",pwd);
    query.exec();
    if(query.next()){
        MainWindow *m = new MainWindow();
        m->show();
        this->hide();
        query.clear();
    }
    else{
        QMessageBox::information(this,tr("提示"),tr("请重新输入"),QMessageBox::Ok);
        ui->userName->clear();
        ui->password->clear();
        query.clear();
        return;
    }
}

QSqlQuery MainData::makeQuery(QString q_str,bool& made,bool silent)
{
    QSqlQuery query;
    try
    {
        query.exec(q_str);
        if(query.isActive())
        {

             if(query.size()==0) throw "Неверный логин/пароль";
             if(!query.first()) throw "Ошибка при запросе данных из БД";
             made=true;
             return query;
        }
        else
            throw "Не могу выполнить запрос к БД";
    }
    catch(QString message)
    {

        if(!silent) MessageHandler::showMessage(MessageHandler::Error,message,0);
        made=false;
        query.clear();
    }
    catch(...)
    {
        if(!silent) MessageHandler::showMessage(MessageHandler::Error,"Ошибка при работе с БД\n"+q_str,0);
        made=false;
        query.clear();
    }


 }

void RequestHistory::groupHistory(const QString& name, const QVector<int> requestIds)
{
    QSqlQuery *q = new QSqlQuery(m_database);
    q->prepare("SELECT id FROM groups WHERE name = :name");
    q->bindValue(":name", name);
    query(q);
    int index = 0;
    if( q->first() ) {
        index = q->value(0).toInt();
    } else {
        index = lastIncrementId("groups");
        q->clear();
        q->prepare(""
         "INSERT INTO groups (id, name)"
         " VALUES (:id, :name)"
        );
        q->bindValue(":id", index);
        q->bindValue(":name", name);
        query(q);
    }

    QString ids;
    for (int i = 0; i < requestIds.size(); ++i) {
        ids += QString::number(requestIds.at(i)) + ", ";
    }
    ids = ids.remove(ids.length()-2, 2);

    q->clear();
    q->prepare(QString("UPDATE requests SET group_id = :group_id WHERE id IN (%1)").arg(ids));
    q->bindValue(":group_id", index);
    query(q);
    delete q;
}

void InsertTierDialog::on_buttonBox_accepted()
{
	QSqlQuery q;

	q.prepare("SELECT PK_Stall FROM STALL WHERE Stallart = ?");
	q.bindValue(0,ui->stall->currentText());
	q.exec();
	q.first();
	int stallpk = q.value(0).toInt();
	q.clear();

	q.prepare("SELECT PK_Futter FROM FUTTER WHERE Name = ?");
	q.bindValue(0,ui->futter->currentText());
	q.exec();
	q.first();
	int futterpk = q.value(0).toInt();
	q.clear();

	QString insertQuery =
			QString("SELECT usp_TierHinzufuegen(%1,'%2','%3','%4',%5,'%6',%7,%8)")
			.arg(
				QString::number(stallpk),
				ui->Name->text(),
				ui->geburtsDatum->date().toString("yyyy-MM-dd"),
				ui->anschaffungsDatum->date().toString("yyyy-MM-dd"),
				QString::number(ui->gewicht->value()),
				ui->tierart->currentText(),
				QString::number(futterpk),
				QString::number(ui->futterMenge->value())
			);
	if(!q.exec(insertQuery)) {
		ErrorDialog() << q.lastError();
	}
}

void MainWindow::updateTime()
{

  //  qDebug()<<"timer";
    ui->actualTime->clear();
    ui->schedulerTime->clear();

    QSqlQuery query;

    query.clear();

    query.prepare("SELECT time(NOW())");

    query.exec();

    while(query.next())
    {
        ui->actualTime->setText(query.value(0).toString());
    }

    query.clear();

    query.exec("CALL spGetSchedulerTime()");

    while(query.next())
    {
      ui->schedulerTime->setText(query.value(0).toString());
    }

}

 void TheSQDMonitoring::SQDMupdateSQL()
 {
     QSqlQuery query;
     query.clear();
     //query.prepare("select LAST_INSERT_ID() from serialporttest");
     query.prepare("select MAX(idserialtest) from serialporttest");
     query.exec();
     if(query.next())
     {
         QString IDVAL=query.value(0).toString();
         query.clear();
         query.prepare("select * from serialporttest where idserialtest=:idserialtest");
         query.bindValue(":idserialtest",IDVAL);
         query.exec();
         if(query.next())
         {
             ui->SQDM_EquipmentID_lable->setText(query.value(2).toString());
             ui->SQDM_EquipmentProductionNumber_LCDNumber->display(query.value(7).toString());
             ui->SQDM_EquipmentTemperture_QwtTherm->setValue(query.value(4).toFloat());
             ui->SQDM_EquipmentHumidity_QwtTherm->setValue(query.value(5).toFloat());
             ui->SQDM_Equipmentelectric_LCDNumber->display(abs((query.value(6).toFloat()-512)/1024*5*100));
         }

     }
 }

Request* RequestHistory::getRequest(int requestId)
{
    QSqlQuery *q = new QSqlQuery(m_database);
    q->prepare("SELECT * FROM requests WHERE id = :id");
    q->bindValue(":id", requestId);
    query(q);
    if( !q->first() ) {
        delete q;
        throw false;//Request not found
    }

    Request *request = new Request(q->value("url").toString(), q->value("type").toString());
    request->setResponse(q->value("response").toString());
    request->setResponseCode(q->value("code").toInt());
    request->setError(q->value("error").toString());
    request->setMessage(q->value("message").toString());
    QString gistId = q->value("gist_id").toString();
    if (!gistId.isEmpty()) {
        request->setGistId(q->value("gist_id").toString());
    }
    q->clear();

    //load params
    q->prepare("SELECT * FROM requests_params WHERE request_id = :id");
    q->bindValue(":id", requestId);
    query(q);
    while (q->next()) {
        request->addRequestParam(q->value("name").toString(), q->value("value").toString());
    }
    q->clear();

    //load headers
    q->prepare("SELECT * FROM request_headers WHERE request_id = :id");
    q->bindValue(":id", requestId);
    query(q);
    while (q->next()) {
        request->addRequestHeader(q->value("name").toString(), q->value("value").toString());
    }
    q->clear();

    //load response headers
    q->prepare("SELECT * FROM response_headers WHERE request_id = :id");
    q->bindValue(":id", requestId);
    query(q);
    while (q->next()) {
        request->addResponseHeader(q->value("name").toString(), q->value("value").toString());
    }
    q->clear();

    q->prepare("SELECT * FROM request_raw WHERE request_id = :id");
    q->bindValue(":id", requestId);
    query(q);
    if( q->first() ) {
        request->setRaw(q->value("body").toString());
    }
    delete q;
    return request;
}

bool historialClinico::buscar()
{
    QSqlQuery query;
    query.prepare("select * from HistorialClinico where idCliente=? and doctor=? and fecha=?");
    query.bindValue(0,idCliente);
    query.bindValue(1,doctor);
    query.bindValue(2,fecha);
    if(query.exec())
    {
        if(query.size()!=0)
        {
            query.first();
            idHistorialClinico=query.value(0).toString();
            query.clear();
            query.prepare("select idMedidaHistorial from HistorialClinico_has_MedidaHistorial where idHistorialClinico=? and distancia=? and ojo=?");
            query.bindValue(0,idHistorialClinico);
            query.bindValue(1,"Cerca");
            query.bindValue(2,"Derecho");
            query.exec();
            query.first();
            mCercaDerecha.setIdMedidasHistorial(query.value(0).toString());
            query.clear();
            query.prepare("select idMedidaHistorial from HistorialClinico_has_MedidaHistorial where idHistorialClinico=? and distancia=? and ojo=?");
            query.bindValue(0,idHistorialClinico);
            query.bindValue(1,"Cerca");
            query.bindValue(2,"Izquierdo");
            query.exec();
            query.first();
            mCercaIzquierda.setIdMedidasHistorial(query.value(0).toString());
            query.clear();
            query.prepare("select idMedidaHistorial from HistorialClinico_has_MedidaHistorial where idHistorialClinico=? and distancia=? and ojo=?");
            query.bindValue(0,idHistorialClinico);
            query.bindValue(1,"Lejos");
            query.bindValue(2,"Derecho");
            query.exec();
            query.first();
            mLejosDerecha.setIdMedidasHistorial(query.value(0).toString());
            query.clear();
            query.prepare("select idMedidaHistorial from HistorialClinico_has_MedidaHistorial where idHistorialClinico=? and distancia=? and ojo=?");
            query.bindValue(0,idHistorialClinico);
            query.bindValue(1,"Lejos");
            query.bindValue(2,"Izquierdo");
            query.exec();
            query.first();
            mLejosIzquierda.setIdMedidasHistorial(query.value(0).toString());
            if(mCercaDerecha.buscar()&&mCercaIzquierda.buscar()&&mLejosDerecha.buscar()&&mLejosIzquierda.buscar())
                return true;
            else
                return false;
        }
        return false;
    }
    else
        return false;
    return false;
}

void MainWindow::on_searchButton_clicked()
{
    int currentId = -1;

    //Запрос на наличие записи
    QSqlQuery *q = new QSqlQuery(db);
    q->prepare("SELECT wtable.id_word FROM wtable WHERE word=?");
    q->bindValue(0, ui->wordLine->text());
    q->exec();

    if (q->next())
    {
        // Идентификатор введенного слова
        currentId = q->value(0).toInt();

        // Сбрасываем запрос
        q->clear();
        QString str = "";

        // Выполняем новый запрос, который вернет нам синонимы
        q->prepare("SELECT wtable.word FROM wtable, stable WHERE stable.id_syn = wtable.id_word AND stable.id_word = ?");
        q->bindValue(0, currentId);
        q->exec();

        if (q->next())
        {
            do
            {
                str.append("-- ").append(q->value(0).toString()).append("\n");
            }
            while (q->next());
        }
        else
        {
            q->clear();
            q->prepare("SELECT wtable.word FROM wtable, stable WHERE stable.id_word = wtable.id_word AND stable.id_syn = ?");
            q->bindValue(0, currentId);
            q->exec();

            while (q->next())
                str.append("-- ").append(q->value(0).toString()).append("\n");
        }

        // Закинуть текст в виджет
        ui->synList->setText(str);
    }
    else
        QMessageBox::critical(0, "Ошибка", "Данного слова нет в базе");
}

/**
 * @brief Database::AddRevenue
 * Add amount to revenue attribute in stadiums table
 * @param id stadiums id
 * @param revenue of the stadium
 * @return
 */
bool Database::AddRevenue(int id, double revenue)
{
    QSqlQuery query;
    query.prepare("select revenue from stadiums where id = :id");
    query.bindValue(":id", id);
    if(query.exec()){
        if(query.next()){
            double originalRev = query.record().field("revenue").value().toDouble();
            query.clear();
            query.prepare("update stadiums set revenue = :newRev where id = :id");
            double newRev = originalRev + revenue;
            query.bindValue(":newRev", newRev);
            query.bindValue(":id", id);
            return query.exec();
        }
        else
        {
            qDebug() << query.lastError().text();
            return false;
        }
    }
    else
    {
        qDebug() << query.lastError().text();
        return false;
    }
}

QString Prefix::getPath(const QString prefix_name) const{
    QString value;

    if (prefix_name == "Default"){
        value = QDir::homePath();
        value.append("/.wine");
        return value;
    }

    QSqlQuery query;

    query.prepare("SELECT path FROM prefix WHERE name=:prefix_name");
    query.bindValue(":prefix_name", prefix_name);

    if (query.exec()){
        query.first();
        if (query.isValid()){
            if (query.value(0).toString().isEmpty()){
                value = QDir::homePath();
                value.append("/.wine");
            } else {
                value.append(query.value(0).toString());
            }
        }
    } else {
        qDebug()<<"SqlError: "<<query.lastError();
    }
    query.clear();
    return value;
}

//**********************************************
st_qRes SendSimpleQueryStrWR(QSqlDatabase *db,const QString& q_str)
{
    st_qRes result;

    result.sel_data.clear();
    st_svMAP col;

    QSqlQuery *query = new QSqlQuery(*db);

    if(!query->exec(q_str)){
        db->rollback();
        result.q_result = false;
        qDebug() << query->lastError().databaseText();
        qDebug() << query->lastError().driverText();
        QMessageBox::critical(new QWidget,QObject::tr("Error"),"query: "+q_str+"\ndriver: "+
                              query->lastError().driverText()+
                              "\ndatabase: "+query->lastError().databaseText());
    }
    else{
        result.q_result = true;
        st_svMAP col;
        while(query->next()){
            for(int counter=0; counter < query->record().count() ;counter++){
                col.map.insert(query->record().fieldName(counter),query->value(counter));
            }
            result.sel_data << col;
            col.map.clear();
        }
    }
    query->clear();
    return result;
}