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





  • 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.


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.