6. Template Queries

The idea of template queries is too provide a query with replaceable parameters that can be changed between query calls with out having to reform the queries.

6.1 Setting Them Up

To set up a template query simply enter the query like it is a normal query. For example:

query << "select (%2:field1, %3:field2) from stock where %1:wheref = %q0:what"

And then execute the Query::parse() method. For example:

query.parse()

6.2 Template Format

An example template looks like this

select (%2:field1, %3:field2) from stock where %1:wheref = %q0:what

The numbers represent the element number in SQLQueryParms (see the next section).

The format of the substation parameter is:

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

Where Modifier can be any one of the following:

%

Print an actual "%"

""

"" means nothing. Don't quote or escape no matter what.

q

This will quote and escape it using mysql_escape_string if it is a string or char *, or another Mysql specific types that needs to be quoted.

Q

Quote but don't escape based on the same rules. This can save a bit of 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

``:name'' is for an optional name which aids in filling SQLQueryParms. Name can contain any alpha-numeric characters or the underscore. If you use name it must be proceeded by non-alpha-numeric charter. If this is not the case add a column after the name. If you need to represent an actual colon after the name follow the name by two-columns. The first one will end the name and the second one won't be processed.

6.3 Setting the Parameters

The parameters can either be set when the query is executed or ahead of time by using default parameters.

6.3.1 At Execution Time

To specify the parameters when you want to execute a query simply use Query::store(const SQLString &parm0, [..., const SQLString &parm11]) (or Query::use or Query::execute). Where parm0 corresponds to parameter number 0, etc. You may specify from 1 to 12 different parameters. For example:

Result res = query.store("Dinner Roles", "item", "item", "price")

with the template query provided in section 6.2 would produce:

select (item, price) from stock where item = "Dinner Roles"

The reason for why we didn't make the template the more logical:

select (%0:field1, %1:field2) from stock where %2:wheref = %q3:what

will become apparent shortly.

6.3.2 Using Defaults

You can also set the parameters one at a time by means of the 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. For example:

query.def[0] = "Dinner Roles"; 
query.def[1] = "item"; 
query.def[2] = "item"; 
query.def[3] = "price";

and

query.def["what"] = "Dinner Roles"; 
query.def["wheref"] = "item"; 
query.def["field1"] = "item"; 
query.def["field2"] = "price";

would both have the same effect.

Once all the parameters are set simply execute as you would have executed the query before you knew about template queries. For example:

Result res = query.store()

6.3.3 Combining the Two

You can also combine the use of setting the parameters at execution time and setting them by use of the def object by simply using the extended form of Query::store (or use or execute) without all of necessary parameters specified. For example:

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.

Because the extended form of Query::store can only effect the beginning (by number not by location) parameters the more logical template query:

select (%0:field1, %1:field2) from stock where %2:wheref = %q3:what

would not of worked in this case. Thus the more twisted ordering of

select (%2:field1, %3:field2) from stock where %1:wheref = %q0:what

was needed so that we can specify wheref and what each time.

One thing to watch out for, however, is that Query::store(const char* q) is also defined for executing the query q. For this reason when you use the 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. For example:

Result res = query.store(SQLString("Hamburger Buns")).

6.3.4 Error Handling

If for some reason you did not specify all the parameters when executing the query and the remaining parameters do not have there values set via def the query object will throw a SQLQueryNEParms object. In which case you you can find out what happened by checking the value of SQLQueryNEParms::string.

For example:

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

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 on you part. (Like in the above example)

6.3.5 More Advanced Stuff

To be written. However, for now see the class SQLQuery (5) and SQLQueryParms (5) for more information.