loading data from database using another thread

Christian Ehrlicher

@Proton-Phoenix said in loading data from database using another thread:

i tried everything ..

And what exactly? There are numerous topics around threading and sql database access...

Proton Phoenix

@Christian-Ehrlicher
Hi Bro ~
i tried QTConcurrent and QThread ...
.

Christian Ehrlicher

@Proton-Phoenix said in loading data from database using another thread:

i tried QTConcurrent and QThread ...

And what did not work? Please read e.g. https://doc.qt.io/qt-5/thread-basics.html before starting.

Proton Phoenix

@Christian-Ehrlicher
for example this

void benefits::on_loaddatabasebutton_5_clicked()
{
    monthly=ui->monthdateedit->text();
 benefits *ben=new benefits();
 qInfo()<< this<<"Working"<<QString::number(1)<<QThread::currentThread();
 QThread thread;
 thread.setObjectName("Worker Thread");
 ben->moveToThread(&thread);
 QObject::connect(&thread,SIGNAL(&QThread::started),ben,SLOT(&data));
 thread.start();

}
void benefits::data(){
counters = 0;
QSqlQuery qry(db);
    qry.prepare("SELECT * FROM fac WHERE month= '"+ui->monthdateedit->text()+"'");
    if(qry.exec("SELECT * FROM fac WHERE month= '"+ui->monthdateedit->text()+"'")){
        qDebug()<<"yep works";
        while(qry.next()){
            counters +=qry.value(9).toFloat();
                }
            }else{
       // ui->statusfacture->setText(qry.lastError().text());
            }  
                     if(counters==NULL){
                         ui->benefitmonthtextedit->setText(QString(""));
                     }else{
                             ui->benefitmonthtextedit->setText(QString("you have  %1 as benefits this month ").append("<font color='red'>dollars</font><font color='green'> Canadian</font>").arg(counters));
                 }
}

here it says you can't run Widgets on another thread ...
i understand that but i didn't find in the internet how to load just data on another thread
i will try to search more on the basics
Thank you so much bro for help

KroMignon

@Proton-Phoenix said in loading data from database using another thread:

it works fast as i want ..
but the problem is i don't know how to make it work on another thread may be someday it needs to calculate 10000 rows per month .. so it will freeze The Main Thread and GUI... i tried everything ..
can someone help me on this?

Your code looks a little bit confusing:

why do you do qry.prepare(...) and then qry.exec(...) with the same string? you can remove qry.prepare
using qry.value(9).toFloat() with SELECT * FROM ... is not really appropriated. I recommend you to change this to ensure you will get the value you want.
and if(counters==NULL) should be if(counters==0) or if(!counters)

I would change this to:

void benefits::on_monthvbutton_clicked()
{
    counters = 0;
    QSqlQuery qry(db);
    qry.prepare("SELECT * FROM fac WHERE month=:data";
    qry.bindValue(":data", ui->monthdateedit->text());
    if(qry.exec())
    {
        qDebug()<<"yep works";
        int colCounter = query.record().indexOf("counter"); // I suppose column name is counter
    
        while(qry.next()){
            counters +=qry.value(colCounter).toFloat();
        }
    }else{
       //ui->statusfacture->setText(qry.lastError().text());
    }  
    if(counters==0){
        ui->benefitmonthtextedit->setText(QString(""));
    }else{
        ui->benefitmonthtextedit->setText(QString("you have %1 as benefits this month <font color='red'>dollars</font><font color='green'> Canadian</font>").arg(counters));
    }
}

KroMignon

@Proton-Phoenix said in loading data from database using another thread:

here it says you can't run Widgets on another thread ...
i understand that but i didn't find in the internet how to load just data on another thread
i will try to search more on the basics
Thank you so much bro for help

The easiest way is to use QtConcurrent::run() in combination with QFutureWatcher:

For example:

void benefits::on_monthvbutton_clicked()
{
    QSqlQuery qry(db);
    qry.prepare("SELECT * FROM fac WHERE month=:data";
    query.bindValue(":data", ui->monthdateedit->text());
    if(qry.exec())
    {
        qDebug()<<"yep works";
        
        // watcher to wait until future is done
        auto watcher = new QFutureWatcher<float>();
        // ==> delete QFuteWatcher instance instance when process done
        connect(watcher, &QFutureWatcher<float>::finished, watcher, [watcher, this]() {
            qDebug() << "Processing done.";
            float counters = watcher->result();
            watcher->deleteLater();
            if(!counters){
                ui->benefitmonthtextedit->setText(QString(""));
            }else{
                ui->benefitmonthtextedit->setText(QString("you have %1 as benefits this month <font color='red'>dollars</font><font color='green'> Canadian</font>").arg(counters));
            }
        });
        
        // start processing in another thread
        QFuture<float> future = QtConcurrent::run([qry]() ->float {
            // Code in this block will run in another thread
            qDebug() << "Processing in another thread";
            float counters = 0;
            int colCounter = query.record().indexOf("counter"); // I suppose column name is counter
    
            while(qry.next()){
                counters +=qry.value(colCounter).toFloat();
            }
            return counters; 
        });
        
        watcher->setFuture(future);
        
        
    }else{
       //ui->statusfacture->setText(qry.lastError().text());
    }  
}

Proton Phoenix

@KroMignon
Thank you so much bro
i was using qry.exec() due to postgresql
when i used QSqlite i used only prepare with binds ..
but when i moved to postgresql it doesn't work at all with prepare and binds
only on qry.exec()
about counters +=qry.value(9).toFloat();

every row has a double value the benefits of a payment ...
so it get's all the rows which contain the same month ... and calculate the full benefits result

...

Proton Phoenix

Thank you so much all of you guys you helped me very well
now problem solved <3
Really Thank you so much

Christian Ehrlicher

@KroMignon : you must not move access a QSqlDatabase from a different thread!

KroMignon

@Christian-Ehrlicher said in loading data from database using another thread:

you must not move access a QSqlDatabase from a different thread!

Yes I know, but this is only the result of the request, there are no SQL statement which will be handled. Only the result of the old request.
What's wrong with this?

Proton Phoenix

@KroMignon
hi bro again
is there any solution
with const lambda

while(qry.next()){  // error here
                  counters +=qry.value(colCounter).toFloat();
              }

it says this error !
'this' argument to member function 'next' has type 'const QSqlQuery', but function is not marked const
qsqlquery.h:92:10: note: 'next' declared here

KroMignon

@Proton-Phoenix said in loading data from database using another thread:

is there any solution
with const lambda

No, you cannot use QSqlQuery::next(), with a const instance.

Proton Phoenix

thank you bro
when i add mutable problem Disappeared .. and it loads data from another thread successfully Finally :D

Really Thank you guys

QFuture<float> future = QtConcurrent::run([qry]()mutable ->float {

Christian Ehrlicher

@KroMignon said in loading data from database using another thread:

Yes I know, but this is only the result of the request, there are no SQL statement which will be handled. Only the result of the old request.
What's wrong with this?

QSqlQuery does not have the data, it only fetches the data when you call e.g. next() so it still uses the database connection.

KroMignon

@Christian-Ehrlicher said in loading data from database using another thread:

QSqlQuery does not have the data, it only fetches the data when you call e.g. next() so it still uses the database connection.

Indeed, that's sound bad :(
I misunderstood how QSqlQuery works, I believe it launch the request and store the result locally.

Christian Ehrlicher

@KroMignon said in loading data from database using another thread:

I believe it launch the request and store the result locally.

This would be a very bad behavior for large result sets.

KroMignon

@Christian-Ehrlicher said in loading data from database using another thread:

This would be a very bad behavior for large result sets.

You are right.
This will made this a little bit more complex, because the SQL connection have to leave in another thread, which means a new DB connection is required.

Christian Ehrlicher

@KroMignon Or do only the calculation in the other thread.

Proton Phoenix

@Christian-Ehrlicher
do i need to change anything???

Proton Phoenix

@Christian-Ehrlicher and @KroMignon
i did it successfully Thanks you so much your opinions helped me to fix the Big O problem too
now it's only calculation with the other thread
<3