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?

I came up with this workaround: `QString getLastExecutedQuery(const QSqlQuery& query) { QString str = query.lastQuery(); QMapIterator it(query.boundValues()); while (it.hasNext()) { it.next(); str.replace(it.key(), it.value().toString()); } return str; }` — lightstep, Apr 25 '11 at 11:02
I'm not able to add it as an answer. Oops! Your answer couldn't be submitted because: New users can't answer their own question for 8 hours. Please use comments, or edit your question instead. — lightstep, Apr 25 '11 at 11:50
you can add it later then, and if no better solution is posted you can also accept your own answer. — Nick Dandoulakis, Apr 25 '11 at 12:05
You can also check for a NULL and quote values: `str.replace(it.key(), it.value().isNull() ? "NULL" : "'" + it.value().toString() + "'")` — user182917, May 21 '19 at 18:34

benjarobin · Answer 1 · 2012-05-18T17:53:22.650

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

score 5 · Accepted Answer · edited May 23 '17 at 11:53

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);

This is a terrible idea leaving you open to SQL injection. – blarf Sep 23 '15 at 05:56 — blarf, Sep 23 '15 at 05:56

iabr · Answer 3 · 2016-05-20T09:00:49.360

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

score 1 · Answer 4 · answered Aug 16 '17 at 15:20

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.

How to get last prepared and executed query using QsqlQuery ?

4 Answers4

Linked