Re-Understanding QSqlQuery prepare and bindValue

I do not understand why the prepare function and the bindValue function exist in QSqlQuery, although I used them many times. Things could be simpler:

QSqlQuery query("select * from myprogrammingnotes");

This statement alone can execute the query. Or, you can use two statements:

QSqlQuery query;
query.exec("select * from myprogrammingnotes");

So, why bother use prepare/bindValue like:

QSqlQuery query;
query.prepare("select * from myprogrammingnotes");
query.exec();

You may say you can use placeholders in prepare, and use bindValue to bind variables to these placeholders:

QSqlQuery query;
query.prepare("select * from myprogrammingnotes where post=:post");
QString post="myprogrammingnotes.com";
query.bindValue(":post",post);
query.exec();

But why don’t you use:

QString post;
QSqlQuery query(QString("select * from myprogrammingnotes where post='%1'").arg(post));

For long time, I think the prepare and bindValue functions have nothing to do with mysql server. They are just QT stuff. bindValue will replace the placeholders with actual variable values, maybe after escaping some characters of the variable values or adding quotes around string values, to form a valid sql statement. Then exec function will send the sql statement to sql server for execution.

It turns out I am wrong. prepare is not a Qt stuff, but a SQL statement supported by sql server. The prepare Qt function will send its argument to sql server for preparation, i.e., sql server will execute a statement like

PREPARE stmt1 FROM 'SELECT * from myprogrammingnotes where post=:post'

The bindValue function does not send data to sql server; it’s just local stuff.

The exec function does send data to sql server: the value of post, and its type string. Note that exec does not send the whole select statement, but just send the data for the placeholders, thus save the bandwidth.

At the sql server, it will replace the placeholders with the data it received (possibly after escaping and adding quotes based on the type of the data). Then, sql server actually executes the select statement and returns the result to client.

The placeholder marks can only occur at allowed places: the value fields. So, the following code is wrong:

QSqlQuery query;
query.prepare("select * from myprogrammingnotes where post=':post'");

Because :post is not in a value field(':post' is indeed a value field).

The following is also wrong for the same reason.

QSqlQuery query;
query.prepare("select * from myprogrammingnotes where post like %:post%");

The correct way to match a sub-string is:

QSqlQuery query;
query.prepare("select * from myprogrammingnotes where post like :post");
QString post="%myprogrammingnotes.com%";
query.bindValue(":post",post);
query.exec();

The advantages of sql server using the prepare statement can be found here. This article shows the sql statements related to prepare.

Did you like this?
Tip admin with Cryptocurrency