How to get last prepared and executed query using QsqlQuery ?

Question

I'm doing an insert:

QSqlQuery myQuery(db);
myQuery.prepare("INSERT INTO mytable VALUES (:val1, :val2)");
myQuery.bindValue(":val1", 1);
myQuery.bindValue(":val2", 2);
myQuery.exec();

Then I need to get the executed SQL query for logging purposes.

myQuery.executedQuery() returns "INSERT INTO mytable VALUES (?, ?)".

How do I to get executed query with the actual binded values that were used?

Answer 1

Ideal solution would be patching Qt (since 2011 it's not changed in Qt 5 nor Qt 6):

The method:

QString QSqlQuery::executedQuery() const

should call (in implementation for SQLite) function:

char *sqlite3_expanded_sql(sqlite3_stmt *pStmt);

We can choose named or positioned variant based on value of

QSqlResult::BindingSyntax QSqlResult::bindingSyntax() const

but it's [protected] - available only for DB backend creators.

Internally, in QSqlResult binding syntax changed to 'positioned' in case of calling one of two methods:

void    addBindValue(const QVariant &val, QSql::ParamType paramType = QSql::In)
void    bindValue(int pos, const QVariant &val, QSql::ParamType paramType = QSql::In)

So we can create a subclass of QSqlQuery, override those methods and keep track of bind syntax. Also can override executedQuery() to choose appropriate formatting function.

With current QT, we can manually call one of two functions, because we know which type of binding we used in our code:

QString formatNamedSqlQuery(const QSqlQuery *query)
{
    QString qstr = query->executedQuery();
    QMap<QString, QVariant> vals = query->boundValues();
    QMap<QString, QVariant>::const_iterator i;

    for(i = vals.constBegin() ; i != vals.constEnd() ; ++i) {
        const QVariant &var = i.value();
        QSqlField field(QLatin1String(""), var.type());

        if (var.isNull()) {
            field.clear();
        } else {
            field.setValue(var);
        }
        QString formatV = query->driver()->formatValue(field);
        if (formatV.size() > 100)
            formatV = formatV.left(100);

        qstr.replace(i.key(), formatV);
    }

    return qstr;
}

QString formatPositionedSqlQuery(const QSqlQuery *query)
{
    QString qstr = query->lastQuery();
    QList<QVariant> list = query->boundValues().values();
    QString val;

    int i = 0;
    int idx = 0;
    for (idx = 0; idx < list.count(); ++idx) {
        i = qstr.indexOf(QLatin1Char('?'), i);
        if (i == -1)
            continue;
        QVariant var = list.value(idx);
        QSqlField f(QLatin1String(""), QVariant::Type(var.userType()));
        if (var.isNull())
            f.clear();
        else
            f.setValue(var);
        val = query->driver()->formatValue(f);
        qstr = qstr.replace(i, 1, val);
        i += val.length();
    }

    return qstr;
}

Answer 2

If the database user has "SUPER" rights, the logging can be set during runtime. I found some inspiration for this answer in this post: How to show the last queries executed on MySQL?

Add the following code in front of the prepare statement:

QSqlQuery query("SET GLOBAL log_output = 'TABLE'");
query.exec("SET GLOBAL general_log = 'ON'");

Add the following code after the prepare, bindValue and exec statements:

query.exec("SET GLOBAL general_log = 0");

The executed queries are stored in the table "general_log" of the database "mysql". The "general_log" table will show the prepared without variables as well as the queries with the filled in variables. I didn't try it out, but it might be possible to set the MySQL session variable "sql_log_off" and than the user don't need "SUPER" rights. See MySQL documentation.

It only works with MySQL >= 5.1.12.

Answer 3

You have to iterate over the elements in reverse order to get the right result.

Example:
Query: " :a :aa " 
query.bindValue(":a",1);
query.bindValue(":aa",1);
getLastExecutedQuery will return: "1 1a"

Fixed solution #1 (lightstep)

QString getLastExecutedQuery(const QSqlQuery& query)
{
    QString str = query.lastQuery();
    QMapIterator<QString, QVariant> it(query.boundValues());

    it.toBack();

    while (it.hasPrevious())
    {
        it.previous();
        str.replace(it.key(),it.value().toString());
    }
    return str;
}

Answer 4

A better function (inspired by the Qt source code : http://qt.gitorious.org/qt/qt/blobs/4.7/src/sql/kernel/qsqlresult.cpp#line644).

This function should handle almost all cases : This code does not work with Oracle DB when using Name Binding (This is the only DB that natively support Name Binding => executedQuery() do not return the query with '?' but the original query...)

To be able to support native support Name Binding of DB, the keys of bound values must be sorted by length, then loop over the sorted map...

QString getLastExecutedQuery(const QSqlQuery& query)
{
    QString sql = query.executedQuery();
    const int nbBindValues = query.boundValues().size();

    for(int i = 0, j = 0; j < nbBindValues; ++j)
    {
        i = sql.indexOf(QLatin1Char('?'), i);
        if (i <= 0)
        {
            break;
        }
        const QVariant &var = query.boundValue(j);
        QSqlField field(QLatin1String(""), var.type());
        if (var.isNull())
        {
            field.clear();
        }
        else
        {
            field.setValue(var);
        }
        QString formatV = query.driver()->formatValue(field);
        sql.replace(i, 1, formatV);
        i += formatV.length();
    }

    return sql;
}

Edit: I found a bug in the previous function, if a '?' exists inside a quoted string, the '?' is replaced by the next available value. The bug already exists in Qt source code. This function should fix this problem (Could be improved a lot, but the idea is there)

QString getLastExecutedQuery(const QSqlQuery& query)
{
    QString sql = query.executedQuery();
    int nbBindValues = query.boundValues().size();

    for(int i = 0, j = 0; j < nbBindValues;)
    {
        int s = sql.indexOf(QLatin1Char('\''), i);
        i = sql.indexOf(QLatin1Char('?'), i);
        if (i < 1)
        {
            break;
        }

        if(s < i && s > 0)
        {
            i = sql.indexOf(QLatin1Char('\''), s + 1) + 1;
            if(i < 2)
            {
                break;
            }
        }
        else
        {
            const QVariant &var = query.boundValue(j);
            QSqlField field(QLatin1String(""), var.type());
            if (var.isNull())
            {
                field.clear();
            }
            else
            {
                field.setValue(var);
            }
            QString formatV = query.driver()->formatValue(field);
            sql.replace(i, 1, formatV);
            i += formatV.length();
            ++j;
        }
    }

    return sql;
}

Answer 5

An alternative to what lightstep suggested, is to prepare query strings and then call a function that first writes the query to the log and only then calls real execute(). I personally use QString::arg() and "%number" for arguments to make a query string instead of bindValue().

Let's sum things up:

Solution #1 (lightstep)

I came up with this workaround:

QString getLastExecutedQuery(const QSqlQuery& query)
{
 QString str = query.lastQuery();
 QMapIterator<QString, QVariant> it(query.boundValues());
 while (it.hasNext())
 {
  it.next();
  str.replace(it.key(),it.value().toString());
 }
 return str;
}

Solution #2 (me):

// my helper function

#define SQLDB_SHOW_QUERIES
#define SQLDB_LOG_QUERIES
#define SQLDB_LOG_FILENAME "sqlite.db.log"

bool executeQuery(QSqlQuery& queryObject, const QString& query)
{
 bool result = true;;
#ifdef SQLDB_SHOW_QUERIES
 std::cout<<query.toStdString()<<std::endl;
#endif
#ifdef SQLDB_LOG_QUERIES
 std::fstream fs_log;
 fs_log.open(SQLDB_LOG_FILENAME,std::ios::out|std::ios::app);
 if (fs_log.is_open())
 {
  fs_log<<query.toUtf8().data()<<std::endl;
 }
#endif
 result &= queryObject.exec(query);
#ifdef SQLDB_SHOW_QUERIES
 if (!result) std::cout<<queryObject.lastError().text().toStdString()<<std::endl;
 std::cout<<std::endl;
#endif
#ifdef SQLDB_LOG_QUERIES
 if (fs_log.is_open())
 {
  if (!result) fs_log<<queryObject.lastError().text().toUtf8().data()<<std::endl;
  fs_log<<std::endl;
  fs_log.close();
 }
#endif
 return result;
}

// your sample code

QSqlQuery myQuery(db);
QString query = QString("INSERT INTO mytable VALUES (%1,%2)")
 .arg(1).arg(2);
executeQuery(myQuery,query);