libpqxx
4.0.1
|
Prepared statements are SQL queries that you define once and then invoke as many times as you like, typically with varying parameters. It's basically a function that you can define ad hoc.
If you have an SQL statement that you're going to execute many times in quick succession, it may be more efficient to prepare it once and reuse it. This saves the database backend the effort of parsing complex SQL and figuring out an efficient execution plan. Another nice side effect is that you don't need to worry about escaping parameters.
You create a prepared statement by preparing it on the connection, passing an identifier and its SQL text. The identifier is the name by which the prepared statement will be known; it should consist of letters, digits, and underscores only and start with a letter. The name is case-sensitive.
Once you've done this, you'll be able to call my_statement
from any transaction you execute on the same connection. You start an invocation by looking up your statement using a member function called "prepared"
. (The definition used a different member function, called "prepare"
).
Did I mention that prepared statements can have parameters? The query text can contain $1
, $2
etc. as placeholders for parameter values that you will provide when you invoke the prepared satement.
How do you pass those parameters? C++ has no good way to let you pass an unlimited, variable number of arguments to a function call, and the compiler does not know how many you are going to pass. There's a trick for that: you can treat the value you get back from prepared
as a function, which you call to pass a parameter. What you get back from that call is the same again, so you can call it again to pass another parameter and so on.
Once you've passed all parameters in this way, you invoke the statement with the parameters by calling exec
on the invocation.
This example looks up the prepared statement "find," passes name
and min_salary
as parameters, and invokes the statement with those values: