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;
}
