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 program demonstrates how to use this feature. This is `examples/tquery.cpp`:

    #include "util.h"

    #include <iostream>

    using namespace std;

    int
    main(int argc, char *argv[])
    {
        try {
            // Establish the connection to the database server.
            mysqlpp::Connection con(mysqlpp::use_exceptions);
            if (!connect_to_db(argc, argv, con)) {
                return 1;
            }

            // Build a template query to retrieve a stock item given by
            // item name.
            mysqlpp::Query query = con.query();
            query << "select * from stock where item = %0q";
            query.parse();

            // Retrieve an item added by resetdb; it won't be there if
            // tquery or custom3 is run since resetdb.
            mysqlpp::Result res1 = query.store("Nürnberger Brats");
            if (res1.empty()) {
                throw mysqlpp::BadQuery("UTF-8 bratwurst item not found in "
                        "table, run resetdb");
            }

            // Replace the proper German name with a 7-bit ASCII
            // approximation using a different template query.
            query.reset();
            query << "update stock set item = %0q where item = %1q";
            query.parse();
            mysqlpp::ResNSel res2 = query.execute("Nuerenberger Bratwurst",
                    res1.at(0).at(0).c_str());

            // Print the new table contents.
            print_stock_table(query);
        }
        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.

### 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 = %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.

### 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::storein()`, `Query::use()` and `Query::execute()`. 'parm0' corresponds to the first parameter, etc. You may specify up to 25 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.

### 4.3. Parameter Types and Function Overloads

There are quite a few overloads for each of `Query`'s query execution functions. (`store()`, `use()`, `execute()`...) It's possible to have code that looks like it should work, but which doesn't, because it's calling the wrong overload. For instance:

    query.storein(my_vector, "1");
    query.storein(my_vector, 1);

The first one works, and the second does not. The cause is a vestigial second parameter to one of `storein()`'s overloads that's compatible with integers. Being vestigial, it's only getting in the way right now, but we can't fix it until the next major version of the libary, where it will be okay to break the ABI. Until then, we're stuck with it.

If the MySQL server keeps rejecting your template queries, try explicitly casting the parameters to `SQLString`:

    query.storein(my_vector, SQLString(1));

This ensures that your code calls one of the overloads meant to handle template query parameters. I don't recommend doing this habitually, because it will clutter your code. For the most part, MySQL++'s interface is set up to do the right thing. It's just that there are still a few corner cases that can't be fixed until the next time we can redesign the interface.

### 4.4. Default Parameters

The template query mechanism allows you to set default parameter values. You simply assign a value for the parameter to the appropriate position in the `Query::def` array. You can refer to the parameters either by position or by name:

    query.def[1] = "item";
    query.def["wheref"] = "item";

Both do the same thing.

This mechanism works much like C++'s default function parameter mechanism: if you set defaults for the parameters at the end of the list, you can call one of `Query`'s query execution methods without passing all of the values. If the query takes four parameters and you've set defaults for the last three, you can execute the query using as little as just one explicit parameter.

Now you can see why we numbered the template query parameters the way we did a few sections earlier. We ordered them so that the ones less likely to change have higher numbers, so we don't always have to pass them. We can just give them defaults and take those defaults when applicable. This is most useful when some parameters in a template query vary less often than other parameters. For example:

    query.def["field1"] = "item";
    query.def["field2"] = "price";
    Result res1 = query.store("Hamburger Buns", "item");
    Result res2 = query.store(1.25, "price");

This stores the result of the following queries in `res1` and `res2`, respectively:

    select (item, price) from stock where item = "Hamburger Buns"
    select (item, price) from stock where price = 1.25

Default parameters are useful in this example because we have two queries to issue, and parameters 2 and 3 remain the same for both, while parameters 0 and 1 vary.

Some have been tempted into using this mechanism as a way to set all of the template parameters in a query:

    query.def["what"] = "Hamburger Buns";
    query.def["wheref"] = "item";
    query.def["field1"] = "item";
    query.def["field2"] = "price";
    Result res1 = query.store();

This can work, but it is not designed to. In fact, it's known to fail horribly in one common case. You will not get sympathy if you complain on the mailing list about it not working. If your code doesn't actively reuse at least one of the parameters in subsequent queries, you're abusing MySQL++, and it is likely to take its revenge on you.

### 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 `Query::def`, the query object will throw a `BadParamCount` object. If this happens, you can get an explanation of what happened by calling `BadParamCount::what()`, like so:

    query.def["field1"] = "item";
    query.def["field2"] = "price";
    Result res = query.store(1.25);

This would throw `BadParamCount` 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