I tend to always forget how to use prepared statements, so I generated this little helper function that always does what I typically want. (untested 2023-09-04)
#include <QSqlQuery>
#include <QSqlError>
#include <QVariant>
#include <QMap>
#include <QDebug>
#include <QSqlDatabase>
/**
* Executes an SQL query with placeholders.
*
* @param sqlString The SQL query string with placeholders.
* @param placeholderMap A map containing placeholder strings as keys and their corresponding QVariant values.
* @param db Optional QSqlDatabase object to specify which database connection to use.
* @return True if the query was successful, false otherwise.
*/
bool executeQueryWithPlaceholders(const QString& sqlString,
const QMap<QString, QVariant>& placeholderMap,
const QSqlDatabase& db = QSqlDatabase::database())
{
// Create a QSqlQuery object using the optional QSqlDatabase object
QSqlQuery query(db);
// Prepare the SQL query with the query string
// If preparation fails, log the error and return false
if (!query.prepare(sqlString)) {
qDebug() << "Error preparing query:" << query.lastError().text();
return false;
}
// Loop through the placeholder map to bind each value to its respective placeholder in the query
for (auto it = placeholderMap.constBegin(); it != placeholderMap.constEnd(); ++it) {
query.bindValue(it.key(), it.value());
}
// Execute the query
// If execution fails, log the error and return false
if (!query.exec()) {
qDebug() << "Error executing query:" << query.lastError().text();
return false;
}
// If the code reaches this point, the query was successfully prepared and executed
return true;
}
// Usage example
int main()
{
// Initialize the database connection (not shown here, please make sure to do this)
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("mydatabase.db");
if (!db.open()) {
qDebug() << "Failed to open the database";
return -1;
}
// Define SQL string and placeholders
QString sqlString = "SELECT * FROM myTable WHERE column1 = :value1 AND column2 = :value2";
QMap<QString, QVariant> placeholderMap;
placeholderMap[":value1"] = QVariant(42);
placeholderMap[":value2"] = QVariant("hello");
// Execute the query and check for success
bool success = executeQueryWithPlaceholders(sqlString, placeholderMap, db);
if (success) {
qDebug() << "Query executed successfully";
} else {
qDebug() << "Query execution failed";
}
return 0;
}