I'm currently facing an issue using QtSQL along with an MS Access database, using the QOBDC driver.
SELECT and INSERT statement work, and read/insert data properly.
UPDATE/DELETE statements are correctly processed (according to QSqlQuery and the OBDC tracer), but in MS Access, the data is not edited.
I'm currently using '?' bindings for the request, but replacing these bindings with hardcoded data didn't work either.
The OBDC tracer sees the UPDATE request, as well as the 2 bindings.
Is there a reason why these UPDATE statements do not modify MS Access' data ?
Database
db = QSqlDatabase::addDatabase("QODBC");
db.setDatabaseName(QString("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=") + file);
DELETE function
bool MSAccessReader::deleteRecord(QString table, QString where)
{
QString req;
req = QString("DELETE FROM ") + table + QString(" WHERE ") + where;
QSqlQuery query;
bool ok = query.exec(req);
return ok;
}
UPDATE function
bool MSAccessReader::update(QList<QString> headers, QList<QVariant> data, QString where, QString table)
{
if(headers.isEmpty() || data.isEmpty())
{
qDebug() << "Headers or data are empty";
return false;
}
else if(headers.size() != data.size())
{
qDebug() << "Number of headers and data fields mismatches";
return false;
}
else if(table.isEmpty())
{
qDebug() << "Table isn't specified";
return false;
}
else if(where.isEmpty())
{
qDebug() << "WHERE isn't specified";
return false;
}
QSqlQuery query;
QString req;
req = QString("UPDATE ") + table + QString(" SET ");
foreach(QString header, headers)
{
req = req + header + "=?,";
}
req = req.left(req.size()-1);
/*WHERE*/
req = req + QString(" WHERE ") + where + QString(";");
query.prepare(req);
foreach(QVariant var,data)
{
query.addBindValue(var);
}
qDebug() << query.lastQuery() << query.boundValues();
bool ok = query.exec();
return ok;
}
Request String and Data
"UPDATE Table1 SET Nom=?,Prenom=? WHERE ID=1;" QMap((":a", QVariant(QString, "Jean"))(":bb", QVariant(QString, "Marc")))
Data table
Found the problem.
I needed to exectue a transaction() and a commit() around the query :
db.transaction();
bool ok = query.exec();
db.commit();