Another powerful feature of MySQL++ is being able to set up template queries. These are kind of like C's printf() facility: you give MySQL++ a string containing the fixed parts of the query and placeholders for the variable parts, and you can later substitute in values into those placeholders.
The following program demonstrates how to use this feature. This is examples/resetdb.cpp, the program you've run a few times now if you've worked through all the examples:
#include "util.h"
#include <mysql++.h>
#include <iostream>
using namespace std;
int
main(int argc, char *argv[])
{
// Connect to database server
mysqlpp::Connection con;
try {
cout << "Connecting to database server..." << endl;
connect_to_db(argc, argv, con, "");
}
catch (exception& er) {
cerr << "Connection failed: " << er.what() << endl;
return 1;
}
// Create new sample database, or re-create it. We suppress
// exceptions, because it's not an error if DB doesn't yet exist.
bool new_db = false;
{
mysqlpp::NoExceptions ne(con);
mysqlpp::Query query = con.query();
if (con.select_db(kpcSampleDatabase)) {
// Toss old table, if it exists. If it doesn't, we don't
// really care, as it'll get created next.
cout << "Dropping existing stock table..." << endl;
query.execute("drop table stock");
}
else {
// Database doesn't exist yet, so create and select it.
if (con.create_db(kpcSampleDatabase) &&
con.select_db(kpcSampleDatabase)) {
new_db = true;
}
else {
cerr << "Error creating DB: " << con.error() << endl;
return 1;
}
}
}
// Create sample data table within sample database.
cout << "Creating new stock table..." << endl;
try {
// Send the query to create the table and execute it.
mysqlpp::Query query = con.query();
query << "create table stock (item char(20) not null, "
"num bigint, weight double, price double, sdate date)";
query.execute();
// Set up the template query to insert the data. The parse()
// call tells the query object that this is a template and
// not a literal query string.
query << "insert into %5:table values (%0q, %1q, %2, %3, %4q)";
query.parse();
// Set the template query parameter "table" to "stock".
query.def["table"] = "stock";
// Notice that we don't give a sixth parameter in these calls,
// so the default value of "stock" is used. Also notice that
// the first row is a UTF-8 encoded Unicode string! All you
// have to do to store Unicode data in recent versions of MySQL
// is use UTF-8 encoding.
cout << "Populating stock table..." << endl;
query.execute("Nürnberger Brats", 92, 1.5, 8.79, "2005-03-10");
query.execute("Pickle Relish", 87, 1.5, 1.75, "1998-09-04");
query.execute("Hot Mustard", 75, .95, .97, "1998-05-25");
query.execute("Hotdog Buns", 65, 1.1, 1.1, "1998-04-23");
cout << (new_db ? "Created" : "Reinitialized") <<
" sample database successfully." << endl;
}
catch (const mysqlpp::BadQuery& er) {
// Handle any query errors
cerr << "Query error: " << er.what() << endl;
return 1;
}
catch (const mysqlpp::BadConversion& er) {
// Handle bad conversions
cerr << "Conversion error: " << er.what() << endl <<
"\tretrieved data size: " << er.retrieved <<
", actual size: " << er.actual_size << endl;
return 1;
}
catch (const mysqlpp::Exception& er) {
// Catch-all for any other MySQL++ exceptions
cerr << "Error: " << er.what() << endl;
return 1;
}
return 0;
}
The line just before the call to query.parse() sets the template, and the parse call puts it into effect. From that point on, you can re-use this query by calling any of several Query member functions that accept query template parameters. In this example, we're using Query::execute().
Let's dig into this feature a little deeper.
To set up a template query, you simply insert it into the Query object, using numbered placeholders wherever you want to be able to change the query. Then, you call the parse() function to tell the Query object that the query string is a template query, and it needs to parse it:
query << "select (%2:field1, %3:field2) from stock where %1:wheref = %0q:what"; query.parse();
The format of the placeholder is:
%###(modifier)(:name)(:)
Where '###' is a number up to three digits. It is the order of parameters given to a SQLQueryParms object, starting from 0.
'modifier' can be any one of the following:
% Print an actual "%" "" Don't quote or escape no matter what. q This will quote and escape the item using the MySQL C API function mysql_escape_string() if it is a string or char *, or another MySQL-specific type that needs to be quoted. Q Quote but don't escape based on the same rules as for 'q'. This can save a bit of processing time if you know the strings will never need quoting r Always quote and escape even if it is a number. R Always quote but don't escape even if it is a number.
":name" is for an optional name which aids in filling SQLQueryParms. Name can contain any alpha-numeric characters or the underscore. You can have a trailing colon, which will be ignored. If you need to represent an actual colon after the name, follow the name with two colons. The first one will end the name and the second one won't be processed.
To specify the parameters when you want to execute a query simply use Query::store(const SQLString &parm0, [..., const SQLString &parm11]). This type of multiple overload also exists for Query::use() and Query::execute(). 'parm0' corresponds to the first parameter, etc. You may specify up to 12 parameters. For example:
Result res = query.store("Dinner Rolls", "item", "item", "price")with the template query provided above would produce:
select (item, price) from stock where item = "Dinner Rolls"
The reason we didn't put the template parameters in numeric order...
select (%0:field1, %1:field2) from stock where %2:wheref = %3q:what
...will become apparent shortly.
You can also set the parameters one at a time by means of class Query's public data member def. To change the values of the def, simply use the subscript operator. You can refer to the parameters either by number or by name. The following two examples have the same effect:
query.def[0] = "Dinner Rolls"; query.def[1] = "item"; query.def[2] = "item"; query.def[3] = "price";
and
query.def["what"] = "Dinner Rolls"; query.def["wheref"] = "item"; query.def["field1"] = "item"; query.def["field2"] = "price";
Once all the parameters are set simply execute as you would have executed the query before you knew about template queries:
Result res = query.store()
You can also combine the use of setting the parameters at execution time and setting them via the def object by calling Query::store() (or use() or execute()) without passing the full number of parameters that the template supports:
query.def["field1"] = "item";
query.def["field2"] = "price";
Result res1 = query.store("Hamburger Buns", "item");
Result res2 = query.store(1.25, "price"); Would store the query:
select (item, price) from stock where item = "Hamburger Buns"
for res1 and
select (item, price) from stock where price = 1.25
for res2.
Now you see why we ordered the placeholders in the template above as we did: we used positions 0 and 1 for the ones we want to change frequently, and used 2 and 3 for the parameters that seldom change.
One thing to watch out for, however, is that Query::store(const char* q) is also defined for executing the query q. Therefore, when you call Query::store() (or use(), or execute()) with only one item and that item is a const char*, you need to explicitly convert it into a SQLString to get the right overload:
Result res = query.store(SQLString("Hamburger Buns")); If for some reason you did not specify all the parameters when executing the query and the remaining parameters do not have their values set via def, the query object will throw a BadParamCount object. If this happens, you can get an explanation of what happened by checking the value of SQLQueryNEParms::string, like so:
query.def["field1"] = "item"; query.def["field2"] = "price"; Result res = query.store(1.25);
This would throw SQLQueryNEParms because the wheref is not specified.
In theory, this exception should never be thrown. If the exception is thrown it probably a logic error in your program.