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; } //! [14] } { // examine with positional binding //! [15] QList<QVariant> list = query.boundValues().values(); for (int i = 0; i < list.size(); ++i) cout << i << ": " << list.at(i).toString().toUtf8().data() << endl; //! [15] } }
bool Login::init(Datapipe *datapipe, const QString ¶ms) { 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); }
bool QgsDb2Provider::addFeatures( QgsFeatureList & flist ) { QgsDebugMsg( "mGeometryColType: " + mGeometryColType ); int writeCount = 0; bool copyOperation = false; if ( !mDatabase.isOpen() ) { QString errMsg; mDatabase = getDatabase( mConnInfo, errMsg ); if ( !errMsg.isEmpty() ) { QgsDebugMsg( "getDatabase failed: " + errMsg ); return false; } } if ( !mDatabase.transaction() ) { QgsDebugMsg( "transaction failed" ); return false; } QSqlQuery query = QSqlQuery( mDatabase ); query.setForwardOnly( true ); QSqlQuery queryFid = QSqlQuery( mDatabase ); queryFid.setForwardOnly( true ); QgsFeature it = flist.at( 0 ); QString statement; QString values; statement = QString( "INSERT INTO %1.%2 (" ).arg( mSchemaName, mTableName ); bool first = true; // Get the first geometry and its wkbType as when we are doing drag/drop, // the wkbType is not passed to the DB2 provider from QgsVectorLayerImport // Can't figure out how to resolved "unreferenced" wkbType compile message // Don't really do anything with it at this point #if 0 QgsGeometry *geom = it.geometry(); QgsWkbTypes::Type wkbType = geom->wkbType(); QgsDebugMsg( QString( "wkbType: %1" ).arg( wkbType ) ); QgsDebugMsg( QString( "mWkbType: %1" ).arg( mWkbType ) ); #endif QgsAttributes attrs = it.attributes(); QgsDebugMsg( QString( "attrs.count: %1" ).arg( attrs.count() ) ); QgsDebugMsg( QString( "fields.count: %1" ).arg( mAttributeFields.count() ) ); if ( mAttributeFields.count() == ( attrs.count() + 1 ) ) { copyOperation = true; // FID is first field but no attribute in attrs } else if ( mAttributeFields.count() != attrs.count() ) { QgsDebugMsg( "Count mismatch - failing" ); return false; } if ( attrs.count() != mAttributeFields.count() ) { QgsDebugMsg( "field counts don't match" ); // return false; } for ( int i = 0; i < mAttributeFields.count(); ++i ) { QgsField fld = mAttributeFields.at( i ); QgsDebugMsg( QString( "i: %1; got field: %2" ).arg( i ).arg( fld.name() ) ); if ( fld.name().isEmpty() ) continue; // invalid if ( mFidColName == fld.name() ) continue; // skip identity field // if ( mDefaultValues.contains( i ) && mDefaultValues[i] == attrs.at( i ) ) // continue; // skip fields having default values if ( !first ) { statement += ','; values += ','; } else first = false; statement += QString( "%1" ).arg( fld.name() ); values += QString( "?" ); } // append geometry column name if ( !mGeometryColName.isEmpty() ) { if ( !first ) { statement += ','; values += ','; } statement += QString( "%1" ).arg( mGeometryColName ); values += QString( "db2gse.%1(CAST (%2 AS BLOB(2M)),%3)" ) .arg( mGeometryColType, QString( "?" ), QString::number( mSRId ) ); } QgsDebugMsg( statement ); QgsDebugMsg( values ); statement += ") VALUES (" + values + ')'; QgsDebugMsg( statement ); QgsDebugMsg( "Prepare statement" ); // use prepared statement to prevent from sql injection if ( !query.prepare( statement ) ) { QString msg = query.lastError().text(); QgsDebugMsg( msg ); pushError( msg ); return false; } for ( QgsFeatureList::iterator it = flist.begin(); it != flist.end(); ++it ) { attrs = it->attributes(); int fieldIdx = 0; if ( copyOperation ) { fieldIdx = 1; // skip first (FID) field if copying from shapefile } int bindIdx = 0; for ( int i = 0; i < attrs.count(); i++ ) { QgsField fld = mAttributeFields.at( fieldIdx++ ); if ( fld.name().isEmpty() ) continue; // invalid if ( mFidColName == fld.name() ) continue; // skip identity field // if ( mDefaultValues.contains( i ) && mDefaultValues[i] == attrs.at( i ) ) // continue; // skip fields having default values QVariant::Type type = fld.type(); if ( attrs.at( i ).isNull() || !attrs.at( i ).isValid() ) { // binding null values if ( type == QVariant::Date || type == QVariant::DateTime ) query.bindValue( bindIdx, QVariant( QVariant::String ) ); else query.bindValue( bindIdx, QVariant( type ) ); } else if ( type == QVariant::Int ) { // binding an INTEGER value query.bindValue( bindIdx, attrs.at( i ).toInt() ); } else if ( type == QVariant::Double ) { // binding a DOUBLE value query.bindValue( bindIdx, attrs.at( i ).toDouble() ); } 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; }