[SOLVED] How can I replace record in my QSqlDatabase ?
-
I'm a newbie in db programming. So I need some answer. I wrote some code :
@QList<QSqlRecord> records = GET_SCO_DB()->getRecords(tableName, keyField, keyValue);if (records.count() <= 0)
return false;QSqlRecord record = records.at(0);
for (int i = 0; i < record.count(); i++) {
QSqlField field = record.field(i);
field.setValue(dbProps.value(field.name()));record.replace(i, field);
}@
When @GET_SCO_DB@ is single-tone class instance and it's method getRecords returns me list of record by given parameters: @QSqlQuery query(*this);QString sql;
if (keyValue.isNull()) {
sql = QString("SELECT * FROM %1 WHERE %2 IS NULL")
.arg(table)
.arg(keyField);query.prepare(sql);
} else {
sql = QString("SELECT * FROM %1 WHERE %2 = ?")
.arg(table)
.arg(keyField);query.prepare(sql);
query.addBindValue(keyValue);
}if (false == query.exec()) {
qDebug() << query.lastError().text();
return QList<QSqlRecord>();
}QList<QSqlRecord> descriptions;
while (query.next()) {
QSqlRecord record = query.record();
descriptions.append(record);
}return descriptions;@
So how can I make to my code being executed to db ? As you see from first code that I post i get QSqlRecord and replaced some fields there. Now I need to apply changes to db. -
Use "UPDATE":http://www.w3schools.com/sql/sql_update.asp statement.
-
I write function like this:
@bool SCODatabase::updateRecord(const QString & table, const QString & keyField, const QVariant & keyValue, const sxProperties & newValues) {QSqlQuery query(*this);
QString sql = QString("UPDATE %1 SET ").arg(table);QHashIterator<QString, QVariant> iter(newValues);
int cnt = newValues.count();
while(iter.hasNext()) {
iter.next();
cnt--;QString sValue = iter.value().toString();
if(sValue.isEmpty())
sValue = "NULL";if (cnt <= 0)
sql += QString("%1=%2").arg(iter.key()).arg(sValue);
else
sql += QString("%1=%2, ").arg(iter.key()).arg(sValue);
}sql += QString(" WHERE %1 = ?").arg(keyField);
query.prepare(sql);
query.addBindValue(keyValue);if (false == query.exec(sql)) {
qDebug() << query.lastError().text();
return false;
}return true;
}@
but lastError returns me this: "ERROR: syntax error at end of input
LINE 1: ... SET ZONE_TAREGT_OUT=0, ZONE_TAREGT_IN=0 WHERE ZONE_NAME = ?
^
QPSQL: Unable to create query" -
[quote author="Anticross" date="1358764224"]
but lastError returns me this: "ERROR: syntax error at end of input
LINE 1: ... SET ZONE_TAREGT_OUT=0, ZONE_TAREGT_IN=0 WHERE ZONE_NAME = ?
^
QPSQL: Unable to create query" [/quote]There might be a problem in your sql syntax:
@SET ZONE_TAREGT_OUT=0, ZONE_TAREGT_IN=0 WHERE ZONE_NAME = ?^@what is "?^" anyways?
you might want to take a look a psql "specifics":http://www.ntchosting.com/postgresql/#Database_triggers
-
Damn, I just need to add ' characters. @bool SCODatabase::updateRecord(const QString & table, const QString & keyField, const QVariant & keyValue, const sxProperties & newValues) {
QSqlQuery query(*this);
QString sql = QString("UPDATE %1 SET ").arg(table);QHashIterator<QString, QVariant> iter(newValues);
int cnt = newValues.count();
while(iter.hasNext()) {
iter.next();
cnt--;QString sValue = iter.value().toString();
if(sValue.isEmpty())
sValue = "NULL";if (cnt <= 0)
sql += QString("%1='%2'").arg(iter.key()).arg(sValue);
else
sql += QString("%1='%2', ").arg(iter.key()).arg(sValue);
}sql += QString(" WHERE %1='%2'").arg(keyField).arg(keyValue.toString());
if (false == query.exec(sql)) {
qDebug() << query.lastError().text();
return false;
}return true;
}@ -
[quote author="Anticross" date="1358769615"]Damn, I just need to add ' characters. [/quote]
Actually, that is a MUST for datatypes like texts/strings. Happy coding!
-
And last update with NULL values handling: @bool SCODatabase::updateRecord(const QString & table, const QString & keyField, const QVariant & keyValue, const sxProperties & newValues) {
QSqlQuery query(*this);
QString sql = QString("UPDATE %1 SET ").arg(table);QHashIterator<QString, QVariant> iter(newValues);
int cnt = newValues.count();
while(iter.hasNext()) {
iter.next();
cnt--;QString sValue = iter.value().toString();
if(sValue.isEmpty())
sql += QString("%1=NULL").arg(iter.key());
else
sql += QString("%1='%2'").arg(iter.key()).arg(sValue);if (cnt > 0)
sql += QString(", ");
}sql += QString(" WHERE %1='%2'").arg(keyField).arg(keyValue.toString());
if (false == query.exec(sql)) {
qDebug() << query.lastError().text();
return false;
}return true;
}@ Thanks, Code_ReaQtor, for help.