4. Template Queries
Prev |  | Next

## 4. Template Queries

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 example demonstrates how to use this feature. This is the program you've run a few times now if you've worked through all the examples, resetdb.cpp:

>     #include "util.h"

>     #include <mysql++.h>

>     #include <iostream>

>     using namespace std;

>     int
>     main(int argc, char *argv[])
>     {
>         mysqlpp::Connection con(mysqlpp::use_exceptions);
>         try {
>             if (!connect_to_db(argc, argv, con, "")) {
>                 return 1;
>             }
>         }
>         catch (exception& er) {
>             cerr << "Connection failed: " << er.what() << endl;
>             return 1;
>         }

>         bool created = false;
>         try {
>             con.select_db(kpcSampleDatabase);
>         }
>         catch (mysqlpp::BadQuery &) {
>             // Couldn't switch to the sample database, so assume that it
>             // doesn't exist and create it.  If that doesn't work, exit
>             // with an error.
>             if (con.create_db(kpcSampleDatabase)) {
>                 cerr << "Failed to create sample database: " <<
>                         con.error() << endl;
>                 return 1;
>             }
>             else if (!con.select_db(kpcSampleDatabase)) {
>                 cerr << "Failed to select sample database." << endl;
>                 return 1;
>             }
>             else {
>                 created = true;
>             }
>         }

>         mysqlpp::Query query = con.query(); // create a new query object

>         try {
>             query.execute("drop table stock");
>         }
>         catch (mysqlpp::BadQuery&) {
>             // ignore any errors
>         }

>         try {
>             // Send the query to create the table and execute it.
>             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();

>             // This is setting the parameter named table to stock.
>             query.def["table"] = "stock";

>             // The last parameter "table" is not specified here.  Thus the
>             // default value for "table" is used, which is "stock".  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.
>             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");

>             if (created) {
>                 cout << "Created";
>             }
>             else {
>                 cout << "Reinitialized";
>             }
>             cout << " sample database successfully." << endl;
>         }
>         catch (mysqlpp::BadQuery& er) {
>             // Handle any connection or query errors
>             cerr << "Error: " << er.what() << endl;
>             return 1;
>         }
>         catch (mysqlpp::BadConversion& er) {
>             // Handle bad conversions
>             cerr << "Error: " << er.what() << "\"." << endl
>                 << "retrieved data size: " << er.retrieved
>                 << " actual data size: " << er.actual_size << endl;
>             return 1;
>         }
>         catch (exception& er) {
>             // Catch-all for any other standard C++ 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.

### 4.1. Setting up template queries

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 = %q0:what";
        query.parse();

The format of the placeholder is:

        %(modifier)##(:name)(:)

Where 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.

## represents a number up to two digits. It is the order of parameters given to a SQLQueryParms object, starting from 0.

":name" is for an optional name which aids in filling SQLQueryParms. Name can contain any alpha-numeric characters or the underscore. If you add this, it must start with a letter. If this is not the case, add a colon after the name. 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.

### 4.2. Setting the parameters at execution time

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 = %q3:what

...will become apparent shortly.

### 4.3. Using defaults

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()

### 4.4. Combining the two

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"));

### 4.5. Error Handling

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 SQLQueryNEParms 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.

Prev | Up | Next
3. Tutorial | Home | 5. Specialized SQL Structures