File: ssqls.html

package info (click to toggle)
mysql%2B%2B 3.2.5-2.3
links: PTS
area: main
in suites: forky, sid, trixie
size: 18,356 kB
sloc: cpp: 35,788; sh: 3,693; perl: 789; makefile: 728
file content (1232 lines) | stat: -rwxr-xr-x 71,558 bytes
parent folder | download | duplicates (3)
<html><head><meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>5.Specialized SQL Structures</title><link rel="stylesheet" type="text/css" href="tangentsoft.css"><meta name="generator" content="DocBook XSL Stylesheets V1.79.1"><link rel="home" href="index.html" title="MySQL++ v3.2.5 User Manual"><link rel="up" href="index.html" title="MySQL++ v3.2.5 User Manual"><link rel="prev" href="tquery.html" title="4.Template Queries"><link rel="next" href="unicode.html" title="6.Using Unicode with MySQL++"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">5.Specialized SQL Structures</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="tquery.html">Prev</a></td><th width="60%" align="center"></th><td width="20%" align="right"><a accesskey="n" href="unicode.html">Next</a></td></tr></table><hr></div><div class="sect1"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="ssqls"></a>5.Specialized SQL Structures</h2></div></div></div><p>The Specialized SQL Structure (SSQLS) feature lets you easily
  define C++ structures that match the form of your SQL tables. At the
  most superficial level, an SSQLS has a member variable corresponding
  to each field in the SQL table. But, an SSQLS also has several
  methods, operators, and data members used by MySQL++&#8217;s internals to
  provide neat functionality, which we cover in this chapter.</p><p>You define SSQLSes using the macros defined in
  <code class="filename">ssqls.h</code>. This is the only MySQL++ header not
  automatically included for you by <code class="filename">mysql++.h</code>. You
  have to include it in code modules that use the SSQLS feature.</p><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="sql_create"></a>5.1.sql_create</h3></div></div></div><p>Let&#8217;s say you have the following SQL table:</p><pre class="programlisting">
CREATE TABLE stock (
    item CHAR(30) NOT NULL,
    num BIGINT NOT NULL,
    weight DOUBLE NOT NULL,
    price DECIMAL(6,2) NOT NULL,
    sdate DATE NOT NULL,
    description MEDIUMTEXT NULL)</pre><p>You can create a C++ structure corresponding to this table
    like so:</p><pre class="programlisting">
sql_create_6(stock, 1, 6,
    mysqlpp::sql_char, item,
    mysqlpp::sql_bigint, num,
    mysqlpp::sql_double, weight,
    mysqlpp::sql_decimal, price,
    mysqlpp::sql_date, sdate,
    mysqlpp::Null&lt;mysqlpp::sql_mediumtext&gt;, description)</pre><p>This declares the <code class="classname">stock</code> structure,
    which has a data member for each SQL column, using the same names.
    The structure also has a number of member functions, operators and
    hidden data members, but we won&#8217;t go into that just
    now.</p><p>The parameter before each field name in the
    <code class="function">sql_create_#</code> call is the C++ data type that
    will be used to hold that value in the SSQLS. While you could use
    plain old C++ data types for most of these columns (<span class="type">long
    int</span> instead of <span class="type">mysqlpp::sql_bigint</span>, for
    example) it&#8217;s <a class="link" href="tutorial.html#sql-types" title="3.6.C++ vs. SQL Data Types">best to use the
    MySQL++ typedefs</a>.</p><p>Sometimes you have no choice but to use special MySQL++
    data types to fully express the database schema. Consider
    the <code class="varname">description</code> field. MySQL++&#8217;s
    <span class="type">sql_mediumtext</span> type is just an alias for
    <span class="type">std::string</span>, since we don&#8217;t need anything
    fancier to hold a SQL <span class="type">MEDIUMTEXT</span> value.
    It&#8217;s the SQL NULL attribute that causes trouble:
    it <a class="link" href="tutorial.html#sql-null" title="3.7.Handling SQL Nulls">has no equivalent in the C++
    type system</a>. MySQL++ offers the <tt><a href="../refman/classmysqlpp_1_1Null.html">Null</a></tt> template, which bridges this difference between the
    two type systems.</p><p>The general format of this macro is:</p><pre class="programlisting">
sql_create_#(NAME, COMPCOUNT, SETCOUNT, TYPE1, ITEM1, ... TYPE#, ITEM#)</pre><p>where # is the number of member variables,
    <em class="parameter"><code>NAME</code></em> is the name of the structure you wish to
    create, <em class="parameter"><code>TYPEx</code></em> is the type of a member
    variable, and <em class="parameter"><code>ITEMx</code></em> is that variable&#8217;s
    name.</p><p>The <em class="parameter"><code>COMPCOUNT</code></em> and
    <em class="parameter"><code>SETCOUNT</code></em> arguments are described in the next
    section.</p></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-compare-init"></a>5.2.SSQLS Comparison and Initialization</h3></div></div></div><p>The <code class="varname">sql_create_#</code> macro adds member
    functions and operators to each SSQLS that allow you to compare one
    SSQLS instance to another. These functions compare the first
    <em class="parameter"><code>COMPCOUNT</code></em> fields in the structure. In the
    example above, <em class="parameter"><code>COMPCOUNT</code></em> is 1, so only the
    <code class="varname">item</code> field will be checked when comparing two
    <code class="classname">stock</code> structures.</p><p>This feature works best when your table&#8217;s
    &#8220;key&#8221; fields are the first ones in the SSQLS and
    you set <em class="parameter"><code>COMPCOUNT</code></em> equal to the number
    of key fields. That way, a check for equality between two SSQLS
    structures in your C++ code will give the same results as a check
    for equality in SQL.</p><p><em class="parameter"><code>COMPCOUNT</code></em> must be at least 1. The
    current implementation of <code class="varname">sql_create_#</code> cannot
    create an SSQLS without comparison member functions.</p><p>Because our <code class="classname">stock</code> structure
    is less-than-comparable, you can use it in STL algorithms
    and containers that require this, such as STL&#8217;s associative
    containers:</p><pre class="programlisting">
std::set&lt;stock&gt; result;   
query.storein(result);
cout &lt;&lt; result.lower_bound(stock("Hamburger"))-&gt;item &lt;&lt; endl;</pre><p>This will print the first item in the result set that begins
    with &#8220;Hamburger.&#8221;</p><p>The third parameter to <code class="varname">sql_create_#</code> is
    <em class="parameter"><code>SETCOUNT</code></em>. If this is nonzero, it adds an
    initialization constructor and a <code class="function">set()</code> member
    function taking the given number of arguments, for setting the first
    <span class="emphasis"><em>N</em></span> fields of the structure. For example, you
    could change the above example like so:</p><pre class="programlisting">
sql_create_6(stock, 1, 2,
    mysqlpp::sql_char, item,
    mysqlpp::sql_bigint, num,         
    mysqlpp::sql_double, weight,  
    mysqlpp::sql_decimal, price,  
    mysqlpp::sql_date, sdate,
    mysqlpp::Null&lt;mysqlpp::sql_mediumtext&gt;, description)
    
stock foo("Hotdog", 52);</pre><p>In addition to this 2-parameter constructor, this version
    of the <code class="classname">stock</code> SSQLS will have a similar
    2-parameter <code class="function">set()</code> member function.</p><p>The <em class="parameter"><code>COMPCOUNT</code></em> and
    <em class="parameter"><code>SETCOUNT</code></em> values cannot be equal. If they
    are, the macro will generate two initialization constructors with
    identical parameter lists, which is illegal in C++. You might be
    asking, why does there need to be a constructor for comparison to
    begin with? It&#8217;s often convenient to be able to say something
    like <strong class="userinput"><code>x == stock("Hotdog")</code></strong>. This requires
    that there be a constructor taking <em class="parameter"><code>COMPCOUNT</code></em>
    arguments to create the temporary <code class="classname">stock</code>
    instance used in the comparison.</p><p>This limitation is not a problem in practice. If you
    want the same number of parameters in the initialization
    constructor as the number of fields used in comparisons,
    pass 0 for <em class="parameter"><code>SETCOUNT</code></em>. This suppresses
    the duplicate constructor you&#8217;d get if you used the
    <em class="parameter"><code>COMPCOUNT</code></em> value instead. This is most
    useful in very small SSQLSes, since it&#8217;s easier for the
    number of key fields to equal the number of fields you want to
    compare on:</p><pre class="programlisting">
sql_create_1(stock_item, 1, 0, mysqlpp::sql_char, item)</pre></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-retrieving"></a>5.3.Retrieving data</h3></div></div></div><p>Let&#8217;s put SSQLS to use. This is
    <code class="filename">examples/ssqls1.cpp</code>:</p><pre class="programlisting">#include "cmdline.h"
#include "printdata.h"
#include "stock.h"

#include &lt;iostream&gt;
#include &lt;vector&gt;

using namespace std;

int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    mysqlpp::examples::CommandLine cmdline(argc, argv);
    if (!cmdline) {
        return 1;
    }

    try {                       
        // Establish the connection to the database server.
        mysqlpp::Connection con(mysqlpp::examples::db_name,
                cmdline.server(), cmdline.user(), cmdline.pass());

        // Retrieve a subset of the stock table's columns, and store
        // the data in a vector of 'stock' SSQLS structures.  See the
        // user manual for the consequences arising from this quiet
        // ability to store a subset of the table in the stock SSQLS.
        mysqlpp::Query query = con.query("select item,description from stock");
        vector&lt;stock&gt; res;
        query.storein(res);

        // Display the items
        cout &lt;&lt; "We have:" &lt;&lt; endl;
        vector&lt;stock&gt;::iterator it;
        for (it = res.begin(); it != res.end(); ++it) {
            cout &lt;&lt; '\t' &lt;&lt; it-&gt;item;
            if (it-&gt;description != mysqlpp::null) {
                cout &lt;&lt; " (" &lt;&lt; it-&gt;description &lt;&lt; ")";
            }
            cout &lt;&lt; endl;
        }
    }
    catch (const mysqlpp::BadQuery&amp; er) {
        // Handle any query errors
        cerr &lt;&lt; "Query error: " &lt;&lt; er.what() &lt;&lt; endl;
        return -1;
    }
    catch (const mysqlpp::BadConversion&amp; er) {
        // Handle bad conversions; e.g. type mismatch populating 'stock'
        cerr &lt;&lt; "Conversion error: " &lt;&lt; er.what() &lt;&lt; endl &lt;&lt;
                "\tretrieved data size: " &lt;&lt; er.retrieved &lt;&lt;
                ", actual size: " &lt;&lt; er.actual_size &lt;&lt; endl;
        return -1;
    }
    catch (const mysqlpp::Exception&amp; er) {
        // Catch-all for any other MySQL++ exceptions
        cerr &lt;&lt; "Error: " &lt;&lt; er.what() &lt;&lt; endl;
        return -1;
    }

    return 0;
}
</pre><p>Here is the stock.h header used by that example, and by
    several others below:</p><pre class="programlisting">#include &lt;mysql++.h&gt;
#include &lt;ssqls.h&gt;

// The following is calling a very complex macro which will create
// "struct stock", which has the member variables:
//
//   sql_char item;
//   ...
//   sql_mediumtext_null description;
//
// plus methods to help populate the class from a MySQL row.  See the
// SSQLS sections in the user manual for further details.
sql_create_6(stock,
    1, 6, // The meaning of these values is covered in the user manual
    mysqlpp::sql_char, item,
    mysqlpp::sql_bigint, num,
    mysqlpp::sql_double, weight,
    mysqlpp::sql_double_null, price,
    mysqlpp::sql_date, sDate,           // SSQLS isn't case-sensitive!
    mysqlpp::sql_mediumtext_null, description)

</pre><p>This example produces the same output as
    <code class="filename">simple1.cpp</code> (see <a class="xref" href="tutorial.html#simple" title="3.2.A Simple Example">Section3.2, &#8220;A Simple Example&#8221;</a>),
    but it uses higher-level data structures paralleling the
    database schema instead of MySQL++&#8217;s lower-level
    generic data structures. It also uses MySQL++&#8217;s <a class="xref" href="tutorial.html#exceptions" title="3.4.Exceptions">exceptions</a> for error handling instead of doing
    everything inline. For small example programs like these, the
    overhead of SSQLS and exceptions doesn&#8217;t pay off very
    well, but in a real program, they end up working much better
    than hand-rolled code.</p><p>Notice that we are only pulling a single column from the
    <code class="varname">stock</code> table, but we are storing the rows in a
    <span class="type">std::vector&lt;stock&gt;</span>. It may strike you as
    inefficient to have five unused fields per record. It&#8217;s
    easily remedied by defining a subset SSQLS:</p><pre class="programlisting">
sql_create_1(stock_subset,
  1, 0,
  string, item)
  
vector&lt;stock_subset&gt; res;
query.storein(res);
// ...etc...</pre><p>MySQL++ is flexible about populating
    SSQLSes.<a href="#ftn.idp140589776381800" class="footnote" name="idp140589776381800"><sup class="footnote">[13]</sup></a> It works much like the Web, a design
    that&#8217;s enabled the development of the largest distributed
    system in the world. Just as a browser ignores tags and attributes
    it doesn&#8217;t understand, you can populate an SSQLS from a query
    result set containing columns that don&#8217;t exist in the SSQLS.
    And as a browser uses sensible defaults when the page doesn&#8217;t
    give explicit values, you can have an SSQLS with more fields defined
    than are in the query result set, and these SSQLS fields will get
    default values. (Zero for numeric types, <span class="type">false</span> for
    <span class="type">bool</span>, and a type-specific default for anything more
    complex, like <span class="type">mysqlpp::DateTime</span>.)</p><p>In more concrete terms, the example above is able to
    populate the <code class="classname">stock</code> objects using as
    much information as it has, and leave the remaining fields at
    their defaults. Conversely, you could also stuff the results
    of <code class="computeroutput">SELECT * FROM stock</code> into
    the <code class="classname">stock_subset</code> SSQLS declared above;
    the extra fields would just be ignored.</p><p>We&#8217;re trading run-time efficiency for flexibility
    here, usually the right thing in a distributed system. Since MySQL
    is a networked database server, many uses of it will qualify as
    distributed systems. You can&#8217;t count on being able to update
    both the server(s) and all the clients at the same time, so you
    have to make them flexible enough to cope with differences while
    the changes propagate. As long as the new database schema
    isn&#8217;t too grossly different from the old, your programs
    should continue to run until you get around to updating them to
    use the new schema.</p><p>There&#8217;s a danger that this quiet coping behavior
    may mask problems, but considering that the previous behavior
    was for the program to crash when the database schema got out
    of synch with the SSQLS definition, it&#8217;s likely to be
    taken as an improvement.</p></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-adding"></a>5.4.Adding data</h3></div></div></div><p>MySQL++ offers several ways to insert data in SSQLS form
    into a database table.</p><div class="sect3"><div class="titlepage"><div><div><h4 class="title"><a name="ssqls-add-one"></a>Inserting a Single Row</h4></div></div></div><p>The simplest option is to insert a single row at a
      time. This is <code class="filename">examples/ssqls2.cpp</code>:</p><pre class="programlisting">#include "cmdline.h"
#include "printdata.h"
#include "stock.h"

#include &lt;iostream&gt;
#include &lt;limits&gt;

using namespace std;

int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    mysqlpp::examples::CommandLine cmdline(argc, argv);
    if (!cmdline) {
        return 1;
    }

    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(mysqlpp::examples::db_name,
                cmdline.server(), cmdline.user(), cmdline.pass());

        // Create and populate a stock object.  We could also have used
        // the set() member, which takes the same parameters as this
        // constructor.
        stock row("Hot Dogs", 100, 1.5,
                numeric_limits&lt;double&gt;::infinity(), // "priceless," ha!
                mysqlpp::sql_date("1998-09-25"), mysqlpp::null);

        // Form the query to insert the row into the stock table.
        mysqlpp::Query query = con.query();
        query.insert(row);

        // Show the query about to be executed.
        cout &lt;&lt; "Query: " &lt;&lt; query &lt;&lt; endl;

        // Execute the query.  We use execute() because INSERT doesn't
        // return a result set.
        query.execute();

        // Retrieve and print out the new table contents.
        print_stock_table(query);
    }
    catch (const mysqlpp::BadQuery&amp; er) {
        // Handle any query errors
        cerr &lt;&lt; "Query error: " &lt;&lt; er.what() &lt;&lt; endl;
        return -1;
    }
    catch (const mysqlpp::BadConversion&amp; er) {  
        // Handle bad conversions
        cerr &lt;&lt; "Conversion error: " &lt;&lt; er.what() &lt;&lt; endl &lt;&lt;
                "\tretrieved data size: " &lt;&lt; er.retrieved &lt;&lt;
                ", actual size: " &lt;&lt; er.actual_size &lt;&lt; endl;
        return -1;
    }
    catch (const mysqlpp::Exception&amp; er) {
        // Catch-all for any other MySQL++ exceptions
        cerr &lt;&lt; "Error: " &lt;&lt; er.what() &lt;&lt; endl;
        return -1;
    }

    return 0;
}
</pre><p>That&#8217;s all there is to it! MySQL++ even takes care
      of <a class="link" href="tutorial.html#qescape" title="3.5.Quoting and Escaping">quoting and escaping</a> the
      data when building queries from SSQLS structures. It&#8217;s
      efficient, too: MySQL++ is smart enough to quote and escape
      data only for those data types that actually require it.</p></div><div class="sect3"><div class="titlepage"><div><div><h4 class="title"><a name="ssqls-add-range"></a>Inserting Many Rows</h4></div></div></div><p>Inserting a single row is useful, to be sure,
      but you might want to be able to insert many SSQLSes or
      <code class="classname">Row</code> objects at once. MySQL++ knows
      how to do that, too, sparing you the necessity of writing
      the loop. Plus, MySQL++ uses an optimized implementation of
      this algorithm, packing everything into a single SQL query,
      eliminating the overhead of multiple calls between the
      client and server. It&#8217;s just a different overload of
      <code class="methodname">insert()</code>, which accepts a pair of
      iterators into an STL container, inserting every row in that
      range:</p><pre class="programlisting">vector&lt;stock&gt; lots_of_stuff;
...populate the vector somehow...
query.insert(lots_of_stuff.begin(), lots_of_stuff.end()).execute();</pre><p>By the way, notice that you can chain
      <code class="classname">Query</code> operations like in the last line
      above, because its methods return <span class="symbol">*this</span> where
      that makes sense.</p></div><div class="sect3"><div class="titlepage"><div><div><h4 class="title"><a name="ssqls-insertfrom"></a>Working Around MySQL&#8217;s Packet Size Limit</h4></div></div></div><p>The two-iterator form of <code class="methodname">insert()</code>
      has an associated risk: MySQL has a limit on the size of the
      SQL query it will process. The default limit is 1MB. You
      can raise the limit, but the reason the limit is configurable
      is not to allow huge numbers of inserts in a single query. They
      made the limit configurable because a single row might be bigger
      than 1MB, so the default would prevent you from inserting
      anything at all. If you raise the limit simply to be able to
      insert more rows at once, you&#8217;re courting disaster with
      no compensating benefit: the more data you send at a time, the
      greater the chance and cost of something going wrong.  Worse,
      this is pure risk, because by the time you hit 1MB,
      the per-packet overhead is such a small fraction of the data
      being transferred that increasing the packet size buys you
      essentially nothing.</p><p>Let&#8217;s say you have a <code class="classname">vector</code>
      containing several megabytes of data; it will get even bigger
      when expressed in SQL form, so there&#8217;s no way you can
      insert it all in a single query without raising the MySQL packet
      limit. One way to cope would be to write your own nave
      loop, inserting just one row at a time. This is slow, because
      you&#8217;re paying the per-query cost for every row in the
      container. Then you might realize that you could use the two
      iterator form of <code class="methodname">insert()</code>, passing
      iterators expressing sub-ranges of the container instead of
      trying to insert the whole container in one go. Now you&#8217;ve
      just got to figure out how to calculate those sub-ranges to
      get efficient operation without exceeding the packet size
      limit.</p><p>MySQL++ already knows how to do that, too, with
      <code class="methodname">Query::insertfrom()</code>.  We gave
      it a different name instead of adding yet another
      <code class="methodname">insert()</code> overload because it
      doesn&#8217;t merely build the <span class="command"><strong>INSERT</strong></span> query,
      which you then <code class="methodname">execute()</code>. It&#8217;s
      more like <code class="methodname">storein()</code>, in that it wraps
      the entire operation up in a single call. This feature is
      demonstrated in <code class="filename">examples/ssqls6.cpp</code>:</p><pre class="programlisting">#include "cmdline.h"
#include "printdata.h"
#include "stock.h"

#include &lt;fstream&gt;

using namespace std;


// Breaks a given text line of tab-separated fields up into a list of
// strings.
static size_t
tokenize_line(const string&amp; line, vector&lt;mysqlpp::String&gt;&amp; strings)
{
    string field;
    strings.clear();

    istringstream iss(line);
    while (getline(iss, field, '\t')) {
        strings.push_back(mysqlpp::String(field));
    }

    return strings.size();
}


// Reads a tab-delimited text file, returning the data found therein
// as a vector of stock SSQLS objects.
static bool
read_stock_items(const char* filename, vector&lt;stock&gt;&amp; stock_vector)
{
    ifstream input(filename);
    if (!input) {
        cerr &lt;&lt; "Error opening input file '" &lt;&lt; filename &lt;&lt; "'" &lt;&lt; endl;
        return false;
    }

    string line;
    vector&lt;mysqlpp::String&gt; strings;
    while (getline(input, line)) {
        if (tokenize_line(line, strings) == 6) {
            stock_vector.push_back(stock(string(strings[0]), strings[1],
                    strings[2], strings[3], strings[4], strings[5]));
        }
        else {
            cerr &lt;&lt; "Error parsing input line (doesn't have 6 fields) " &lt;&lt; 
                    "in file '" &lt;&lt; filename &lt;&lt; "'" &lt;&lt; endl;
            cerr &lt;&lt; "invalid line: '" &lt;&lt; line &lt;&lt; "'" &lt;&lt; endl;
        }
    }

    return true;
}


int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    mysqlpp::examples::CommandLine cmdline(argc, argv);
    if (!cmdline) {
        return 1;
    }

    // Read in a tab-delimited file of stock data
    vector&lt;stock&gt; stock_vector;
    if (!read_stock_items("examples/stock.txt", stock_vector)) {
        return 1;
    }

    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(mysqlpp::examples::db_name,
                cmdline.server(), cmdline.user(), cmdline.pass());

        // Clear all existing rows from stock table, as we're about to
        // insert a bunch of new ones, and we want a clean slate.
        mysqlpp::Query query = con.query();
        query.exec("DELETE FROM stock");

        // Insert data read from the CSV file, allowing up to 1000
        // characters per packet.  We're using a small size in this
        // example just to force multiple inserts.  In a real program,
        // you'd want to use larger packets, for greater efficiency.
        mysqlpp::Query::MaxPacketInsertPolicy&lt;&gt; insert_policy(1000);
        query.insertfrom(stock_vector.begin(), stock_vector.end(),
                insert_policy);

        // Retrieve and print out the new table contents.
        print_stock_table(query);
    }
    catch (const mysqlpp::BadQuery&amp; er) {
        // Handle any query errors
        cerr &lt;&lt; "Query error: " &lt;&lt; er.what() &lt;&lt; endl;
        return -1;
    }
    catch (const mysqlpp::BadConversion&amp; er) {
        // Handle bad conversions
        cerr &lt;&lt; "Conversion error: " &lt;&lt; er.what() &lt;&lt; endl &lt;&lt;
                "\tretrieved data size: " &lt;&lt; er.retrieved &lt;&lt;
                ", actual size: " &lt;&lt; er.actual_size &lt;&lt; endl;
        return -1;
    }
    catch (const mysqlpp::BadInsertPolicy&amp; er) {
        // Handle bad conversions
        cerr &lt;&lt; "InsertPolicy error: " &lt;&lt; er.what() &lt;&lt; endl;
        return -1;
    }
    catch (const mysqlpp::Exception&amp; er) {
        // Catch-all for any other MySQL++ exceptions
        cerr &lt;&lt; "Error: " &lt;&lt; er.what() &lt;&lt; endl;
        return -1;
    }

    return 0;
}

</pre><p>Most of the complexity in this example goes to
      just reading in the data from a file; we have to get
      our test data from somewhere. There are only two key
      lines of code: create an insertion policy object, and
      pass it along with an STL container full of row data to
      <code class="methodname">Query::insertfrom()</code>.</p><p>This policy object is the main thing that differentiates
      <code class="methodname">insertfrom()</code> from the two-iterator
      form of <code class="methodname">insert()</code>. It controls
      how <code class="methodname">insertfrom()</code> builds the query
      strings, primarily controlling how large each query gets before
      <code class="methodname">insertfrom()</code> executes it and starts
      building a new query. We designed it to use policy objects
      because there is no single &#8220;right&#8221; choice for the
      decisions it makes.</p><p>MySQL++ ships with three different insertion policy
      classes, which should cover most situations.</p><p><code class="classname">MaxPacketInsertPolicy</code>, demonstrated
      in the example above, does things the most obvious way: when
      you create it, you pass the maximum packet size, which it uses
      to prevent queries from going over the size limit. It builds
      up a query string row by row, checking each time through the
      loop whether adding another insert statement to the query string
      would make the packet size go over the limit. When that happens,
      or it gets to the end of the iteration range, it executes the
      query and starts over if it&#8217;s not yet at the end. This
      is robust, but it has a downside: it has to build each insert
      query in advance of knowing that it can append it to the larger
      query. Any time an insert query would push the packet over the
      limit, it has to throw it away, causing the library to do more
      work than is strictly necessary.</p><p>Imagine you&#8217;ve done some benchmarking and have found
      that the point of diminishing returns is at about 20KB per
      query in your environment; beyond that point, the per-query
      overhead ceases to be an issue. Let&#8217;s also say you
      know for a fact that your largest row will always be less than
      1MB &#8212; less 20KB &#8212; when expressed as a SQL
      insert statement. In that case, you can use the more efficient
      <code class="classname">SizeThresholdInsertPolicy</code>. It differs from
      <code class="classname">MaxPacketInsertPolicy</code> in that it allows
      <code class="methodname">insertfrom()</code> to insert rows blindly into
      the query string until the built query exceeds the threshold,
      20KB in this example. Then it ships the packet off, and if
      successful, starts a new query. Thus, each query (except possibly
      the last) will be at least 20KB, exceeding that only by as
      much as one row&#8217;s worth of data, minus one byte. This is
      quite appropriate behavior when your rows are relatively small,
      as is typical for tables not containing BLOB data. It is more
      efficient than <code class="classname">MaxPacketInsertPolicy</code>
      because it never has to throw away any SQL fragments.</p><p>The simplest policy object type is
      <code class="classname">RowCountInsertPolicy</code>. This lets you simply
      say how many rows at a time to insert into the database. This
      works well when you have a good handle on how big each row
      will be, so you can calculate in advance how many rows you
      can insert at once without exceeding some given limit. Say
      you know your rows can&#8217;t be any bigger than about
      1KB. If we stick with that 20KB target, passing
      <code class="classname">RowCountInsertPolicy&lt;&gt;(20)</code>
      for the policy object would ensure we never exceed
      the size threshold. Or, say that maximum size
      value above is still true, but we also know the
      average row size is only 200 bytes. You could pass
      <code class="classname">RowCountInsertPolicy&lt;&gt;(100)</code> for
      the policy, knowing that the average packet size will be around
      20KB, and the worst case packet size 100KB, still
      nowhere near the default 1MB packet size limit. The code
      for this policy is very simple, so it makes your program a little
      smaller than if you used either of the above policies. Obviously
      it&#8217;s a bad choice if you aren&#8217;t able to predict
      the size of your rows accurately.</p><p>If one of the provided insert policy classes
      doesn&#8217;t suit your needs, you can easily create
      a custom one. Just study the implementation in
      <code class="filename">lib/insertpolicy.*</code>.</p></div><div class="sect3"><div class="titlepage"><div><div><h4 class="title"><a name="ssqls-insertfrom-transactions"></a>Interaction with Transactions</h4></div></div></div><p>These policy classes are all templates, taking a parameter
      that defaults to <tt><a href="../refman/classmysqlpp_1_1Transaction.html">Transaction</a></tt>. This
      means that, by default, <code class="methodname">insertfrom()</code>
      wraps the entire operation in a SQL transaction, so that if
      any of the insertions fail, the database server rolls them all
      back. This prevents an error in the middle of the operation
      from leaving just part of the container&#8217;s data inserted
      in the database, which you usually don&#8217;t want any more
      than you&#8217;d want half a single row to be inserted.</p><p>There are good reasons why you might
      not want this. Perhaps the best reason is if the
      <code class="methodname">insertfrom()</code> call is to be part
      of a larger transaction. MySQL doesn&#8217;t support nested
      transactions, so the <code class="methodname">insertfrom()</code>
      call will fail if it tries to start one of its own. You can
      pass <code class="classname">NoTransactions</code> for the insert
      policy&#8217;s template parameter to make it suppress the
      transaction code.</p></div></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-modifying"></a>5.5.Modifying data</h3></div></div></div><p>It almost as easy to modify data with SSQLS as to add it. This
    is <code class="filename">examples/ssqls3.cpp</code>:</p><pre class="programlisting">#include "cmdline.h"
#include "printdata.h"
#include "stock.h"

#include &lt;iostream&gt;

using namespace std;

int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    mysqlpp::examples::CommandLine cmdline(argc, argv);
    if (!cmdline) {
        return 1;
    }

    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(mysqlpp::examples::db_name,
                cmdline.server(), cmdline.user(), cmdline.pass());

        // Build a query to retrieve the stock item that has Unicode
        // characters encoded in UTF-8 form.
        mysqlpp::Query query = con.query("select * from stock ");
        query &lt;&lt; "where item = " &lt;&lt; mysqlpp::quote &lt;&lt; "Nrnberger Brats";

        // Retrieve the row, throwing an exception if it fails.
        mysqlpp::StoreQueryResult res = query.store();
        if (res.empty()) {
            throw mysqlpp::BadQuery("UTF-8 bratwurst item not found in "
                    "table, run resetdb");
        }

        // Because there should only be one row in the result set,
        // there's no point in storing the result in an STL container.
        // We can store the first row directly into a stock structure
        // because one of an SSQLS's constructors takes a Row object.
        stock row = res[0];

        // Create a copy so that the replace query knows what the
        // original values are.
        stock orig_row = row;

        // Change the stock object's item to use only 7-bit ASCII, and
        // to deliberately be wider than normal column widths printed
        // by print_stock_table().
        row.item = "Nuerenberger Bratwurst";

        // Form the query to replace the row in the stock table.
        query.update(orig_row, row);

        // Show the query about to be executed.
        cout &lt;&lt; "Query: " &lt;&lt; query &lt;&lt; endl;

        // Run the query with execute(), since UPDATE doesn't return a
        // result set.
        query.execute();

        // Retrieve and print out the new table contents.
        print_stock_table(query);
    }
    catch (const mysqlpp::BadQuery&amp; er) {
        // Handle any query errors
        cerr &lt;&lt; "Query error: " &lt;&lt; er.what() &lt;&lt; endl;
        return -1;
    }
    catch (const mysqlpp::BadConversion&amp; er) {
        // Handle bad conversions
        cerr &lt;&lt; "Conversion error: " &lt;&lt; er.what() &lt;&lt; endl &lt;&lt;
                "\tretrieved data size: " &lt;&lt; er.retrieved &lt;&lt;
                ", actual size: " &lt;&lt; er.actual_size &lt;&lt; endl;
        return -1;
    }
    catch (const mysqlpp::Exception&amp; er) {
        // Catch-all for any other MySQL++ exceptions
        cerr &lt;&lt; "Error: " &lt;&lt; er.what() &lt;&lt; endl;
        return -1;
    }

    return 0;
}
</pre><p>Don&#8217;t forget to run <code class="filename">resetdb</code> after
    running the example.</p></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-comparing"></a>5.6.Storing SSQLSes in Associative Containers</h3></div></div></div><p>One of the requirements of STL&#8217;s associative
    containers on data stored in them is that the data type
    has to be less-than comparable. That is, it has to have
    an <code class="function">operator&lt;</code> defined.
    SSQLS does optionally give you this, as demonstrated in
    <code class="filename">examples/ssqls4.cpp</code>:</p><pre class="programlisting">#include "cmdline.h"
#include "printdata.h"
#include "stock.h"

#include &lt;iostream&gt;

using namespace std;

int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    mysqlpp::examples::CommandLine cmdline(argc, argv);
    if (!cmdline) {
        return 1;
    }

    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(mysqlpp::examples::db_name,
                cmdline.server(), cmdline.user(), cmdline.pass());

        // Retrieve all rows from the stock table and put them in an
        // STL set.  Notice that this works just as well as storing them
        // in a vector, which we did in ssqls1.cpp.  It works because
        // SSQLS objects are less-than comparable.
        mysqlpp::Query query = con.query("select * from stock");
        set&lt;stock&gt; res;
        query.storein(res);

        // Display the result set.  Since it is an STL set and we set up
        // the SSQLS to compare based on the item column, the rows will
        // be sorted by item.
        print_stock_header(res.size());
        set&lt;stock&gt;::iterator it;
        cout.precision(3);
        for (it = res.begin(); it != res.end(); ++it) {
            print_stock_row(it-&gt;item.c_str(), it-&gt;num, it-&gt;weight,
                    it-&gt;price, it-&gt;sDate);
        }

        // Use set's find method to look up a stock item by item name.
        // This also uses the SSQLS comparison setup.
        it = res.find(stock("Hotdog Buns"));
        if (it != res.end()) {
            cout &lt;&lt; endl &lt;&lt; "Currently " &lt;&lt; it-&gt;num &lt;&lt;
                    " hotdog buns in stock." &lt;&lt; endl;
        }
        else {
            cout &lt;&lt; endl &lt;&lt; "Sorry, no hotdog buns in stock." &lt;&lt; endl;
        }
    }
    catch (const mysqlpp::BadQuery&amp; er) {
        // Handle any query errors
        cerr &lt;&lt; "Query error: " &lt;&lt; er.what() &lt;&lt; endl;
        return -1;
    }
    catch (const mysqlpp::BadConversion&amp; er) {
        // Handle bad conversions
        cerr &lt;&lt; "Conversion error: " &lt;&lt; er.what() &lt;&lt; endl &lt;&lt;
                "\tretrieved data size: " &lt;&lt; er.retrieved &lt;&lt;
                ", actual size: " &lt;&lt; er.actual_size &lt;&lt; endl;
        return -1;
    }
    catch (const mysqlpp::Exception&amp; er) {
        // Catch-all for any other MySQL++ exceptions
        cerr &lt;&lt; "Error: " &lt;&lt; er.what() &lt;&lt; endl;
        return -1;
    }

    return 0;
}
</pre><p>The <code class="methodname">find()</code> call works because of
    the way the SSQLS was declared. It&#8217;s properly covered
    <a class="link" href="ssqls.html#ssqls-compare-init" title="5.2.SSQLS Comparison and Initialization">elsewhere</a>,
    but suffice it to say, the &#8220;1&#8221; in the
    declaration of <code class="classname">stock</code> <a class="link" href="ssqls.html#ssqls-retrieving" title="5.3.Retrieving data">above</a> tells it that only the
    first field needs to be checked in comparing two SSQLSes. In
    database terms, this makes it the primary key. Therefore, when
    searching for a match, our exemplar only had to have its first
    field populated.</p></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-table-name"></a>5.7.Changing the Table Name</h3></div></div></div><p>Another feature you might find a use for is changing the
    table name MySQL++ uses to build queries involving SSQLSes. By
    default, the database server table is assumed to have the same name
    as the SSQLS structure type. But if this is inconvenient, you can
    globally change the table name used in queries like this:</p><pre class="programlisting">
stock::table("MyStockData");</pre><p>It&#8217;s also possible to change the name of a table on
    a per-instance basis:</p><pre class="programlisting">
stock s;
s.instance_table("AlternateTable");</pre><p>This is useful when you have an SSQLS definition that is
    compatible with multiple tables, so the table name to use for
    each instance is different. This feature saves you from having
    to define a separate SSQLS for each table. It is also useful for
    mapping a class hierarchy onto a set of table definitions. The
    common SSQLS definition is the &#8220;superclass&#8221; for a
    given set of tables.</p><p>Strictly speaking, you only need to use this feature in
    multithreaded programs. Changing the static table name before
    using each instance is safe if all changes happen within a single
    thread. That said, it may still be convenient to change the name of
    the table for an SSQLS instance in a single-threaded program if it
    gets used for many operations over an extended span of code.</p></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-in-header"></a>5.8.Using an SSQLS in Multiple Modules</h3></div></div></div><p>It&#8217;s convenient to define an SSQLS in a header file so
    you can use it in multiple modules. You run into a bit of a
    problem, though, because each SSQLS includes a few static data
    members to hold information common to all structures of that
    type. (The table name and the list of field names.) When you
    <span class="command"><strong>#include</strong></span> that header in more than one module,
    you get a multiply-defined symbol error at link time.</p><p>The way around this is to define the preprocessor macro
    <code class="varname">MYSQLPP_SSQLS_NO_STATICS</code> in <span class="emphasis"><em>all but
    one</em></span> of the modules that use the header definining the
    SSQLS. When this macro is defined, it suppresses the static data
    members in any SSQLS defined thereafter.</p><p>Imagine we have a file <code class="filename">my_ssqls.h</code> which
    includes a <code class="function">sql_create_N</code> macro call to define an
    SSQLS, and that that SSQLS is used in at least two modules. One
    we&#8217;ll call <code class="filename">foo.cpp</code>, and we&#8217;ll say
    it&#8217;s just a user of the SSQLS; it doesn&#8217;t
    &#8220;own&#8221; it. Another of the modules,
    <code class="filename">my_ssqls.cpp</code> uses the SSQLS more heavily, so
    we&#8217;ve called it the owner of the SSQLS. If there aren&#8217;t
    very many modules, this works nicely:</p><pre class="programlisting">
// File foo.cpp, which just uses the SSQLS, but doesn&#8217;t "own" it:
#define MYSQLPP_SSQLS_NO_STATICS
#include "my_ssqls.h"</pre><pre class="programlisting">
// File my_ssqls.cpp, which owns the SSQLS, so we just #include it directly
#include "my_ssqls.h"</pre><p>If there are many modules that need the SSQLS, adding all
    those <span class="command"><strong>#defines</strong></span> can be a pain. In that case,
    it&#8217;s easier if you flip the above pattern on its head:</p><pre class="programlisting">
// File my_ssqls.h:
#if !defined(EXPAND_MY_SSQLS_STATICS)
#   define MYSQLPP_SSQLS_NO_STATICS
#endif
sql_create_X(Y, Z....) // the SSQLS definition</pre><pre class="programlisting">
// File foo.cpp, a mere user of the SSQLS:
#include "my_ssqls.h"</pre><pre class="programlisting">
// File my_ssqls.cpp, which owns the SSQLS:
#define EXPAND_MY_SSQLS_STATICS
#include "my_ssqls.h"</pre></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-internals"></a>5.9.Harnessing SSQLS Internals</h3></div></div></div><p>The <span class="symbol">sql_create</span> macros define several methods
    for each SSQLS. These methods are mostly for use within the library,
    but some of them are useful enough that you might want to harness
    them for your own ends. Here is some pseudocode showing how the most
    useful of these methods would be defined for the
    <span class="structname">stock</span> structure used in all the
    <code class="filename">ssqls*.cpp</code> examples:</p><pre class="programlisting">
// Basic form
template &lt;class Manip&gt;   
stock_value_list&lt;Manip&gt; value_list(cchar *d = ",",
  Manip m = mysqlpp::quote) const;  

template &lt;class Manip&gt;   
stock_field_list&lt;Manip&gt; field_list(cchar *d = ",",   
  Manip m = mysqlpp::do_nothing) const;  

template &lt;class Manip&gt;   
stock_equal_list&lt;Manip&gt; equal_list(cchar *d = ",",
  cchar *e = " = ", Manip m = mysqlpp::quote) const;  


// Boolean argument form
template &lt;class Manip&gt;
stock_cus_value_list&lt;Manip&gt; value_list([cchar *d, [Manip m,] ]   
  bool i1, bool i2 = false, ... , bool i5 = false) const;  

// List form  
template &lt;class Manip&gt;
stock_cus_value_list&lt;Manip&gt; value_list([cchar *d, [Manip m,] ]  
  stock_enum i1, stock_enum i2 = stock_NULL, ...,
  stock_enum i5 = stock_NULL) const;  

// Vector form  
template &lt;class Manip&gt;
stock_cus_value_list&lt;Manip&gt; value_list([cchar *d, [Manip m,] ]  
  vector&lt;bool&gt; *i) const;  

...Plus the obvious equivalents for field_list() and equal_list()</pre><p>Rather than try to learn what all of these methods do at
    once, let&#8217;s ease into the subject. Consider this code:</p><pre class="programlisting">
stock s("Dinner Rolls", 75, 0.95, 0.97, sql_date("1998-05-25"));   
cout &lt;&lt; "Value list: " &lt;&lt; s.value_list() &lt;&lt; endl;  
cout &lt;&lt; "Field list: " &lt;&lt; s.field_list() &lt;&lt; endl;  
cout &lt;&lt; "Equal list: " &lt;&lt; s.equal_list() &lt;&lt; endl;</pre><p>That would produce something like:</p><pre class="programlisting">
Value list: 'Dinner Rolls&#8217;,75,0.95,0.97,'1998-05-25'   
Field list: item,num,weight,price,sdate  
Equal list: item = 'Dinner Rolls&#8217;,num = 75,weight = 0.95, price = 0.97,sdate = '1998-05-25'</pre><p>That is, a &#8220;value list&#8221; is a list of data member
    values within a particular SSQLS instance, a &#8220;field
    list&#8221; is a list of the fields (columns) within that SSQLS, and
    an &#8220;equal list&#8221; is a list in the form of an SQL equals
    clause.</p><p>Just knowing that much, it shouldn&#8217;t surprise you to
    learn that <code class="methodname">Query::insert()</code> is implemented
    more or less like this:</p><pre class="programlisting">
*this &lt;&lt; "INSERT INTO " &lt;&lt; v.table() &lt;&lt; " (" &lt;&lt; v.field_list() &lt;&lt;
    ") VALUES (" &lt;&lt; v.value_list() &lt;&lt; ")";</pre><p>where &#8216;v&#8217; is the SSQLS you&#8217;re asking the
    Query object to insert into the database.</p><p>Now let&#8217;s look at a complete example, which uses one of
    the more complicated forms of <code class="methodname">equal_list()</code>.
    This example builds a query with fewer hard-coded strings than the
    most obvious technique requires, which makes it more robust in the
    face of change. Here is
    <code class="filename">examples/ssqls5.cpp</code>:</p><pre class="programlisting">#include "cmdline.h"
#include "printdata.h"
#include "stock.h"

#include &lt;iostream&gt;
#include &lt;vector&gt;

using namespace std;

int
main(int argc, char *argv[])
{
    // Get database access parameters from command line
    mysqlpp::examples::CommandLine cmdline(argc, argv);
    if (!cmdline) {
        return 1;
    }

    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(mysqlpp::examples::db_name,
                cmdline.server(), cmdline.user(), cmdline.pass());

        // Get all the rows in the stock table.
        mysqlpp::Query query = con.query("select * from stock");
        vector&lt;stock&gt; res;
        query.storein(res);

        if (res.size() &gt; 0) {
            // Build a select query using the data from the first row
            // returned by our previous query.
            query &lt;&lt; "select * from stock where " &lt;&lt;
                    res[0].equal_list(" and ", stock_weight, stock_price);

            // Display the finished query.
            cout &lt;&lt; "Custom query:\n" &lt;&lt; query &lt;&lt; endl;
        }
    }
    catch (const mysqlpp::BadQuery&amp; er) {
        // Handle any query errors
        cerr &lt;&lt; "Query error: " &lt;&lt; er.what() &lt;&lt; endl;
        return -1;
    }
    catch (const mysqlpp::BadConversion&amp; er) {
        // Handle bad conversions
        cerr &lt;&lt; "Conversion error: " &lt;&lt; er.what() &lt;&lt; endl &lt;&lt;
                "\tretrieved data size: " &lt;&lt; er.retrieved &lt;&lt;
                ", actual size: " &lt;&lt; er.actual_size &lt;&lt; endl;
        return -1;
    }
    catch (const mysqlpp::Exception&amp; er) {
        // Catch-all for any other MySQL++ exceptions
        cerr &lt;&lt; "Error: " &lt;&lt; er.what() &lt;&lt; endl;
        return -1;
    }

    return 0;
}
</pre><p>This example uses the list form of
    <code class="methodname">equal_list()</code>. The arguments
    <code class="varname">stock_weight</code> and <code class="varname">stock_price</code>
    are enum values equal to the position of these columns within the
    <span class="structname">stock</span> table.  <span class="symbol">sql_create_#</span>
    generates this enum for you automatically.</p><p>The boolean argument form of that
    <code class="methodname">equal_list()</code> call would look like
    this:</p><pre class="programlisting">
query &lt;&lt; "select * from stock where " &lt;&lt;
    res[0].equal_list(" and ", false, false, true, true, false);</pre><p>It&#8217;s a little more verbose, as you can see. And if you want
    to get really complicated, use the vector form:</p><pre class="programlisting">
vector&lt;bool&gt; v(5, false);
v[stock_weight] = true;
v[stock_price] = true;
query &lt;&lt; "select * from stock where " &lt;&lt;
    res[0].equal_list(" and ", v);</pre><p>This form makes the most sense if you are building many other
    queries, and so can re-use that vector object.</p><p>Many of these methods accept manipulators and custom
    delimiters. The defaults are suitable for building SQL queries, but
    if you&#8217;re using these methods in a different context, you may
    need to override these defaults. For instance, you could use these
    methods to dump data to a text file using different delimiters and
    quoting rules than SQL.</p><p>At this point, we&#8217;ve seen all the major aspects of the
    SSQLS feature. The final sections of this chapter look at some of
    the peripheral aspects.</p></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-field-names"></a>5.10.Having Different Field Names in C++ and SQL</h3></div></div></div><p>There&#8217;s a more advanced SSQLS creation macro,
    which all the others are built on top of. Currently, the only
    feature it adds over what&#8217;s described above is that it
    lets you name your SSQLS fields differently from the names
    used by the database server. Perhaps you want to use <a class="ulink" href="http://en.wikipedia.org/wiki/Hungarian_notation" target="_top">Hungarian
    notation</a> in your C++ program without changing the SQL
    database schema:</p><pre class="programlisting">
sql_create_complete_5(stock, 1, 5,   
    mysqlpp::sql_char, m_sItem, "item",
    mysqlpp::sql_bigint, m_nNum, "num",
    mysqlpp::sql_double, m_fWeight, "weight",
    mysqlpp::sql_decimal, m_fPrice, "price",
    mysqlpp::sql_date, m_Date, "sdate")</pre><p>Note that you don&#8217;t have to use this mechanism if the
    only difference in your SQL and C++ field names is case. SSQLS
    field name lookups are case-insensitive as of MySQL++ 3.1. You can
    see this in the examples: some parts of the code deliberately refer
    to the <code class="classname">stock.sdate</code> sample table field as
    <code class="classname">stock.sDate</code> to exercise this feature.</p></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-pretty"></a>5.11.Expanding SSQLS Macros</h3></div></div></div><p>If you ever need to see the code that a given
    SSQLS declaration expands out to, use the utility
    <code class="filename">doc/ssqls-pretty</code>, like so:</p><pre class="programlisting">
doc/ssqls-pretty &lt; myprog.cpp | less</pre><p>This Perl script locates the first SSQLS declaration in that
    file, then uses the C++ preprocessor to expand that macro. (The
    script assumes that your system&#8217;s preprocessor is called
    <code class="filename">cpp</code>, and that its command line interface
    follows Unix conventions.)</p><p>If you run it from the top MySQL++ directory, as shown above,
    it will use the header files in the distribution&#8217;s
    <code class="filename">lib</code> subdirectory. Otherwise, it assumes the
    MySQL++ headers are in their default location,
    <code class="filename">/usr/include/mysql++</code>. If you want to use
    headers in some other location, you&#8217;ll need to change the
    directory name in the <span class="command"><strong>-I</strong></span> flag at the top of the
    script.</p></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-customization"></a>5.12.Customizing the SSQLS Mechanism</h3></div></div></div><p>The SSQLS header <code class="filename">ssqls.h</code>
    is automatically generated by the Perl script
    <code class="filename">ssqls.pl</code>. Although it is possible to
    change this script to get additional functionality, most of
    the time it&#8217;s better to just derive a custom class from
    the generated SSQLS to add functionality to it. (See the <a class="link" href="ssqls.html#ssqls-derivation" title="5.13.Deriving from an SSQLS">next section</a> to see how to do
    this correctly.)</p><p>That said, <code class="filename">ssqls.pl</code> does have a few
    configurables you might want to tweak.</p><p>The first configurable value sets the maximum number of
    data members allowed in an SSQLS. This is discussed elsewhere,
    in <a class="xref" href="configuration.html#max-fields" title="8.2.The Maximum Number of Fields Allowed">Section8.2, &#8220;The Maximum Number of Fields Allowed&#8221;</a>. Beware the warnings there about
    increasing this value too much.</p><p>The second configurable is the default floating point
    precision used for comparison. As described above (<a class="xref" href="ssqls.html#ssqls-compare-init" title="5.2.SSQLS Comparison and Initialization">Section5.2, &#8220;SSQLS Comparison and Initialization&#8221;</a>) SSQLSes can be compared for
    equality. The only place this is tricky is with floating-point
    numbers, since rounding errors can make two &#8220;equal&#8221;
    values compare as distinct. This property of floating-point numbers
    means we almost never want to do exact comparison. MySQL++ lets
    you specify the precision you want it to use. If the difference
    between two values is under a given threshold, MySQL++ considers
    the values equal. The default threshold is 0.00001. This threshold
    works well for &#8220;human&#8221; scale values, but because of the
    way floating-point numbers work, it can be wildly inappropriate for
    very large or very small quantities like those used in scientific
    applications.</p><p>There are actually two ways to change this
    threshold. If you need a different system-wide default,
    edit <code class="filename">ssqls.pl</code> and change the
    <code class="varname">$fp_min_delta</code> variable at the top of the file,
    then rebuild <code class="filename">ssqls.h</code> as described below. If
    you need different thresholds per file or per project, it&#8217;s
    better to set the C macro <code class="varname">MYSQLPP_FP_MIN_DELTA</code>
    instead. The Perl variable sets this macro&#8217;s
    default; if you give a different value before #including
    <code class="filename">ssqls.h</code>, it will use that instead.</p><p>To rebuild <code class="filename">ssqls.h</code> after changing
    <code class="filename">ssqls.pl</code>, you&#8217;ll need a Perl
    interpreter.  The only modern Unixy system I&#8217;m aware
    of where Perl isn&#8217;t installed by default is Cygwin, and
    it&#8217;s just a <code class="filename">setup.exe</code> choice away
    there. You&#8217;ll probably only have to download and install a
    Perl interpreter if you&#8217;re on Windows and don&#8217;t want
    to use Cygwin.</p><p>If you&#8217;re on a system that uses autoconf, building
    MySQL++ automatically updates <code class="filename">ssqls.h</code>
    any time <code class="filename">ssqls.pl</code> changes. Otherwise,
    you&#8217;ll need to run the Perl interpreter by hand:</p><pre class="screen">c:\mysql++&gt; cd lib
c:\lib&gt; perl ssqls.pl</pre></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-derivation"></a>5.13.Deriving from an SSQLS</h3></div></div></div><p>Specialized SQL Structures make good base
    classes. They&#8217;re simple, and have few requirements on any
    class that derives from them. There are some gotchas to look out
    for, however.</p><p>Consider this:</p><pre class="programlisting">
sql_create_2(
  Base, 1, 2,
  mysqlpp::sql_varchar, a,
  mysqlpp::sql_int, b
);

class Derived : public Base
{
public:
  // constructor
  Derived(mysqlpp::sql_varchar _a, mysqlpp::sql_int _b) :
  Base(_a, _b)
  {
  }

  // functionality added to the SSQLS through inheritance
  bool do_something_interesting(int data);
};</pre><p>We&#8217;ve derived a class from an SSQLS in order to add
    a method to it. Easy, right?</p><p>Sadly, too easy. The code has a rather large flaw which makes
    our derived class unusable as an SSQLS. In C++, if a derived class
    has a function of the same name as one in the base class, the
    base class versions of that function are all hidden by those in
    the derived class. This applies to constructors, too: an SSQLS
    defines several constructors, but our derived class defines
    only one, causing that one to hide all of the ones in the base
    class. Many of the MySQL++ mechanisms that use SSQLSes rely on
    having these contructors, so our <code class="classname">Derived</code>
    above is-not-a <code class="classname">Base</code>, and so it isn&#8217;t
    an SSQLS. If you try to use <code class="classname">Derived</code>
    as an SSQLS, you&#8217;ll get compiler errors wherever MySQL++
    tries to access one of these other constructors.</p><p>There&#8217;s another minor flaw, as well. Our lone constructor
    above takes its parameters by value, but the corresponding
    constructor in the SSQLS takes them by const reference. Our derived
    class has technically hidden a fourth base class constructor this
    way, but this particular case is more a matter of efficiency than
    correctness. Code that needs the full-creation constructor will
    still work with our code above, but passing stringish types like
    <code class="classname">sql_varchar</code> by value instead of by const
    reference is inefficient.</p><p>This is the corrected version of the above code:</p><pre class="programlisting">
sql_create_2(
  Base, 1, 2,
  mysqlpp::sql_varchar, a,
  mysqlpp::sql_int, b
);

class Derived : public Base
{
public:
  // default constructor<a href="#ftn.idp140589485418344" class="footnote" name="idp140589485418344"><sup class="footnote">[14]</sup></a>
  Derived() :
  Base()
  {
  }

  // for-comparison constructor<a href="#ftn.idp140589485419240" class="footnote" name="idp140589485419240"><sup class="footnote">[15]</sup></a>
  Derived(const mysqlpp::sql_varchar&amp; _a) :
  Base(_a)
  {
  }

  // full creation constructor
  Derived(const mysqlpp::sql_varchar&amp; _a, const mysqlpp::sql_int&amp; _b) :
  Base(_a, _b)
  {
  }

  // population constructor<a href="#ftn.idp140589485421640" class="footnote" name="idp140589485421640"><sup class="footnote">[16]</sup></a>
  Derived(const mysqlpp::Row&amp; row) :
  Base(row)
  {
  }

  // functionality added to the SSQLS through inheritance
  bool do_something_interesting(int data);
};</pre><p>Now <code class="classname">Derived</code> is-an SSQLS.</p><p>You might wonder if you can use protected inheritance
    above to redefine the SSQLS&#8217;s public interface. For
    instance, OO purists might object to the public data members
    in an SSQLS. You could encapsulate these public data members
    in the derived class by using protected inheritance, exposing
    access to the base class&#8217;s data members with public
    accessor methods. The problem with this is that each SSQLS has
    <span class="emphasis"><em>dozens</em></span> of public member functions. These are
    needed by MySQL++ internals, so unless you re-exposed all of them
    as we did with the constructors above, you&#8217;d again have an
    SSQLS derivative that is-not-an SSQLS. Simply put, only public
    inheritance is practical with SSQLSes.</p></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-blob"></a>5.14.SSQLS and BLOB Columns</h3></div></div></div><p>It takes special care to use SSQLS with BLOB columns.
    It&#8217;s safest to declare the SSQLS field as of type
    <code class="classname">mysqlpp::sql_blob</code>. This is currently a
    typedef alias for <tt><a href="../refman/classmysqlpp_1_1String.html">String</a></tt>, which is the form the data is in just
    before the SSQLS mechanism populates the structure. Thus, when the
    data is copied from the internal MySQL++ data structures into your
    SSQLS, you get a direct copy of the <code class="classname">String</code>
    object&#8217;s contents, without interference.</p><p>Because C++ strings handle binary data just fine, you might
    think you can use <code class="classname">std::string</code> instead of
    <code class="classname">sql_blob</code>, but the current design of
    <code class="classname">String</code> converts to


    <code class="classname">std::string</code> via a C string. As a result, the
    BLOB data is truncated at the first embedded null character during
    population of the SSQLS. There&#8217;s no way to fix that without
    completely redesigning either <code class="classname">String</code> or the
    SSQLS mechanism.</p><p>The <code class="classname">sql_blob</code> typedef may be changed to
    alias a different type in the future, so using it instead of
    <code class="classname">String</code> ensures that your code tracks these
    library changes automatically. Besides,
    <code class="classname">String</code> is only intended to be an internal
    mechanism within MySQL++. The only reason the layering is so thin
    here is because it&#8217;s the only way to prevent BLOB data from
    being corrupted while avoiding that looming redesign effort.</p><p>You can see this technique in action in the
    <code class="filename">cgi_jpeg</code> example:</p><pre class="programlisting">#include "cmdline.h"
#include "images.h"

#define CRLF            "\r\n"
#define CRLF2           "\r\n\r\n"

int
main(int argc, char* argv[])
{
    // Get database access parameters from command line if present, else
    // use hard-coded values for true CGI case.
    mysqlpp::examples::CommandLine cmdline(argc, argv, "root",
            "nunyabinness");
    if (!cmdline) {
        return 1;
    }

    // Parse CGI query string environment variable to get image ID
    unsigned int img_id = 0;
    char* cgi_query = getenv("QUERY_STRING");
    if (cgi_query) {
        if ((strlen(cgi_query) &lt; 4) || memcmp(cgi_query, "id=", 3)) {
            std::cout &lt;&lt; "Content-type: text/plain" &lt;&lt; std::endl &lt;&lt; std::endl;
            std::cout &lt;&lt; "ERROR: Bad query string" &lt;&lt; std::endl;
            return 1;
        }
        else {
            img_id = atoi(cgi_query + 3);
        }
    }
    else {
        std::cerr &lt;&lt; "Put this program into a web server's cgi-bin "
                "directory, then" &lt;&lt; std::endl;
        std::cerr &lt;&lt; "invoke it with a URL like this:" &lt;&lt; std::endl;
        std::cerr &lt;&lt; std::endl;
        std::cerr &lt;&lt; "    http://server.name.com/cgi-bin/cgi_jpeg?id=2" &lt;&lt;
                std::endl;
        std::cerr &lt;&lt; std::endl;
        std::cerr &lt;&lt; "This will retrieve the image with ID 2." &lt;&lt; std::endl;
        std::cerr &lt;&lt; std::endl;
        std::cerr &lt;&lt; "You will probably have to change some of the #defines "
                "at the top of" &lt;&lt; std::endl;
        std::cerr &lt;&lt; "examples/cgi_jpeg.cpp to allow the lookup to work." &lt;&lt;
                std::endl;
        return 1;
    }

    // Retrieve image from DB by ID
    try {
        mysqlpp::Connection con(mysqlpp::examples::db_name,
                cmdline.server(), cmdline.user(), cmdline.pass());
        mysqlpp::Query query = con.query();
        query &lt;&lt; "SELECT * FROM images WHERE id = " &lt;&lt; img_id;
        mysqlpp::StoreQueryResult res = query.store();
        if (res &amp;&amp; res.num_rows()) {
            images img = res[0];
            if (img.data.is_null) {
                std::cout &lt;&lt; "Content-type: text/plain" &lt;&lt; CRLF2;
                std::cout &lt;&lt; "No image content!" &lt;&lt; CRLF;
            }
            else {
                std::cout &lt;&lt; "X-Image-Id: " &lt;&lt; img_id &lt;&lt; CRLF; // for debugging
                std::cout &lt;&lt; "Content-type: image/jpeg" &lt;&lt; CRLF;
                std::cout &lt;&lt; "Content-length: " &lt;&lt;
                        img.data.data.length() &lt;&lt; CRLF2;
                std::cout &lt;&lt; img.data;
            }
        }
        else {
            std::cout &lt;&lt; "Content-type: text/plain" &lt;&lt; CRLF2;
            std::cout &lt;&lt; "ERROR: No image with ID " &lt;&lt; img_id &lt;&lt; CRLF;
        }
    }
    catch (const mysqlpp::BadQuery&amp; er) {
        // Handle any query errors
        std::cout &lt;&lt; "Content-type: text/plain" &lt;&lt; CRLF2;
        std::cout &lt;&lt; "QUERY ERROR: " &lt;&lt; er.what() &lt;&lt; CRLF;
        return 1;
    }
    catch (const mysqlpp::Exception&amp; er) {
        // Catch-all for any other MySQL++ exceptions
        std::cout &lt;&lt; "Content-type: text/plain" &lt;&lt; CRLF2;
        std::cout &lt;&lt; "GENERAL ERROR: " &lt;&lt; er.what() &lt;&lt; CRLF;
        return 1;
    }

    return 0;
}
</pre></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-vc2003"></a>5.15.SSQLS and Visual C++ 2003</h3></div></div></div><p>SSQLS works on all platforms supported by MySQL++ except for
    Visual C++ 2003. (Because the rest of MySQL++ works just fine with
    Visual C++ 2003, we haven&#8217;t removed this platform from the
    supported list entirely.)</p><p>If you do need SSQLS and are currently on Visual C++ 2003, you
    have these options:</p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>The simplest option is to upgrade to a newer
      version of Visual C++. The compiler limitations that break SSQLS
      are all fixed in Visual C++ 2005 and newer. <a class="ulink" href="http://www.microsoft.com/express/vc/" target="_top">Visual C++
      Express</a> is free and is apparently here to stay; coupled
      with the free <a class="ulink" href="http://wxwidgets.org/" target="_top">wxWidgets</a>
      library, it lacks little compared to Visual C++ Professional.  A
      bonus of using wxWidgets is that it&#8217;s cross-platform and
      better-supported than MFC.</p></li><li class="listitem"><p>If you can&#8217;t upgrade your compiler, you may
      be able to downgrade to MySQL++ v2.<span class="emphasis"><em>x</em></span>.  The
      SSQLS feature in these older versions worked with Visual C++ 2003,
      but didn&#8217;t let you use a given SSQLS in more than one module
      in a program. If you can live with that limitation and have a Perl
      interpreter on your system, you can re-generate
      <code class="filename">lib/ssqls.h</code> to remove the multiple-module
      SSQLS support. To do this, you run the command <span class="command"><strong>perl
      ssqls.pl -v</strong></span> from within MySQL++&#8217;s
      <code class="filename">lib</code> subdirectory before you build and install
      the library.</p></li><li class="listitem"><p>There&#8217;s <a class="ulink" href="https://tangentsoft.com/mysqlpp/wiki?name=SSQLSv2+Design" target="_top">a
      plan</a> to replace the current SSQLS mechanism with an
      entirely new code base. Although this is being done primarily to
      get new features that are too difficult to add within the current
      design, it also means we&#8217;ll have the chance to test
      step-by-step along the way that we don&#8217;t reintroduce code
      that Visual C++ 2003 doesn&#8217;t support. This may happen
      without you doing anything, but if there&#8217;s someone on the
      team who cares about this, that will naturally increase the
      chances that it does happen.</p></li></ol></div></div><div class="footnotes"><br><hr style="width:100; text-align:left;margin-left: 0"><div id="ftn.idp140589776381800" class="footnote"><p><a href="#idp140589776381800" class="para"><sup class="para">[13] </sup></a>Programs built against versions of MySQL++
    prior to 3.0 would crash at almost any mismatch between the database
    schema and the SSQLS definition. It&#8217;s no longer necessary to
    keep the data design in lock-step between the client and database
    server. A mismatch can result in data loss, but not a
    crash.</p></div><div id="ftn.idp140589485418344" class="footnote"><p><a href="#idp140589485418344" class="para"><sup class="para">[14] </sup></a>needed by mechanisms like <code class="methodname">Query::storein()</code>; anything using an STL container, which usually require default ctors for contained data structures</p></div><div id="ftn.idp140589485419240" class="footnote"><p><a href="#idp140589485419240" class="para"><sup class="para">[15] </sup></a>takes the <em class="parameter"><code>COMPCOUNT</code></em> subset of the SSQLS&#8217;s data members, used for making comparison exemplars, used with <code class="methodname">Query::update()</code> and similar mechanisms; see <a class="xref" href="ssqls.html#sql_create" title="5.1.sql_create">Section5.1, &#8220;sql_create&#8221;</a> for more on <em class="parameter"><code>COMPCOUNT</code></em></p></div><div id="ftn.idp140589485421640" class="footnote"><p><a href="#idp140589485421640" class="para"><sup class="para">[16] </sup></a>used in taking raw row data from a SQL result set and converting it to SSQLS form</p></div></div></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="tquery.html">Prev</a></td><td width="20%" align="center"></td><td width="40%" align="right"><a accesskey="n" href="unicode.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">4.Template Queries</td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top">6.Using Unicode with MySQL++</td></tr></table></div></body></html>