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

void addJingle::on_pushButton_clicked()
{
    if(!adb.isOpen()){
        /*connect to db*/
            adb=QSqlDatabase::addDatabase("QSQLITE");
            adb.setDatabaseName("/home/fred/adb.db");
            adb.open();
        /*eof connect to db*/
    }

    QString jpath = ui->txt_file->text();
    QString jname = ui->txt_name->text();



    QSqlQuery sql;

    sql.prepare("insert into jingles values(:jname,:jpath)");
    sql.bindValue(":jname",jname);
    sql.bindValue(":jpath",jpath);

    if(sql.exec())
    {
        qDebug() << "Sql for insert is: " << sql.lastQuery();
        QMessageBox::information(this,tr("Save"),tr("Jingle Added!"));
        adb.close();
        this->hide();

    } else {
        QMessageBox::critical(this,tr("Error"),sql.lastError().text());
        qDebug() << "Sql for insert is: " << sql.lastQuery();
    }

}

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 RBDtoWeatherDialog::processDay(QDateTime date)
{
    QDateTime dates;
    dates.setDate(date.date());
    if(date.time() > QTime(21, 0)){
        dates = dates.addDays(1);
        dates.setTime(QTime(0, 0));
    }else{
        if(date.time() <= QTime(3, 0)) dates.setTime(QTime(0, 0));
        if(date.time() > QTime(3, 0) && date.time() <= QTime(9, 0)) dates.setTime(QTime(6, 0));
        if(date.time() > QTime(9, 0) && date.time() <= QTime(15, 0)) dates.setTime(QTime(12, 0));
        if(date.time() > QTime(15, 0) && date.time() <= QTime(21, 0)) dates.setTime(QTime(18, 0));
    };

    QSqlQuery insert;
    QSqlQuery select;

    select.exec(QString("SELECT * FROM tiempos WHERE fecha = '%1'").arg(dates.toString("yyyy-MM-dd hh:mm:ss")));
    qDebug() << select.lastQuery() << select.lastError();
    if(select.next()){
        insert.exec(QString("UPDATE estadotiempos SET maxima = %1, minima = %2, vientovel = %3, direccionviento = %4"
                            ", precipitacion = %5, mil500 = %6 WHERE fecha LIKE '%7'")
                    .arg(select.record().field("maxima").value().toDouble())
                    .arg(select.record().field("minima").value().toDouble())
                    .arg(select.record().field("vientovel").value().toDouble())
                    .arg(select.record().field("direccionviento").value().toDouble())
                    .arg(select.record().field("precipitacion").value().toInt())
                    .arg(select.record().field("mil500").value().toInt())
                    .arg(date.toString("yyyy-MM-dd hh:mm:ss"))
                    );
        qDebug() << insert.lastQuery() << insert.lastError();
    };
}

QList<NutshMetaData> NutshSqlSaver::getMetaDatas(const QString& listName) {

    //retourne la liste de métadonnée selon une requete

        QList<NutshMetaData> metaList;
        QVariantList cache;

        QSqlQuery requete;
    if(listName != QString()){
        if(!requete.exec(QString("SELECT * FROM bibliotheque INNER JOIN relationships ON relationships.music_id = bibliotheque.id WHERE (relationships.playlist_id = %1)").arg(crypt(listName)))) {
            qDebug() << requete.lastError() << requete.lastQuery();
        }
    } else {
        if(!requete.exec(QString("SELECT * FROM bibliotheque"))) {
            qDebug() << requete.lastError() << requete.lastQuery();
        }
    }

   while(requete.next()) {
       for(int i = 0;i<NB_CHAMPS_DATABASE;i++) {

           cache.append(requete.value(i));
       }
       metaList.append(NutshMetaData(cache));
       cache.clear();
   }

   return metaList;
}

/*!
 * \brief MItemPlanCuota::agregarAdelanto
 *  Genera un adelanto de cuotas según el importe ingresado.
 *  Esto implica: cargar todos los items de cuota, buscar cuales deben ser eliminados segun el monto y cambiar el monto de la ultima cuota según el resto.
 * \param id_plan_cuota Identificador de plan de cuota emitido
 * \param monto Monto a adelantar
 * \return Verdadero si se pudo realizar el registro
 */
bool MItemPlanCuota::agregarAdelanto(const int id_plan_cuota, double monto)
{
    // Busco todos los identificadores de cuotas en orden inverso.
    QSqlQuery cola;
    if( !cola.exec( QString( "SELECT id_item_cuota, monto FROM item_cuota WHERE id_plan_cuota = %1"
                             "   AND fecha_pago IS NULL "
                             "   AND id_recibo  IS NULL "
                             " ORDER BY fecha_pago DESC ").arg( id_plan_cuota ) ) ) {
        qDebug() << "Error al ejecutar la cola de obtención de datos de planes de cuotas";
        qDebug() << cola.lastError().text();
        qDebug() << cola.lastQuery();
        return false;
    }
    QVector<int> ids;
    QVector<double> montos;
    while( cola.next() ) {
        ids.append( cola.record().value(0).toInt() );
        montos.append( cola.record().value(0).toDouble() );
    }
    if( ids.size() <= 0 || montos.size() <= 0 ) {
        qDebug() << "Error al cargar los datos - ids.size() <= 0 || montos.size() <= 0";
        return false;
    }
    // Veo hasta que ID tengo que eliminar
    int cantidad = 0;
    double temp = monto;
    for( int i = ids.size()-1; i >= 0; i-- ) {
        if( temp > montos.at( i ) ) {
            temp -= montos.at( i );
            cantidad++;
        }
    }
    // Elimino los ultimos "cantidad" items de cuota
    QStringList a_eliminar;
    for( int i=0; i<cantidad; i++ ) {
        int id = ids.at( ids.size() - ( i + 1 ) );
        a_eliminar.append( QString::number( id ) );
    }
    if( !cola.exec( QString( "DELETE FROM item_cuota WHERE id_plan_cuota = %1 AND id_item_cuota IN ( %2 ) " ).arg( id_plan_cuota ).arg( a_eliminar.join( "," ) ) ) ) {
        qDebug() << "Error al ejecutar la cola de eliminación de items de cuotas";
        qDebug() << cola.lastError().text();
        qDebug() << cola.lastQuery();
        return false;
    }
    // Busco cuanto tengo que cambiar de la ultima cuota
    // La variable temporal posee lo que falta restar a la ultima cuota
    int id_cuota_modificar = ids.at( ids.size() - cantidad );
    double nuevo_monto = montos.at( ids.size() - cantidad ) - temp;
    // Actualizo el valor de la cuota
    if( !cola.exec( QString( "UPDATE item_cuota SET monto = %1"
                             " WHERE id_item_cuota = %2 " ).arg( nuevo_monto ).arg( id_cuota_modificar ) ) ) {
        qDebug() << "Error al ejecutar la cola de modificacion de precio del item de cuota";
        qDebug() << cola.lastError().text();
        qDebug() << cola.lastQuery();
        return false;
    }
    return true;
}

void NutshSqlSaver::destroy(NutshMetaData data) {
    QSqlQuery requete;
    if(!requete.exec(QString("DELETE FROM bibliotheque WHERE id = %1").arg(data.getId()))) {

        qDebug() << requete.lastError() << requete.lastQuery();
    }

    if(!requete.exec(QString("DELETE FROM relationships WHERE music_id = %1").arg(data.getId()))) {

        qDebug() << requete.lastError() << requete.lastQuery();
    }
}

void MainWindow::undoLastCommand()
{
	// make sure that the stack isn't empty
	if( undoStack.isEmpty() ) return;
	
	// get the most recently executed command
	Command entry = undoStack.pop();
	QSqlQuery query;
	
	/*addToLog( QString("Command retrieved: %1, %2, %3")
	         .arg(entry.type).arg(entry.odometer).arg(entry.state) );*/
	
	// remove the entry from given table at given odometer reading
	if(entry.type=="Crossings") query.prepare("DELETE FROM Crossings WHERE Odometer= :odo");
	else query.prepare("DELETE FROM Refuels WHERE Odometer= :odo");
	query.bindValue( ":odo", entry.odometer );
	if( !query.exec() ){
		addToLog( query.lastQuery() );
		addToLog( QString("Error %1: %2").arg(query.lastError().number()).arg(query.lastError().text()));
		return;
	} else{
		addToLog( QString("Event at odometer %1 was removed from the set of %2.")
		         .arg(entry.odometer).arg(entry.type) );
	}
	query.clear();
	
	// update the states fields with the new state of the tables
	query.prepare("UPDATE States SET "
               "NumRefuels= (SELECT COUNT(*) FROM Refuels WHERE State=:state1), "
               "GasAmt= (SELECT SUM(Volume) FROM Refuels WHERE State=:state2), "
               "PurchaseAmt= (SELECT SUM(Cost) FROM Refuels WHERE State=:state3), "
               "Mileage= (SELECT SUM(Distance) FROM Crossings WHERE Origin=:state4), "
               "NumEntries= (SELECT COUNT(*) FROM Crossings WHERE Origin=:state5 AND Distance<>0) "
               "WHERE Abbrev=:state6;");
	query.bindValue(":state1", entry.state);
	query.bindValue(":state2", entry.state);
	query.bindValue(":state3", entry.state);
	query.bindValue(":state4", entry.state);
	query.bindValue(":state5", entry.state);
	query.bindValue(":state6", entry.state);
	
	if( !query.exec() ){
		addToLog( query.lastQuery() );
		addToLog( QString("Error %1: %2").arg(query.lastError().number()).arg(query.lastError().text()));
		return;
	}
	
	// tell the gui to update the tables
	if( undoStack.isEmpty() ) ui->action_Undo->setEnabled(false);
	emit dataUpdated();
}

void NutshSqlSaver::remove(const QString &listName) {

    QSqlQuery requete;

    if(!requete.exec(QString("DELETE FROM listeDeLecture WHERE name = \"%1\"").arg(NutshSqlSaver::sqlStringFormat(listName)))) {

        qDebug() << requete.lastError() << requete.lastQuery();
    }
    if(!requete.exec(QString("DELETE FROM relationships WHERE playlist_id = %1").arg(crypt(listName)))) {

        qDebug() << requete.lastError() << requete.lastQuery();
    }

}

static void showError( QSqlQuery& query )
{
    const QString txt =
        i18n("<p>There was an error while executing the SQL backend command. "
             "The error is likely due to a broken database file.</p>"
             "<p>To fix this problem run Maintenance->Recreate Exif Search database.</p>"
             "<hr/>"
             "<p>For debugging: the command that was attempted to be executed was:<br/>%1</p>"
             "<p>The error message obtained was:<br/>%2</p>",
             query.lastQuery(), query.lastError().text() );

    KMessageBox::information( MainWindow::Window::theMainWindow(), txt, i18n("Error Executing Exif Command"), QString::fromLatin1( "sql_error_in_exif_DB" )
        );

    qWarning( "Error running query: %s\nError was: %s", qPrintable(query.lastQuery()), qPrintable(query.lastError().text()));
}

// 操作worktime表
// 插入数据
bool worktimeInsert(int empId, const char* time, const char* photo)
{
    Log log(__LOGARG__,1);
	QSqlQuery query;
	query.exec("INSERT INTO work_time (emp_id, check_time, check_photo) VALUES(?,?,?)");
	QVariantList emp_id;
	emp_id << empId;
	query.addBindValue(emp_id);
	QVariantList check_time;
	check_time << time;
	query.addBindValue(check_time);
	QVariantList check_photo;
	check_photo << photo;
	query.addBindValue(check_photo);
	try
    {
        if (!query.execBatch())
        {
            log << ((query.lastQuery()).toUtf8()).constData() << Log::endl;
            log << (((query.lastError()).text()).toUtf8()).constData() << Log::endl;
            return false;
        }
    }
    catch(...)
    {
        log << "Add New Node error! Unable to add a new Node!" << Log::endl;
    }
    return true;
}

// 修改empId
bool worktimeAltEmpId(int worktimeId, int empId)
{
    Log log(__LOGARG__,1);
	QSqlQuery query;
	query.exec("UPDATE work_time SET emp_id=? WHERE work_time_id=?");
	QVariantList emp_id;
	emp_id << empId;
	query.addBindValue(emp_id);
	QVariantList work_time_id;
	work_time_id << worktimeId;
	query.addBindValue(work_time_id);
	try
    {
        if (!query.execBatch())
        {
            log << ((query.lastQuery()).toUtf8()).constData() << Log::endl;
            log << (((query.lastError()).text()).toUtf8()).constData() << Log::endl;
            return false;
        }
    }
    catch(...)
    {
        log << "Add New Node error! Unable to add a new Node!" << Log::endl;
    }
    return true;
}

/*!
*   \brief update a obj from the table behavioreventpacket based in the ID.
*   \note  Object must contains the ID that will be used to update the row.
*   \param obj a pointer to the object with of the obj that contians the new information
*   \return an bool with the result of the operation
*   \retval true if sucess
*   \retval false if fail
*/
bool BehaviorEventPacketDAO::update(BehaviorEventPacketObject * obj) {
        if(!db->isOpen())
            db->open();

        QSqlQuery query;
        bool ret = query.exec(QString("update behavioreventpacket set idtask = %1, port = %2, idpacket = %3, timeserver = %4, timesec = %5, timeusec = %6,  pinscontext = %7,  pinevent = %8,  pineventlabel = '%9' WHERE id = %10")
                              .arg(obj->getIDTask())
                              .arg(obj->getPort())
                              .arg(obj->getIDPacket())
                              .arg(obj->getTimeServer())
                              .arg(obj->getTimeSec())
                              .arg(obj->getTimeUSec())
                              .arg(obj->getPin())
                              .arg(obj->getTypeEvent())
                              .arg(obj->getTypePin())
                              .arg(obj->getPinContext())
                              .arg(obj->getID()));
        if (!ret)
        {
            qCritical() << query.lastQuery();
            qCritical() << query.lastError();
            qCritical() << db->lastError();
        }

    db->close();
    return ret;
}

// 操作model_img表
bool modelImgInsert(int empId, AutoType& photo)
{
    Log log(__LOGARG__,1);
	QSqlQuery query;
	query.exec("INSERT INTO model_img (emp_id, emp_photo) VALUES(?,?)");
	QVariantList emp_id;
	emp_id << empId;
	query.addBindValue(emp_id);
	QVariantList emp_photo;
    QByteArray data(photo.getStr(),photo.getLen());
	emp_photo << data;
	query.addBindValue(emp_photo);
	try
    {
        if (!query.execBatch())
        {
            log << ((query.lastQuery()).toUtf8()).constData() << Log::endl;
            log << (((query.lastError()).text()).toUtf8()).constData() << Log::endl;
            return false;
        }
    }
    catch(...)
    {
        log << "Add New Node error! Unable to add a new Node!" << Log::endl;
    }
    return true;
}

bool object_e_tipo_usuario::mf_add()
{
	//function mf_add
	//w!

	QSqlQuery query;

	query.prepare("INSERT INTO e_tipo_usuario(descripcion, persmisos_default) VALUES(?, ?)");
	query.bindValue(0, md_o_descripcion);
	query.bindValue(1, md_o_persmisos_default);

	if(query.exec())
	{
		//state OK
		//w!
        //qDebug()<<query.lastQuery()<<endl;

		return true;

	}else{
		//state FAILED
		//w!
        qDebug()<<query.lastQuery()<<endl;
        qDebug()<<query.lastError().databaseText()<<endl;
		return false;
	}
}

/*!
*   \brief insert a new obj into the table behavioreventpacket
*   \param obj a pointer to the object with of the new obj
*   \return an integer id of new row
*   \retval -1 if fail.
*/
int BehaviorEventPacketDAO::insert(BehaviorEventPacketObject * obj) {
    if(!db->isOpen())
        db->open();

        QSqlQuery query;
        bool ret = query.exec(QString("insert into behavioreventpacket values ( NULL, %1, %2, %3, datetime('%4', 'unixepoch', 'localtime'), %5, %6, %7, %8, %9, '%10')")
                              .arg(obj->getIDTask())
                              .arg(obj->getPort())
                              .arg(obj->getIDPacket())
                              .arg(obj->getTimeServer())
                              .arg(obj->getTimeSec())
                              .arg(obj->getTimeUSec())
                              .arg(obj->getPin())
                              .arg(obj->getTypeEvent())
                              .arg(obj->getTypePin())
                              .arg(obj->getPinContext()));
        int newId = -1;
        // Get database given autoincrement value
        if (ret)
        {
            newId = query.lastInsertId().toInt();
        }else{
            qCritical() << query.lastQuery();
            qCritical() << query.lastError();
            qCritical() << db->lastError();
        }

    db->close();
    return newId;
}