File: ssqls.html

package info (click to toggle)
mysql%2B%2B 3.0.0-1
links: PTS
area: main
in suites: lenny
size: 10,328 kB
ctags: 9,487
sloc: cpp: 33,486; sh: 3,091; perl: 809; makefile: 683
file content (526 lines) | stat: -rw-r--r-- 36,651 bytes
<html><head><meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>5.Specialized SQL Structures</title><link rel="stylesheet" href="tangentsoft.css" type="text/css"><meta name="generator" content="DocBook XSL Stylesheets V1.69.1"><link rel="start" href="index.html" title="MySQL++ v3.0.0 User Manual"><link rel="up" href="index.html" title="MySQL++ v3.0.0 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" lang="en"><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. Because
  of the extra functionality that this feature builds into these
  structures, MySQL++ can populate them automatically when retrieving
  data from the database; with queries returning many records, you can
  ask MySQL++ to populate an STL container of your SSQLS records with
  the results. When updating the database, MySQL++ can use SSQLS
  structures to match existing data, and it can insert SSQLS structures
  directly into the database.</p><p>You define an SSQLS using one of several macros defined in
  <code class="filename">ssqls.h</code>. The following sections will discuss each
  macro type separately, beginning with the easiest and most generally
  useful.</p><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="sql_create"></a>5.1.sql_create</h3></div></div></div><p>This is the most basic sort of SSQLS declaration:</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 creates a C++ structure called
    <code class="classname">stock</code> containing six member
    variables (<code class="varname">item</code>, <code class="varname">num</code>,
    <code class="varname">weight</code>, <code class="varname">price</code>,
    <code class="varname">sdate</code>, and <code class="varname">description</code>),
    along with some constructors and other useful member
    functions.</p><p>The parameter before each field name is the C++ data type
    that will be used to hold that value in the SSQLS. MySQL++ has
    a <span class="type">sql_*</span> typedef for almost every data type MySQL
    understands.<sup>[<a name="id2854985" href="#ftn.id2854985">10</a>]</sup> While it&#8217;s possible to use
    some regular C and C++ data types here, it&#8217;s safer to use
    the ones MySQL++ defines, as they&#8217;re likely to be a better
    match to the types used by the database server. Plus, if you use
    the predefined types, you are assured that MySQL++ knows how to
    do the data conversions between the C++ and SQL type systems. If
    you use other data types and the C++ compiler can&#8217;t convert
    it to one MySQL++ already understands, MySQL++ will throw a
    <tt><a href="../refman/classmysqlpp_1_1TypeLookupFailed.html">TypeLookupFailed</a></tt> exception. The
    <span class="type">sql_*</span> types are defined in MySQL++&#8217;s
    <code class="filename">sql_types.h</code> header. The naming scheme is
    easy to learn when you know the SQL data type names.</p><p>Another thing you&#8217;ll notice above is the type of the
    last column. We&#8217;ve wrapped it in MySQL++&#8217;s <tt><a href="../refman/classmysqlpp_1_1Null.html">Null</a></tt> template, which enables it to take a
    SQL null value in addition to the values the base data type allows.
    For more on this topic, see <a href="tutorial.html#sql-null" title="3.9.Handling SQL Nulls">Section3.9, &#8220;Handling SQL Nulls&#8221;</a>.</p><p>One of the generated constructors takes a reference to
    a <tt><a href="../refman/classmysqlpp_1_1Row.html">Row</a></tt>, allowing you to easily
    populate a vector of stocks like so:</p><pre class="programlisting">
vector&lt;stock&gt; result; 
query.storein(result);</pre><p>MySQL++ takes care of mapping result set data to SSQLS
    fields. The SSQLS doesn&#8217;t have to have the same number
    of fields as the result set, and the order of fields in the
    result set doesn&#8217;t have to match the order of fields in
    the SSQLS. Fields in the result set that don&#8217;t exist in the
    SSQLS are just quietly dropped, and fields in the SSQLS for which
    there is no data in the result get set to a default value.</p><p>The general format of this set of macros 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" lang="en"><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><code class="varname">sql_create_</code><span class="emphasis"><em>x</em></span> 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><span class="emphasis"><em>x</em></span> 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><span class="emphasis"><em>x</em></span> 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_5(stock, 1, 2,
    mysqlpp::sql_char, item,
    mysqlpp::sql_bigint, num,         
    mysqlpp::sql_double, weight,  
    mysqlpp::sql_decimal, price,  
    mysqlpp::sql_date, sdate)
    
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" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-extra-features"></a>5.3.Additional Features of Specialized SQL
    Structures</h3></div></div></div><p>Up to this point, we haven&#8217;t been using all of the
    features in the SSQLS structures we&#8217;ve been generating.  What
    else can we do with SSQLSes? Consider this:</p><pre class="programlisting">
query.insert(s);</pre><p>This does exactly what you think it does: it builds
    an <span><strong class="command">INSERT</strong></span> query to insert the contents
    of <code class="varname">s</code> into the database. You have only to
    call <code class="methodname">query::execute()</code> to actually
    insert it. This is possible because SSQLSes have functions that
    <code class="classname">Query</code> can call to get the list of fields
    and such, which it uses to build the <span><strong class="command">INSERT</strong></span>
    query. <code class="methodname">query::update()</code> and
    <code class="methodname">query::replace()</code> also rely on these
    features.</p><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. The simplest way this can happen is if the
    tables all have identical definitions; it 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" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-in-header"></a>5.4.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><strong class="command">#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'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><strong class="command">#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" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-internals"></a>5.5.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',75,0.95,0.97,'1998-05-25'   
Field list: item,num,weight,price,sdate  
Equal list: item = 'Dinner Rolls',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
    const char* db = 0, *server = 0, *user = 0, *pass = "";
    if (!parse_command_line(argc, argv, &amp;db, &amp;server, &amp;user, &amp;pass)) {
        return 1;
    }

    try {
        // Establish the connection to the database server.
        mysqlpp::Connection con(db, server, user, 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><span class="emphasis"><em>x</em></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" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-field-names"></a>5.6.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 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, item, "m_sItem",
    mysqlpp::sql_bigint, num, "m_nNum",
    mysqlpp::sql_double, weight, "m_fWeight",
    mysqlpp::sql_decimal, price, "m_fPrice",
    mysqlpp::sql_date, sdate, "m_Date")</pre></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-pretty"></a>5.7.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><strong class="command">-I</strong></span> flag at the top of the
    script.</p></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-customization"></a>5.8.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 stock SSQLS and add your additional
    functionality that way.</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. It&#8217;s 25 out of the box, set by
    the <code class="varname">$max_data_members</code> variable at the top of
    <code class="filename">ssqls.pl</code>. Beware, making this value larger
    increases the size of <code class="filename">ssqls.h</code> exponentially;
    this will increase compile time, and can even expose limits in your
    compiler. Before you increase this value, take a good, hard look at
    your database schema and ask if it&#8217;s really the best design.
    Almost always, having so many columns is a sign that you need to
    refactor the table.</p><p>The second configurable is the default floating point
    precision used for comparison. As described above (<a 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. Since exact comparison makes no sense
    with floating-point values, 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" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-blob"></a>5.9.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 &lt;mysql++.h&gt;
#include &lt;ssqls.h&gt;

#define IMG_DATABASE    "mysql_cpp_data"
#define IMG_HOST        "localhost"
#define IMG_USER        "root"
#define IMG_PASSWORD    "nunyabinness"

sql_create_2(images,
    1, 2,
    mysqlpp::sql_int_unsigned, id,
    mysqlpp::sql_blob, data)

int main()
{
    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;
    }

    try {
        mysqlpp::Connection con(IMG_DATABASE, IMG_HOST, IMG_USER,
                IMG_PASSWORD);
        mysqlpp::Query query = con.query();
        query &lt;&lt; "SELECT * FROM images WHERE id = " &lt;&lt; img_id;
        mysqlpp::UseQueryResult res = query.use();
        if (res) {
            images img = res.fetch_row();
            std::cout &lt;&lt; "Content-type: image/jpeg" &lt;&lt; std::endl;
            std::cout &lt;&lt; "Content-length: " &lt;&lt; img.data.length() &lt;&lt; "\n\n";
            std::cout &lt;&lt; img.data;
        }
        else {
            std::cout &lt;&lt; "Content-type: text/plain" &lt;&lt; std::endl &lt;&lt; std::endl;
            std::cout &lt;&lt; "ERROR: No such image with ID " &lt;&lt; img_id &lt;&lt; std::endl;
        }
    }
    catch (const mysqlpp::BadQuery&amp; er) {
        // Handle any query errors
        std::cout &lt;&lt; "Content-type: text/plain" &lt;&lt; std::endl &lt;&lt; std::endl;
        std::cout &lt;&lt; "QUERY ERROR: " &lt;&lt; er.what() &lt;&lt; std::endl;
        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; std::endl &lt;&lt; std::endl;
        std::cout &lt;&lt; "GENERAL ERROR: " &lt;&lt; er.what() &lt;&lt; std::endl;
        return 1;
    }

    return 0;
}
</pre></div><div class="sect2" lang="en"><div class="titlepage"><div><div><h3 class="title"><a name="ssqls-vc2003"></a>5.10.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 type="1"><li><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 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 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><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><strong class="command">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><p>There&#8217;s <a href="http://svn.gna.org/viewcvs/*checkout*/mysqlpp/trunk/Wishlist" target="_top">a
      plan</a> to replace the current SSQLS mechanism with an
      entirely new code base. Although this is being done primary 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 width="100" align="left"><div class="footnote"><p><sup>[<a name="ftn.id2854985" href="#id2854985">10</a>] </sup>MySQL++ doesn&#8217;t have typedefs
    for some of the more exotic data types, like those for the
    geospatial types. Patches to correct this will be thoughtfully
    considered.</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>