[Mysqlcppapi-main] Templates

[Bryan W. H. <bwh...@ea...>]

Since our friend did not answer, I'll do so,

Quoting from the MySql++ manual,

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


-- 
____               .:.                 ____
Bryan W. Headley - bwh...@ea...