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

void add_new_job::on_submit_new_position_button_clicked()
{
    QString subject_name = this->ui->position_name_edit->text();
    int picked_index = this->ui->subject_picker->currentIndex();

    QSqlQuery query;
    query.prepare("insert into positions(position_name,id_subject) values(?,?)");
    query.addBindValue(subject_name);

    if(this->ui->is_teacher_checkbox->checkState() == Qt::Unchecked)
        query.addBindValue(QVariant(QVariant::Int));
    else
        query.addBindValue(this->ui->subject_picker->model()->index(picked_index,1).data().toInt());

    query.exec();

    //error
    QMessageBox msg;
    if (query.lastError().type()!=QSqlError::NoError){
        msg.setText(query.lastError().text());
        msg.exec();
        return;
    }
    else{
        msg.setText("Rows affected - "+QString::number(query.numRowsAffected()));
        msg.exec();
    }

    this->ui->position_name_edit->clear();
    this->ui->is_teacher_checkbox->setCheckState(Qt::Unchecked);
    this->ui->subject_picker->model()->deleteLater();

    emit restore_main_menu();
    this->hide();
}

int QSqlCursor::applyPrepared( const QString& q, bool invalidate )
{
    int ar = 0;
    QSqlQuery* sql = 0;
    
    if ( invalidate ) {
	sql = (QSqlQuery*)this;
	d->lastAt = QSql::BeforeFirst;
    } else {
	sql = d->query();
    }
    if ( !sql )
	return 0;
    
    if ( invalidate || sql->lastQuery() != q ) {
	if ( !sql->prepare( q ) )
	    return 0;
    }
    
    int cnt = 0;
    int fieldCount = (int)count();
    for ( int j = 0; j < fieldCount; ++j ) {
	const QSqlField* f = d->editBuffer.field( j );
	if ( d->editBuffer.isGenerated( j ) ) {
	    sql->bindValue( cnt, f->value() );
	    cnt++;
	}
    }
    if ( sql->exec() ) {
	ar = sql->numRowsAffected();
    }
    return ar;
}

void CTaskType::fillTaskModel(QSqlQuery &stored)
{
    modelTask->removeRows(0, modelTask->rowCount(QModelIndex()), QModelIndex());
    modelTask->insertRows(stored.numRowsAffected(), 0);

#ifndef QT_NO_CURSOR
    QApplication::setOverrideCursor(QCursor(QPixmap("data/picture/additionally/wait.png")));
#endif

    QString   it;
    int ncols = stored.record().count();
    int row(0);

    while(stored.next())
    {
        for (int i = 0; i != ncols; ++i){
            it = stored.value(i).toString();
            QStandardItem *item = new QStandardItem(it);
            if (i == stored.record().indexOf("tt_name")){
                   item->setIcon(QIcon("data/picture/sidebar/tasktype.ico"));
            }
            modelTask->setItem(row ,i, item);
        }
        ++row;
    }

#ifndef QT_NO_CURSOR
    QApplication::restoreOverrideCursor();
#endif
}

int JSQLInvitationCodeDB::removeInvitationCode(const QString& code)
{
	QSqlQuery query;
	
	QString dn = QSqlDatabase::database().driverName();
	// QSQLITE
	if( dn == "QSQLITE" ){
		PREPARE( query,
				"DELETE FROM "
				TableName
				" where `id` in ( "
				"  select `id` from "
				TableName
				"  where `code` = :code "
				"  order by `id` LIMIT 1 "
				" ) ",
				0);
	}else{
		PREPARE( query,
				"DELETE FROM "
				TableName
				" where code = :code "
				"LIMIT 1",
				0);
	}

	query.bindValue(":code" , code );

	EXEC( query , -1 );

	return query.numRowsAffected();
}

void assign_human_to_position::on_submit_button_clicked()
{
    int id_human = this->ui->human_picker->model()->index(this->ui->human_picker->currentIndex(),1).data().toInt();
    int id_administrating_position = this->ui->position_picker->model()->index(this->ui->position_picker->currentIndex(),1).data().toInt();
    int incentive = this->ui->incentive->text().toInt();
    QString date = this->ui->start_date->text();


    QSqlQuery query;
    query.prepare("insert into personnel(id_human,id_administrating_position,incentive,start_date,end_date) values(?,?,?,?,?)");
    query.addBindValue(id_human);
    query.addBindValue(id_administrating_position);
    query.addBindValue(incentive);
    query.addBindValue(date);
    query.addBindValue(QVariant(QVariant::Date));

    //error
    QMessageBox msg;
    if (query.lastError().type()!=QSqlError::NoError){
        msg.setText(query.lastError().text());
        msg.exec();
        return;
    }
    else{
        msg.setText("Rows affected - "+QString::number(query.numRowsAffected()));
        msg.exec();
    }

    this->ui->human_picker->model()->deleteLater();
    this->ui->position_picker->model()->deleteLater();
    this->ui->incentive->clear();
    this->ui->start_date->setDate(QDate::currentDate());
    emit restore_main_menu();
    this->hide();
}

/*!
 * Добавление канала.
 * Если канал уже добавлен, он будет обновлён.
 *
 * \param channel Указатель на канал.
 * \return Ключ в таблице \b channels или -1 в случае ошибки.
 *
 * \sa update().
 */
qint64 DataBase::add(ChatChannel channel)
{
  qint64 key = channel->key();
  if (key <= 0)
    key = channelKey(channel->id(), channel->type());

  if (key > 0) {
    if (channel->key() != key)
      channel->setKey(key);

    update(channel);
    return key;
  }

  QSqlQuery query;
  query.prepare(LS("INSERT INTO channels (channel, normalized, type, gender, name, data, date) "
                     "VALUES (:channel, :normalized, :type, :gender, :name, :data, :date);"));

  query.bindValue(LS(":channel"),    SimpleID::encode(channel->id()));
  query.bindValue(LS(":normalized"), SimpleID::encode(channel->normalized()));
  query.bindValue(LS(":type"),       channel->type());
  query.bindValue(LS(":gender"),     channel->gender().raw());
  query.bindValue(LS(":name"),       channel->name());
  query.bindValue(LS(":data"),       JSON::generate(channel->data()));
  query.bindValue(LS(":date"),       channel->date());
  query.exec();

  if (query.numRowsAffected() <= 0) {
    LOG_N6011
    return -1;
  }

int LanCDB::getFriendList(quint32 dwUserId, quint32& dwUserNum, UserInfoList& strus)
{
    std::stringstream oss;
    oss.str("");
    oss << "select user_name, user_flag from users where user_id in(select user_id_b from userGrant where deal_flag=1 and user_id_a=" << dwUserId;
    oss << " union select user_id_a from userGrant where deal_flag=1 and user_id_b=" << dwUserId << ")";

    qDebug() << "SQL=[" << oss.str().c_str() << "]";

    QSqlQuery *sqlQuery = new QSqlQuery(db);
    if (!sqlQuery->exec((char *)(oss.str().c_str())))
    {
        qDebug() << "Err_SQL=[" << oss.str().c_str() << "]";
        qDebug() << sqlQuery->lastError().databaseText();
        return LCDB_ERR_DBDATA_ERROR;
    }

    dwUserNum = 0;
    if (sqlQuery->numRowsAffected())
    {
        while (sqlQuery->next())
        {
            UserInfo stru;
            stru.szUsername = sqlQuery->value(0).toString();
            stru.shFlag = (quint16)sqlQuery->value(1).toInt();
            strus.push_back(stru);
            dwUserNum++;
            qDebug() << "int LanCDB::getFriendList(quint32 dwUserId, quint32& dwUserNum, UserInfoList& strus), username=" << stru.szUsername;
        }
    }
    return LCDB_ERR_SUCCESS;
}

int LanCDB::verifyUser(UserInfo &stru)
{
    std::stringstream oss;
    oss.str("");
    oss << "select user_id, user_flag from users where user_name='";
    oss << stru.szUsername.toUtf8().data() << "' and aes_decrypt(user_pwd, 'users.user_pwd')='";
    oss << stru.szUserPwd.toUtf8().data() << "'";

    qDebug() << "SQL=[" << oss.str().c_str() << "]";

    QSqlQuery *sqlQuery = new QSqlQuery(db);
    if (!sqlQuery->exec((char *)(oss.str().c_str())))
    {
        qDebug() << "Err_SQL=[" << oss.str().c_str() << "]";
        qDebug() << sqlQuery->lastError().databaseText();
        return LCDB_ERR_DBDATA_ERROR;
    }

    if (sqlQuery->numRowsAffected())
    {
        if (sqlQuery->next())
        {
            stru.dwUserId = (quint32)sqlQuery->value(0).toInt();
            stru.shFlag = (quint16)sqlQuery->value(1).toInt();
        }
        return LCDB_ERR_SUCCESS;
    }
    else
        return LCDB_ERR_USER_VerifyFailed;
}

QString DatabaseController::makeQueryResultString(const QSqlQuery& query, int iNbRowsSelected)
{
	QString szResultString;
	QTime time;

	int iNbRow = 0;
	if(query.isSelect()){
		iNbRow = query.size();
		if(iNbRow == -1){
			iNbRow = iNbRowsSelected;
		}
	} else {
		iNbRow = query.numRowsAffected();
	}

	// Write the time
	szResultString += time.currentTime().toString()+" => ";
	// Write sql error
	if(query.lastError().isValid()){
		szResultString += "Query executed with error(s) (" + query.lastError().text() + "): \n";
	}else{
		szResultString += "Query executed successfully: ";
	}
	// Write number of rows
	szResultString += QString::number(iNbRow)+" row(s) selected/affected\n";
	// Write query
	if(!query.lastQuery().isEmpty()){
		szResultString += query.lastQuery() + "\n";
	}
	szResultString += "\n";

	return szResultString;
}

void addstdform::on_btnOk_clicked()
{
    if(ui->leID->text()==""||ui->leStdNum->text()==""||ui->leName->text()==""||ui->lePwd->text()=="")
    {QMessageBox::about(this,"Message","item with * can't not be empty!");return ;}
     QSqlQuery query;
     query.exec("select count(*) from Student where ID='"+ui->leID->text()+"'");
     int ID=0;
     if(query.next())
            ID = query.value(0).toInt();
     if(ID!=0)
     {
            QMessageBox::about(this,"Message","this ID existed!");
            QString maxNum ;
            query.exec("select  ID from Student order by ID desc");
            if(query.next())
            {
                maxNum = query.value(0).toString();
            }
            QMessageBox::about(this,"Message","max ID="+maxNum);
            return ;
     }
     query.exec("select * from Student where Name='"+ui->leName->text()+"'");
     if(query.next())
     {
         QMessageBox::about(this,"Message",tr("该用户已经存在！"));
         return ;
     }
     query.exec("insert into Student values('"+ui->leID->text()+"','"+ui->leStdNum->text()+"','"+ui->leName->text()+"','"+ui->lePwd->text()+"')");
     if(query.isActive())
     {
            query.numRowsAffected();
            QMessageBox::about(this,"message","add success");
     }

}

void Positions::fillingModel(QSqlQuery &stored)
{
    m_model->clear();
    m_model->insertColumns(0, MODEL_COLUMN_COUNT);
    m_model->insertRows(0, stored.numRowsAffected());

    unsigned  j(0);
    QString   m_item;

    unsigned ncols = stored.record().count();
#ifndef QT_NO_CURSOR
    QApplication::setOverrideCursor(QCursor(Qt::WaitCursor));
#endif
    while(stored.next())
    {
        for (unsigned i = 0; i != ncols; ++i) {
            if (stored.value(i).toDateTime().isValid()) {
                m_item = stored.value(i).toDateTime().toString("yyyy-MM-dd hh:mm:ss");
            } else {
                m_item = stored.value(i).toString();
            }
            QStandardItem *item = new QStandardItem(m_item.trimmed());
            if (i == 1) {
                item->setIcon(QIcon("data/picture/sidebar/positions.ico"));
            }
            m_model->setItem(j ,i, item);
        }
        ++j;
    }
#ifndef QT_NO_CURSOR
    QApplication::restoreOverrideCursor();
#endif
}

void FrmCatch::setPreviewQuery()
{
    if (m_sample==0) return;

    QString strQuery=
    tr("SELECT     dbo.Sampled_Catch.ID, dbo.Ref_Commercial_Categories.Name AS [Commercial Category], ")+
    tr("                      dbo.Sampled_Catch.catch_weight_estimated AS [Total Catch], dbo.Ref_Units.Name AS Units")+
    tr(" FROM         dbo.Sampled_Catch INNER JOIN")+
    tr("                      dbo.Ref_Commercial_Categories ON dbo.Sampled_Catch.id_commercial_category = dbo.Ref_Commercial_Categories.ID INNER JOIN")+
    tr("                      dbo.Ref_Units ON dbo.Sampled_Catch.id_catch_no_boxes_units = dbo.Ref_Units.ID AND ")+
    tr("                      dbo.Sampled_Catch.id_catch_units_units = dbo.Ref_Units.ID AND dbo.Sampled_Catch.id_catch_weight_units = dbo.Ref_Units.ID AND ")+
    tr("                      dbo.Sampled_Catch.id_sample_units = dbo.Ref_Units.ID")+
    tr("                      WHERE     (dbo.Sampled_Catch.id_fishing_operation = :id) ORDER BY ID DESC")
    ;

    QSqlQuery query;
    query.prepare( strQuery );
    query.bindValue(0,m_sample->operationId);
    if (!query.exec()){
        emit showError(tr("Could not obtain filter for Sampled Catches!"));
        return;
    }

    qDebug() << query.numRowsAffected() << endl;

    viewCatch->setQuery(query);

    tableView->hideColumn(0);
    resizeToVisibleColumns(tableView);
}

Response::ResponseCode ServerSocketInterface::cmdDeckUpload(const Command_DeckUpload &cmd, ResponseContainer &rc)
{
    if (authState != PasswordRight)
        return Response::RespFunctionNotAllowed;

    if (!cmd.has_deck_list())
        return Response::RespInvalidData;

    sqlInterface->checkSql();

    QString deckStr = QString::fromStdString(cmd.deck_list());
    DeckList deck(deckStr);

    QString deckName = deck.getName();
    if (deckName.isEmpty())
        deckName = "Unnamed deck";

    if (cmd.has_path()) {
        int folderId = getDeckPathId(QString::fromStdString(cmd.path()));
        if (folderId == -1)
            return Response::RespNameNotFound;

        QSqlQuery *query = sqlInterface->prepareQuery("insert into {prefix}_decklist_files (id_folder, id_user, name, upload_time, content) values(:id_folder, :id_user, :name, NOW(), :content)");
        query->bindValue(":id_folder", folderId);
        query->bindValue(":id_user", userInfo->id());
        query->bindValue(":name", deckName);
        query->bindValue(":content", deckStr);
        sqlInterface->execSqlQuery(query);

        Response_DeckUpload *re = new Response_DeckUpload;
        ServerInfo_DeckStorage_TreeItem *fileInfo = re->mutable_new_file();
        fileInfo->set_id(query->lastInsertId().toInt());
        fileInfo->set_name(deckName.toStdString());
        fileInfo->mutable_file()->set_creation_time(QDateTime::currentDateTime().toTime_t());
        rc.setResponseExtension(re);
    } else if (cmd.has_deck_id()) {
        QSqlQuery *query = sqlInterface->prepareQuery("update {prefix}_decklist_files set name=:name, upload_time=NOW(), content=:content where id = :id_deck and id_user = :id_user");
        query->bindValue(":id_deck", cmd.deck_id());
        query->bindValue(":id_user", userInfo->id());
        query->bindValue(":name", deckName);
        query->bindValue(":content", deckStr);
        sqlInterface->execSqlQuery(query);

        if (query->numRowsAffected() == 0)
            return Response::RespNameNotFound;

        Response_DeckUpload *re = new Response_DeckUpload;
        ServerInfo_DeckStorage_TreeItem *fileInfo = re->mutable_new_file();
        fileInfo->set_id(cmd.deck_id());
        fileInfo->set_name(deckName.toStdString());
        fileInfo->mutable_file()->set_creation_time(QDateTime::currentDateTime().toTime_t());
        rc.setResponseExtension(re);
    } else
        return Response::RespInvalidData;

    return Response::RespOk;
}

bool SqlHelper::exec(const QString &sql) {
    QSqlQuery query;
    bool ret = query.exec(sql);
    if (!ret) {
        qDebug() << query.lastError();
    }

    return (ret && query.numRowsAffected());
}

bool Database::articleAdd(const QVariantMap values) {
    qDebug() << "db.articleAdd" << values["title"];

    QSqlQuery q;
    q.prepare("INSERT OR IGNORE INTO articles (id, type, date, timestamp, title, subtitle, nb_comments, icon, link, parent, content) "
              "VALUES (:id, :type, :date, :timestamp, :title, :subtitle, :nb_comments, :icon, :link, :parent, :content)");

    q.bindValue(":id"         , values["id"]);
    q.bindValue(":type"       , values.value("type", 0));
    q.bindValue(":date"       , values["date"]);
    q.bindValue(":timestamp"  , values["timestamp"]);
    q.bindValue(":title"      , values["title"]);
    q.bindValue(":subtitle"   , values.value("subtitle", QVariant())); // QVariant() is for NULL
    q.bindValue(":nb_comments", values["comments"]);
    q.bindValue(":icon"       , values.value("icon", QVariant()));
    q.bindValue(":link"       , values["link"]);
    q.bindValue(":parent"     , values.value("parent", -1));
    q.bindValue(":content"    , values.value("content", QVariant()));
    bool ret = q.exec();
    if (!ret) {
        qDebug() << "insert failed:" << q.lastError().text();
        return false;
    }

    qDebug() << "inserted rows: " << q.numRowsAffected();
    if (q.numRowsAffected() > 0) {
        return true;
    }

    // if article already exists
    q.prepare("UPDATE articles SET nb_comments = :nb_comments, new_comments = 1 "
              "WHERE id = :id AND type = :type AND nb_comments < :nb_comments");
    q.bindValue(":id"         , values["id"]);
    q.bindValue(":type"       , values.value("type", 0));
    q.bindValue(":nb_comments", values["comments"]);
    ret = q.exec();
    if (!ret) {
        qDebug() << "update failed:" << q.lastError().text();
        return false;
    }

    qDebug() << "updated rows: " << q.numRowsAffected();
    return ret;
}