Qt: save the result of the SQL query in a variable, use the C ++ variable in the SQL query

Question

My project is to program a simple ShopApp. One of the functions is to check if there is a sufficient stock of stocks so that the client can buy the desired amount of what he wants to buy. The functions look like this: ( cart - std::vector<product> , and Pid stands for product identifier):

 bool sqlfunctions::checkStock(){ QSqlQuery query; int diff, stock; for(iter cursor = cart.begin();cursor!=cart.end();cursor++){ query.prepare("SELECT stock FROM products WHERE id = cursor->getPid()"); query.exec(); // Need to save result of query into variable stock stock = ??; diff = stock - cursor->getAmount; if(diff < 0){ return false; } } return true; } 

Obviously, this function does not work because cursor-> getPid () is not executed because it is a string. So the question here is: how to insert a C ++ variable in sql-query ? In regular C ++, I would use some swprintf_s function. So, a good idea?

Secondly, since both query.exec() and query.prepare() are booleans that return true or false based on success , how can I store the results of queries in a C ++ variable?

Note that I am new to SQL and SQL with Qt. I am using QT5. I already tried to read the documentation about the QSqlQuery class and its functions, BindValue() and addBindValue() seems interesting. However, I really do not understand how they work.

Edit

So now I have a minimal example that doesn't work yet, despite the accepted answer. However, the compiler does not give me any warnings or errors:

 void MainWindow::on_ButtonSQL_clicked() { QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL"); db.setHostName("..."); db.setDatabaseName("..."); db.setUserName("..."); db.setPassword("..."); db.setPort(3306); db.open(); QMessageBox msgBox; if (db.open()) { msgBox.setText("It works :)"); msgBox.exec(); } else { msgBox.setText("No connection."); msgBox.exec(); } QSqlQuery query(db); // This query worked! query.exec("INSERT INTO users (id, username, balance) VALUES(25, 'someName', 10000)"); // initialize someNumber to check later, whether it was processed correctly. int id = 2, someNumber = 20; query.prepare("SELECT stock FROM products WHERE id = :myid"); query.bindValue(":myid", id); query.exec(); QString idValue = query.boundValue(0).toString(); someNumber = query.value(0).toInt(); msgBox.setText("The stock is: "+QString::number(someNumber)+"\nThe placeholder has the value: "+idValue); msgBox.exec(); } 

Expected msgBox of the last msgBox:

 The stock is: 100 The placeholder value is: 2 

Actually conclusion:

 The stock is: 0 The placeholder value is: 2 

If I try instead to select a row (e.g. productName), say using QString myProductName = query.value(0).toString() (and the corresponding changes in the code), return will be an empty string.

** SOLVED: ** See the comment from Floris in the accepted answer. I missed query.next() .

Answer 1

This is actually pretty straight forward:

 QSqlQuery query; query.prepare("Select stock from products where id = :input"); query.bindValue(":input", cursor->getPid()); query.exec(); 

You bind values ​​to an argument in a string. The arguments are in the format :name . Is there also a positional binding that binds in the order in which it sees?

 QSqlQuery query; query.prepare("Select stock from products where id = ?"); // No need for an identifier query.bindValue(cursor->getPid()); query.exec(); 

To iterate the records received from the query, you can do the following:

 QSqlQuery query; query.prepare("SELECT stock FROM employee WHERE id = ?"); query.bindValue(cursor->getPid()); query.exec(); if (query.next()) { int stock = query.value(0).toInt(); // You could store the information you obtain here in a vector or something } 

You can also put the make statement outside the for loop. If you are interested in repeating multiple entries (from a select statement), you can replace the if statement with a while statement.

Regarding QSqlQuery::next() :

Retrieves the next record as a result, if available, and positions the query in the received record. Note that the result must be in the active state, and isSelect () must return true before calling this function or will do nothing and return false.

As taken from QSqlQuery . You will need to make this call before the record is actually available using .value(int) .