[SOLVED] How can I replace record in my QSqlDatabase ?

[Anticross]

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.

[Code_ReaQtor]

Use "UPDATE":http://www.w3schools.com/sql/sql_update.asp statement.

[Anticross]

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"

[Code_ReaQtor]

[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

[Anticross]

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;
}@

[Code_ReaQtor]

[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!

[Anticross]

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.