[ACCEPTED]-How to get last prepared and executed query using QsqlQuery ?-sqlite

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

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

To 10 be able to support native support Name Binding 9 of DB, the keys of bound values must be 8 sorted by length, then loop over the sorted 7 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 6 function, if a '?' exists inside a quoted 5 string, the '?' is replaced by the next 4 available value. The bug already exists 3 in Qt source code. This function should 2 fix this problem (Could be improved a lot, but 1 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;
}

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

Let's sum things up:

Solution 2 #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 1 #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);

You have to iterate over the elements in 2 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 1 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;
}

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

Add the following code in front of 12 the prepare statement:

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

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

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

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

It only works with MySQL >= 5.1.12.