QSqlDatabase Transactions and QSqlQuery Creation

joeuser

The docs say "When using transactions, you must start the transaction before you create your query". But it doesn't say why nor what happens if you don't. Does anyone know why this is or the consequences of not doing it? It seems like this restriction really limits the usefulness of prepared queries.

I've run a couple of test queries created before the transaction and everything seems OK. I can commit and rollback the transactions with no problems.

broadpeak

This is a perfect example:
@
QSqlDatabase::database().transaction();
QSqlQuery query;
query.exec("SELECT id FROM artist WHERE name = 'Gluecifer'");
if (query.next()) {
int artistId = query.value(0).toInt();
query.exec("INSERT INTO cd (id, artistid, title, year) "
"VALUES (201, " + QString::number(artistId)
+ ", 'Riding the Tiger', 1997)");
}
QSqlDatabase::database().commit();
@

Here an ID is queryied, and in the next query this ID is used. So, this ID won't be modified under the whole transactions! And this is an important thing.

joeuser

broadpeak, sorry but I don't see the relevance of your example to my question.

What would be the consequences creating the QSqlQuery before starting the transaction:

@
QSqlQuery query;
QSqlDatabase::database().transaction();

query.exec("SELECT id FROM artist WHERE name = 'Gluecifer'");
...
@

In my testing there doesn't seem to be a difference but the docs say don't do that. I'm wondering why.

What I want to do is implement a class that uses prepared queries to interact with the database. Like the following:

@
class MyData
{
public:
MyData();
~MyData();

void addCD(const QString& artistName, const QString& title, int year);

private:
QSqlQuery insCdQuery;
QSqlQuery insArtistQuery;

};

MyData::MyData() : insCdQuery(), insArtistQuery()
{
insCdQuery.prepare( "INSERT INTO cd (artistid, title, year) VALUES (:artistid, :title, :year)" );
insArtistQuery.prepare( "INSERT INTO artist (name) VALUES (:name)" );

}

void MyData::addCD(const QString& artistName, const QString& title, int year)
{
// assuming the artist doesn't exist yet, insert a new artist

QSqlDatabase::database().transaction();

insArtistQuery.bindValue( ":name", artistName );

if ( insArtistQuery.exec&#40;&#41; )
{
    // get new artist ID
    int artistID = insArtistQuery.lastInsertId().toInt();

    insCdQuery.bindValue( ":artistid", artistID );
    insCdQuery.bindValue( ":title", title );
    insCdQuery.bindValue( ":year", year );

    if ( insCdQuery.exec&#40;&#41; )
    {
        int cdID = insCdQuery.lastInsertId().toInt();
        
        // everything OK, commit transaction and return from method
        QSqlDatabase::database().commit();
        return;
    } 
    

}

// if we made it here something didn't go right, rollback the transaction
QSqlDatabase::database().rollback();

}

@

But the docs suggest I shouldn't do this but doesn't give a reason.

goetz

The query may not be executed in the transaction, I would suppose, probably depending on the driver.