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

DatabaseException::DatabaseException(const QSqlQuery& query)
{
    m_message = query.lastError().text();
    qDebug() << "DatabaseException: error = " << m_message;
    qDebug() << "DatabaseException: sql = " << query.lastQuery();    
    QList<QVariant> list = query.boundValues().values();
    int n = list.size();
    qDebug() << "Bound values: size = " << n;
    for (int i = 0; i < n; i++)
        qDebug() << "Bound value position " << i << ": " << list.at(i);

    QMapIterator<QString, QVariant> i(query.boundValues());
    while (i.hasNext()) {
        i.next();
        qDebug() << "Bound value " << i.key() << ": " << i.value();
    }
}

QSqlQuery SqlDatabase::exec(const QSqlQuery &query)
{
    QSqlQuery out(databaseForThread(QThread::currentThread()));
    out.prepare(query.lastQuery());

    const QList<QVariant> boundValues = query.boundValues().values();

    foreach (const QVariant &variant, boundValues) {
        out.addBindValue(variant);
    }

QString getLastExecutedQuery(const QSqlQuery& query)
{
    QString str = query.lastQuery();
    QMapIterator<QString, QVariant> it(query.boundValues());
    while (it.hasNext())
    {
      it.next();
      str.replace(it.key(),it.value().toString());
    }
    return str;
}

bool Database::CheckErrors(const QSqlQuery& query) {
    QSqlError last_error = query.lastError();
    if (last_error.isValid()) {
        qLog(Error) << "db error: " << last_error;
        qLog(Error) << "faulty query: " << query.lastQuery();
        qLog(Error) << "bound values: " << query.boundValues();

        return true;
    }

    return false;
}

void DataManager::onProcessPacket(const mon_bin_get* packet)
{
	QSqlQuery packetQuery;
	packetQuery.prepare("INSERT INTO `packets` "
			"(urb_id, device, bus, endpoint, time, status) "
			"VALUES(?,?,?,?,?,?);");
	QSqlQuery dataQuery;
	dataQuery.prepare("INSERT INTO `data` "
				"(packet_id, data, type) "
				"VALUES(?, ?, ?);");
	packetQuery.bindValue(0, packet->header->id);
	packetQuery.bindValue(1, packet->header->device);
	packetQuery.bindValue(2, packet->header->bus);
	packetQuery.bindValue(3, packet->header->endpoint);
	packetQuery.bindValue(4, QDateTime::fromTime_t(packet->header->ts_sec).addMSecs(packet->header->ts_usec/1000).toString("hh:mm:ss.zzz"));
	packetQuery.bindValue(5, (unsigned char)packet->header->status);

	if(!packetQuery.exec())
		goto error;

	dataQuery.bindValue(0, packetQuery.lastInsertId().toUInt());
	packetQuery.finish();

	if(packet->header->flag_setup == 0)
	{
		QByteArray setupData((const char*) packet->header->setup, 8);
		dataQuery.bindValue(1, setupData);
		dataQuery.bindValue(2, "Setup");
	}
	else if(packet->header->lengthCaptured > 0)
	{
		QByteArray data((const char*) packet->data, packet->header->lengthCaptured);
		dataQuery.bindValue(1, data);
		dataQuery.bindValue(2, "Data");
	}

	if(dataQuery.boundValues().size() == 3 && !dataQuery.exec())
		goto error;

	dataQuery.finish();
	goto cleanup;

error:
	database.rollback();
	qDebug() << "Database error: " << database.lastError().databaseText() << database.lastError().driverText();
	goto cleanup;

cleanup:
	delete packet->header;
	delete[] (char*)packet->data;
	delete packet;
	return;
}

QString sqlite::getLastExecutedQuery(const QSqlQuery &query){
        QString str = query.lastQuery();
        QMapIterator<QString, QVariant> it(query.boundValues());

        it.toBack();

        while (it.hasPrevious())
        {
            it.previous();
            str.replace(it.key(),it.value().toString());
        }
        return str;
}

void QSqlQuery_snippets()
{
    {
    // typical loop
//! [7]
    QSqlQuery query("SELECT country FROM artist");
    while (query.next()) {
        QString country = query.value(0).toString();
        doSomething(country);
    }
//! [7]
    }

    {
    // field index lookup
//! [8]
    QSqlQuery query("SELECT * FROM artist");
    int fieldNo = query.record().indexOf("country");
    while (query.next()) {
        QString country = query.value(fieldNo).toString();
        doSomething(country);
    }
//! [8]
    }

    {
    // named with named
//! [9]
    QSqlQuery query;
    query.prepare("INSERT INTO person (id, forename, surname) "
                  "VALUES (:id, :forename, :surname)");
    query.bindValue(":id", 1001);
    query.bindValue(":forename", "Bart");
    query.bindValue(":surname", "Simpson");
    query.exec();
//! [9]
    }

    {
    // positional with named
//! [10]
    QSqlQuery query;
    query.prepare("INSERT INTO person (id, forename, surname) "
                  "VALUES (:id, :forename, :surname)");
    query.bindValue(0, 1001);
    query.bindValue(1, "Bart");
    query.bindValue(2, "Simpson");
    query.exec();
//! [10]
    }

    {
    // positional 1
//! [11]
    QSqlQuery query;
    query.prepare("INSERT INTO person (id, forename, surname) "
                  "VALUES (?, ?, ?)");
    query.bindValue(0, 1001);
    query.bindValue(1, "Bart");
    query.bindValue(2, "Simpson");
    query.exec();
//! [11]
    }

    {
    // positional 2
//! [12]
    QSqlQuery query;
    query.prepare("INSERT INTO person (id, forename, surname) "
                  "VALUES (?, ?, ?)");
    query.addBindValue(1001);
    query.addBindValue("Bart");
    query.addBindValue("Simpson");
    query.exec();
//! [12]
    }

    {
    // stored
//! [13]
    QSqlQuery query;
    query.prepare("CALL AsciiToInt(?, ?)");
    query.bindValue(0, "A");
    query.bindValue(1, 0, QSql::Out);
    query.exec();
    int i = query.boundValue(1).toInt(); // i is 65
//! [13]
    Q_UNUSED(i);
    }

    QSqlQuery query;

    {
    // examine with named binding
//! [14]
    QMapIterator<QString, QVariant> i(query.boundValues());
    while (i.hasNext()) {
        i.next();
        cout << i.key().toUtf8().data() << ": "
             << i.value().toString().toUtf8().data() << endl;
//.........这里部分代码省略.........

bool Login::init(Datapipe *datapipe, const QString &params)
{
    this->datapipe = datapipe;
    this->params  = params;

    QHBoxLayout *mainLayout = new QHBoxLayout();
    setLayout(mainLayout);

    QSqlQuery   query;

    loglevel = datapipe->getVariables()->value(_S("loglevel")).toInt();

    query.prepare(datapipe->getSettings()->value(_S("login/queries/userslist")).toString());
    query.bindValue(":current_base_id", datapipe->getVariables()->value(_S("current_base_id")));

    if (!query.exec())
    {
        qCritical() << _T("%1 Ошибка: %2\nЗапрос: %3\nПеременные: %4")
                    .arg(posForLog,
                         query.lastError().text(),
                         query.lastQuery(),
                         Service::join(query.boundValues()));

        QMessageBox message;
        message.setText(_T("Ошибка получения списка пользователей\nТекст: %1\n").arg(query.lastError().text()));
        message.exec();

        return false;
    }

    model.setQuery(query);
    model.setHeaderData(0, Qt::Horizontal, _T("Пользователи"));

    view.setModel(&model);
    view.setSelectionMode(QAbstractItemView::SingleSelection);

    view.hideColumn(1);
    view.hideColumn(2);

    QHeaderView *header = view.verticalHeader();
    header->setDefaultSectionSize(datapipe->getVariables()->value(_S("standart_text_height")).toInt());
    header->setVisible(false);

    header = view.horizontalHeader();
    header->setStretchLastSection(true);

    view.setFocusPolicy(Qt::NoFocus);
    mainLayout->addWidget(&view);

    QVBoxLayout *numpadLayout = new QVBoxLayout();
    mainLayout->addLayout(numpadLayout);

    Numpad *npad = new Numpad(datapipe, this);
    npad->setEchoMode(QLineEdit::Password);
    numpadLayout->addWidget(npad);
    numpadLayout->addStretch();

    connect(npad, SIGNAL(inputCommited(QString)),
            this, SLOT(passwordCommited(QString)));

    setFocusProxy(npad);

    this->installEventFilter(this);

    datapipe->getVariables()->remove(_S("user"));
    datapipe->getVariables()->remove(_S("user_id"));

    return true;
}

void Cashbox::summCommited(const QString &text)
{
    bool ok;
    int operation_id;

    int row = view.currentIndex().row();
    if(row<0)
        ok=false;
    else
    {
        QSqlRecord record = model.record(row);
        operation_id = record.value(1).toInt(&ok);
    };

    if(!ok)
    {
        QMessageBox mb;
        mb.setText(_T("Ошибка: не выбрана операция"));
        mb.exec();
        return;
    }


     QSqlDatabase db = QSqlDatabase::database();
     db.transaction();


     QSqlQuery query;
     QSettings *settings = data->getSettings();

     query.prepare(settings->value("cashbox/queries/get_section_id").toString());
     query.bindValue(":operation_id", operation_id);
     if(!query.exec()){
         db.rollback();
         qDebug()<<posInFile<<query.lastError().text()<<endl<<query.lastQuery()<<endl<<query.boundValues();
         return;
     }

     query.next();
     int section_id = query.value(0).toInt();

     query.prepare(settings->value("cashbox/queries/get_new_document_id").toString());
     if(!query.exec()){
         db.rollback();
         qDebug()<<posInFile<<query.lastError().text()<<endl<<query.lastQuery()<<endl<<query.boundValues();
         return;
     }

     query.next();
     int doc_id = query.value(0).toInt();

     query.prepare(settings->value("cashbox/queries/save_document").toString());
     //:id,:data,:section_id,:operation_id,:store,:user
     query.bindValue(":id", doc_id);
     query.bindValue(":data", QDate::currentDate());
     query.bindValue(":time", QTime::currentTime());
     query.bindValue(":section_id", section_id);
     query.bindValue(":operation_id", operation_id);
     query.bindValue(":summ", text.toDouble());
     query.bindValue(":store", data->getVariables()->value("current_base_id"));
     query.bindValue(":user", data->getVariables()->value("user_id"));

     if(!query.exec()){
         db.rollback();
         qDebug()<<posInFile<<query.lastError().text()<<endl<<query.lastQuery()<<endl<<query.boundValues();
         return;
     }

     db.commit();

     updateTotals();

}

void Cashbox::updateTotals()
{
    QString text;// = "<h1>Свод по кассе</h1><br>";

    QSqlQuery query;
    double summOnStart = 0;

    QDate start_date = QDate(2000,1,1);
    QTime start_time = QTime(0,0,0);

    QSettings *settings = data->getSettings();

    query.prepare(settings->value("cashbox/queries/first_shiftwork_document").toString());
    query.bindValue(":store", data->getVariables()->value("current_base_id"));
    query.bindValue(":user", data->getVariables()->value("user_id"));

    if(!query.exec()){
        qDebug()<<posInFile<<query.lastError().text()<<endl<<query.lastQuery()<<endl<<query.boundValues();
    }else {
        if(query.next()){
            start_date = query.value(0).toDate();
            start_time = query.value(1).toTime();
        }
    }


    text += "<h2>Товарные операции</h2>";
    query.prepare(settings->value("cashbox/queries/goods_operations_sum").toString());
    query.bindValue(":time_start",start_time);
    query.bindValue(":date_start",start_date);
    query.bindValue(":date_start2",start_date);

    if(!query.exec()){
        qDebug()<<posInFile<<query.lastError().text()<<endl<<query.lastQuery()<<endl<<query.boundValues();
    }
    while (query.next()) {
        text += "<li>"+query.value(0).toString() + ": "+ QString::number(query.value(1).toDouble(),'f',2);
        summOnStart -= query.value(1).toDouble();
    }

    text += "<h2>Денежные операции</h2>";
    query.prepare(settings->value("cashbox/queries/money_operations_sum").toString());
    query.bindValue(":time_start",start_time);
    query.bindValue(":date_start",start_date);
    query.bindValue(":date_start2",start_date);
   // qDebug()<<query.lastQuery()<<Service::join(query.boundValues());
    if(!query.exec()){
        qDebug()<<posInFile<<query.lastError().text()<<endl<<query.lastQuery()<<endl<<query.boundValues();
    }
    while (query.next()) {
        text += "<li>"+query.value(0).toString() + ": "+ QString::number(query.value(1).toDouble(),'f',2);
        summOnStart -= query.value(1).toDouble();
    }

    query.prepare(settings->value("cashbox/queries/rests_goods_sum").toString());
    if(!query.exec()){
        qDebug()<<posInFile<<query.lastError().text()<<endl<<query.lastQuery()<<endl<<query.boundValues();
    }
    double rests = 0;
    while (query.next())
       rests += query.value(0).toDouble();

    query.prepare(settings->value("cashbox/queries/rests_money_sum").toString());
    if(!query.exec()){
        qDebug()<<posInFile<<query.lastError().text()<<endl<<query.lastQuery()<<endl<<query.boundValues();
    }
    while (query.next())
       rests += query.value(0).toDouble();

    text += "<h2>Остаток в кассе: "+ QString::number(rests,'f',2)+"</h2>";
    summOnStart += rests;


    text = "Смена с "+start_date.toString("dd.MM.yyyy")+" "+start_time.toString("hh:mm:ss")
             + "<h2>Остаток на начало: "+ QString::number(summOnStart,'f',2)+"</h2>"
             + text;



    totals.setText(text);
}

//.........这里部分代码省略.........
      }
      else if ( type == QVariant::String )
      {
        // binding a TEXT value
        query.bindValue( bindIdx,  attrs.at( i ).toString() );
      }
      else if ( type == QVariant::Time )
      {
        // binding a TIME value
        query.bindValue( bindIdx,  attrs.at( i ).toTime().toString( Qt::ISODate ) );
      }
      else if ( type == QVariant::Date )
      {
        // binding a DATE value
        query.bindValue( bindIdx,  attrs.at( i ).toDate().toString( Qt::ISODate ) );
      }
      else if ( type == QVariant::DateTime )
      {
        // binding a DATETIME value
        query.bindValue( bindIdx,  attrs.at( i ).toDateTime().toString( Qt::ISODate ) );
      }
      else
      {
        query.bindValue( bindIdx,  attrs.at( i ) );
      }

#if 0
      QgsDebugMsg( QString( "bound i: %1; name: %2; value: %3; bindIdx: %4" ).
                   arg( i ).arg( fld.name() ).arg( attrs.at( i ).toString() ).arg( bindIdx ) );
#endif
      bindIdx++;
    }

    if ( !mGeometryColName.isEmpty() )
    {
      QgsGeometry geom = it->geometry();

      QByteArray bytea = QByteArray(( char* )geom.asWkb(), ( int ) geom.wkbSize() );
      query.bindValue( bindIdx,  bytea, QSql::In | QSql::Binary );
    }

    QList<QVariant> list = query.boundValues().values();

// Show bound values
#if 0
    for ( int i = 0; i < list.size(); ++i )
    {
      QgsDebugMsg( QString( "i: %1; value: %2; type: %3" )
                   .arg( i ).arg( list.at( i ).toString().toLatin1().data() ).arg( list.at( i ).typeName() ) );
    }
#endif
    if ( !query.exec() )
    {
      QString msg = query.lastError().text();
      QgsDebugMsg( msg );
      if ( !mSkipFailures )
      {
        pushError( msg );
        return false;
      }
    }

    statement = QString( "select IDENTITY_VAL_LOCAL() AS IDENTITY "
                         "FROM SYSIBM.SYSDUMMY1" );
//    QgsDebugMsg( statement );
    if ( !queryFid.exec( statement ) )
    {
      QString msg = query.lastError().text();
      QgsDebugMsg( msg );
      if ( !mSkipFailures )
      {
        pushError( msg );
        return false;
      }
    }

    if ( !queryFid.next() )
    {
      QString msg = query.lastError().text();
      QgsDebugMsg( msg );
      if ( !mSkipFailures )
      {
        pushError( msg );
        return false;
      }
    }
    it->setFeatureId( queryFid.value( 0 ).toLongLong() );
    writeCount++;
//    QgsDebugMsg( QString( "count: %1; featureId: %2" ).arg( writeCount ).arg( queryFid.value( 0 ).toLongLong() ) );
  }
  bool commitStatus = mDatabase.commit();
  QgsDebugMsg( QString( "commitStatus: %1; write count: %2; featureId: %3" )
               .arg( commitStatus ).arg( writeCount ).arg( queryFid.value( 0 ).toLongLong() ) );
  if ( !commitStatus )
  {
    pushError( "Commit of new features failed" );
    return false;
  }
  return true;
}