How to get the executed SQL statement associated with QSqlTableModel::setData()?

Question

I am inserting/updating a database table from a Qt application using QSqlTableModelmethods setData, submitAll and commit. For logging purposes, I need to record the corresponding SQL statements. How can I do that? (I am looking at QSqlTableModel::query() but seems as if it only returns SELECTs)

Here is a simplified version of what I am doing.

QSqlTableModel *_dataTableModel = qobject_cast<QSqlTableModel*>(dataTableView->model());
_dataTableModel->setData(item, value);
_dataTableModel->database().transaction();
_dataTableModel->submitAll();
_dataTableModel->database().commit()
QSqlQuery _currentQuery = _dataTableModel->query();
qDebug() << _currentQuery.lastQuery();

The last line always prints a SELECT, even when I do an update or an insert.

update

Here is a self-sufficient code snippet. The setup is that, I have a UI with a QTableView and a QPushButton. clicked() signal of push button is captured in addNewRow() slot and a arbitrary value is inserted to the first cell. I am printing the result of the lastQuery() expecting it to be the insert statement but it is a SELECT statement for the given table. What am I doing wrong?

main.cpp

{
    QApplication a(argc, argv);
    test1 w;
    // connect to and open database
    w.setDataTable("MY_TEST_TABLE");
    w.show();
    return a.exec();
}

test1.h

public:
    void setDataTable(QString dataTableName);

private slots:
    void addNewRow();

test1.cpp

void test1::setDataTable( QString dataTableName )
{
    QSqlTableModel *tableModel = new QSqlTableModel(); 
    tableModel->setTable(dataTableName);
    tableModel->select();   
    ui.tableView->setModel(tableModel);
}

void test1::addNewRow()
{
    QSqlTableModel *tableModel = qobject_cast<QSqlTableModel*>(ui.tableView->model());
    if(!tableModel->insertRow(tableModel->rowCount())) {
            return;
    }       
    tableModel->setData(tableModel->index(0, 0), QVariant("123345")); 
    tableModel->database().transaction(); 
    if(!tableModel->submitAll()) {
            return;
    }  
    if (!tableModel->database().commit()) {                                          
            return;
    }     
    qDebug() << tableModel->query().lastQuery();
}

You would need to provide a self-contained example reproducing the issue. Please refer to sscce.org. — László Papp, Dec 26 '13 at 07:59
This is still not "self-sufficient" example. It only has code chunks. Please provide an example that we can run off-hand, and introspect the operation. Please refer to sscce.org. — László Papp, Dec 26 '13 at 11:42
I was not able to find a solution to this. As a reference to people who'd face the same problem in the future, you will have to explicitly create SQLs and use `exec()` and then `executedQuery()`. — fortytwo, Jan 03 '14 at 05:41
that is also what I wrote, so what is left to be resolved here then? — László Papp, Jan 03 '14 at 05:46
This is getting tired. You mentioned this method AND you also mentioned 'or you could also use the following method to query the last statement if it is done dynamically' and went on to describe how I can get last query from model. That second method is not working. Am I clear enough here? — fortytwo, Jan 03 '14 at 05:55
As I already wrote both ways work for me (and many other people), and you did not yet provide a self-contained example. Either way, I gave the fallback operation what to do if the first does not work, so your problem is solved either way? What can we do more if we cannot reproduce your issue and you do not provide an sscce.org compliant example? — László Papp, Jan 03 '14 at 05:58
Yes. I am using the first method. The method I had a question with is still not working for me. — fortytwo, Dec 22 '14 at 05:51

László Papp · Answer 1 · 2013-12-26T07:23:07.123

0

Based on the official documentation:

Inserting, Updating, and Deleting Records

QSqlQuery can execute arbitrary SQL statements, not just SELECTs. The following example inserts a record into a table using INSERT: QSqlQuery query; query.exec("INSERT INTO employee (id, name, salary) " "VALUES (1001, 'Thad Beaumont', 65000)");

This works with any statements, and not just select. If you do it this way in a raw mode, you could always build a QString first, and use that for the QSqlQuery::exec() operation and then log it, or you could also use the following method to query the last statement if it is done dynamically for instance:

QString QSqlQuery::lastQuery() const

Returns the text of the current query being used, or an empty string if there is no current query text.

So, the only remaining bit is how to get the query object from your model class. This can be done with the following method:

QSqlQuery QSqlQueryModel::query() const

Returns the QSqlQuery associated with this model.

edited Dec 26 '13 at 07:23

answered Dec 26 '13 at 05:35

László Papp

51,870
39
111
135

Thank you. But the thing is that I do not construct a `QSqlQuery`. What I am doing is that using `QSqlTableModel` (to be precise, a subclass of it), `setData()` and then `submit()`. I implemented `queryChange()` to see if that query actually changes after submit and it does. Cannot figure out why I cant get the SQL with `lastQuery()`. Doing some fiddling, let's see where it gets. – fortytwo Dec 26 '13 at 07:07
@RaggS: you can get the QSqlQSquery from QSqlTableModel by using the query() accessor method... – László Papp Dec 26 '13 at 07:12
Laszlo Papp> Added a code snippet to the original question for clarity. – fortytwo Dec 26 '13 at 07:31
@Raggs: also, as I wrote in my answer, you can always construct the query yourself... Although, I find it a bit unusual to log the sql statements rather than Qt operations. – László Papp Dec 26 '13 at 07:49
Laszlo Papp> If you would read the question again, what I have stated is exactly what I was doing; `QSqlTableModel setData, submitAll and commit` and then `query`ing. – fortytwo Dec 26 '13 at 08:29
Laszlo Papp> And to answer why I need SQL statements and not the Qt operations, I need the SQLs so that I can do the same operations on a another database in the form of a macro. – fortytwo Dec 26 '13 at 08:33
@Raggs: no, you have not mentioned neither query(), nor lastQuery(). Please double check the history. You have not even understand how to get the query class from the model. Anyway, good luck to find whatever you are looking for. I do not understand currently what that is. It feels like you are trying to understand what you want. It is hard to help in the stage. – László Papp Dec 26 '13 at 08:34
"I need the SQLs so that I can do the same operations on a another database in the form of a macro" -> Yikes. Sounds like a suboptimal idea to me. I would either replicate the Qt operation, or I would use SQL commands just as per my answer. – László Papp Dec 26 '13 at 08:37
Please read again. Within brackets I mentioned I am 'looking at QSqlTableModel::query() but seems as if it only returns SELECTs' – fortytwo Dec 26 '13 at 08:39
And why I need the SQL statements is that, I don't have access to the second DB. That is the integration DB where changes are made using (by someone with access rights) logging requests with required SQLs. – fortytwo Dec 26 '13 at 08:43
"Please read again. Within brackets I mentioned I am 'looking at QSqlTableModel::query() but seems as if it only returns SELECTs'" -> _After_ my answer, yes. – László Papp Dec 26 '13 at 08:55
"And why I need the SQL statements is that, I don't have access to the second DB. That is the integration DB where changes are made using (by someone with access rights) logging requests with required SQLs." -> My answer gives what you can and what you cannot do, in my opinion. Not sure what else you need. Moreover, You have not provided any self-explanatory example that reproduces the issue. I really cannot. – László Papp Dec 26 '13 at 08:57
Check question history. The part about `query` within brackets is in the original question. Only time I edited the question was to add code bits. So how can I read your comment even before I posted my question? I immensely appreciate your voluntary help but please be respectful even to a newbie like me. We are not here to steal your ideas, we are trying to get help. – fortytwo Dec 26 '13 at 09:02
It is not about stealing idea, but it is hard to help if the question changes radically. That being said, I wrote at least 2-3 times now how you can get what you wish to have even though I think it is a weird use case. I am not sure what more information you need, and based on the ping-pong about history, you do not seem to wish to clarify it either. Also, please note that I cannot reproduce your issue, and you have not provided a self-contained example either which is a minimum for being able to help any further. – László Papp Dec 26 '13 at 09:11
And again, the question did not change at all. And if you care to notice, all this time I was talking about your solution not yielding results I am looking for. It **does not** give me the INSERT SQL. – fortytwo Dec 26 '13 at 11:24
As written in the answer, you have to use the exec() call explicitly, or you have no way to get it. – László Papp Dec 26 '13 at 11:25
But your answer says '... use that for the `QSqlQuery::exec()` operation and then log it, or you could also use the following method ... `QString QSqlQuery::lastQuery() const` and now you say there's only one way. I would rather not build the SQL and then call `exec()` so all this time I was trying to follow your second option and now you tell me it is not possible and the only way is to use `exec()`? I am more than ok with you suggesting possible techniques, but to down-vote my question and to suggest that I cheated is a bit too extreme. – fortytwo Dec 26 '13 at 11:38
There is no second option. You can get the last query if you use exec() by a prebuilt string or with the lastQuery() method. Moreover, I gave the explanation for my down vote. Check how many downvoters do that. Also, I have never claimed you "cheated". I only said it is more difficult to follow a thread if I write something, and then you put that into your question. That triggers my answer useless, and if I do not care, the mess stays around. :) – László Papp Dec 26 '13 at 11:41
And the `lastQuery()` always gives me a SELECT even right after the INSERT. Am I doing something terribly stupid here? – fortytwo Dec 26 '13 at 11:58
Even if you follow the example in the documentation linked, i.e. using `INSERT` with raw exec and query the last one? – László Papp Dec 26 '13 at 11:59
No. I am not using raw exec. Unfortunately, that is not an option with my current task. I set data to be inserted through `QSqlTableModel::setData()` and then do `submit()` and `commit` and then trying `lastQuery()`. By the way, do you want to post all my files, even the UI file? – fortytwo Dec 26 '13 at 12:09
"No. I am not using raw exec. Unfortunately, that is not an option with my current task." -> I am not sure how else you would try to solve, or at least debug the issue if you do not have the willingness for such trials... "By the way, do you want to post all my files, even the UI file?" -> what? Even a command line one-shot running application should be fine. I am not sure why you do not read through sscee.org. Sql does not depend on user interfaces, like GUI... – László Papp Dec 26 '13 at 12:13

How to get the executed SQL statement associated with QSqlTableModel::setData()?

update

1 Answers1