QSqlQuery prepared statements proper usage

[psih128]

I'm trying to figure out what is the proper way to use prepared statements with QSqlQuery. The docs are not very specific on this subject.

void select(const QSqlDatabase &database) {
    QSqlQuery query(database);
    query.prepare("SELECT * FROM theUniverse WHERE planet = :planet");
    query.bindValue(":planet", "earth");
    query.exec();
}

void select(const QSqlDatabase &database) {
    QSqlQuery query(database);
    query.prepare("SELECT * FROM theUniverse WHERE planet = :planet");
    query.bindValue(":planet", "earth");
    query.exec();
}

To copy to clipboard, switch view to plain text mode 

So will this snippet create a permanent prepared statement in the connection database? Will this prepared statement persist between calls to select(), i.e. will it be saved when the function returns and QSqlQuery query is disposed?

Or should I create QSqlQuery on the heap and use the same instance over and over again?

[Lesiok]

This is not essential if it is prepared or not prepared statement. In Yours example query will be destroyed on exit from procedure. If You want to play with result of query just redefine procedure to :

QSqlQuery select(const QSqlDatabase &database) {
    QSqlQuery query(database);
    query.prepare("SELECT * FROM theUniverse WHERE planet = :planet");
    query.bindValue(":planet", "earth");
    query.exec();
    return query;
}

QSqlQuery select(const QSqlDatabase &database) {
    QSqlQuery query(database);
    query.prepare("SELECT * FROM theUniverse WHERE planet = :planet");
    query.bindValue(":planet", "earth");
    query.exec();
    return query;
}

To copy to clipboard, switch view to plain text mode 

[psih128]

What do you mean not essential?
In my case I have a bunch of repeating queries that I would like to optimize in some way (arguments differ). I wanted to create a prepared query, and make sure it is parsed only once. How do I make sure that the query is prepared only once in a given connection?

[Lesiok]

What do you mean not essential?
In my case I have a bunch of repeating queries that I would like to optimize in some way (arguments differ).

I mean that in Yours example query is created on stack and will be destroyed on exit from select() procedure.

I wanted to create a prepared query, and make sure it is parsed only once. How do I make sure that the query is prepared only once in a given connection?

Create one instance of QSqlQuery as global (?), prepare them and bind values in all places where You need.

[tanderson]

I remember correctly, you have to call prepare between execute calls. I would be cautious about creating a query on the stack of a function and then returning that query. Probably the query copy constructor is smart enough not to make a copy of all the results, but I would check it out. You can do some rudimentary profiling with qdebug and qtime.

[ChrisW67]

So will this snippet create a permanent prepared statement in the connection database? Will this prepared statement persist between calls to select(), i.e. will it be saved when the function returns and QSqlQuery query is disposed?

The prepared statements stays valid as a long as the QSQlQuery object stays in existence and the database is connected to stays accessible. Once the QSqlQuery goes out-of-scope is is destroyed (this is C++) taking the data structures that represent the prepared statement and your means of accessing them with it.

For clarification, a prepared statement is not the same thing as a database procedure, which is persisted in the database and can be accessed by any user of the database.

Or should I create QSqlQuery on the heap and use the same instance over and over again?

Yes, but you still need to keep the pointer to it somewhere that persists between calls to select(). If "select()" is part of a class then make the QSqlQuery a member variable. How you do it depends on how the rest of your program looks.