1

I noticed that the QSqlDatabase documentation says that

"Note: When using transactions, you must start the transaction before you create your query."

Doesn't this limit the usefulness of QSqlQuery::prepare() in case of transactions if you have to create the query only after you have started the transaction? The same question has been asked here... but no satisfactory answer was provided.

My another question is that if you prepare a query using QSqlQuery::prepare() and call QSqlQuery::finish(), should the query be prepared again? I am asking this because there is no mention of prepared queries in case QSqlQuery::finish() in docs.

Tim Meyer
  • 12,210
  • 8
  • 64
  • 97
Programmer
  • 1,290
  • 2
  • 12
  • 16
  • 3
    There is no difference in the ordering of creating the QSqlQuery and starting a transaction. If you look in the code behind the scenes, creating the QSqlQuery object does not involve communicating with the DBE. I think they were referring to the execution of a SQL query, not the creation of the QSqlQuery object. – RobbieE Aug 19 '13 at 06:33

1 Answers1

4

The main point of transactions is to be able to execute several different queries, where the database changes will only be commited if everything worked.

Consider the following code structure (pseudo code):

startTransaction()
try
{
    insertParentDataset(parent);
    insertChildDataset(child1);
    insertChildDataset(child2);
    commitTransaction();
}
catch
{
    rollbackTransaction()
}

Code which is structured like this will ensure that each query will only be executed if the previous query worked, and in case of any error, every change inflicted by the methods in the try { } block will be ignored, i.e. the database will still be in the same state as before.

For those reasons, I only use transactions when modifying the database, not when reading from them.

The decision if you prepare or don't prepare your queries is a completely different thing. You usually prepare queries in order to improve performance of mass data insertion or to prevent users from doing SQL Injection (there may be more reasons), but that doesn't affect the behaviour of transactions. From the view of the transaction, it doesn't matter if a 'normal' query or a prepared one is executed.

Concerning finish(), I have never used that. It's usually more convenient to use different QSqlQuery objects for different queries.

Tim Meyer
  • 12,210
  • 8
  • 64
  • 97
  • Thanks for your comment. 1) "I only use transactions when modifying the database, not when reading from them." I guess the decision as to when to use transactions sometimes depends on the database driver also!. For example in Sqlite, if there are multiple select statements to be executed, it is better to wrap them inside a transaction due to performance reasons. See [this question](http://stackoverflow.com/questions/1711631/how-do-i-improve-the-performance-of-sqlite) for more details. – Programmer Aug 19 '13 at 08:57
  • 2)"Concerning finish()" : If the same query needs to be executed again and again, then I prepare the query once like [this](http://stackoverflow.com/questions/17479426/how-to-optimize-a-block-of-qt-code-that-involving-huge-number-of-sql-queries) and then I can use it any number of times. But if a query returns a large dataset, then I may have to use finish() to free up resources. In such cases I wanted to know whether I have to prepare the query again. – Programmer Aug 19 '13 at 09:03
  • You might be interested in [this question and answer](http://stackoverflow.com/questions/5609245/qsqlquery-prepared-statements-proper-usage) then. – Tim Meyer Aug 19 '13 at 12:32
  • Thanks for the link. It helped me. I tested the queries on a sqlite database and I was able to perform 1lakh inserts per second without any memory issues. Accepting the answer because of the link provided. – Programmer Aug 20 '13 at 08:35