This is the most basic sort of SSQLS declaration:

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<mysqlpp::sql_mediumtext>, description)

This creates a C++ structure called stock containing six member variables (item, num, weight, price, sdate, and description), along with some constructors and other useful member functions.

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 sql_* typedef for almost every data type MySQL understands.^[10] While it’s possible to use some regular C and C++ data types here, it’s safer to use the ones MySQL++ defines, as they’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’t convert it to one MySQL++ already understands, MySQL++ will throw a TypeLookupFailed exception. The sql_* types are defined in MySQL++’s sql_types.h header. The naming scheme is easy to learn when you know the SQL data type names.

Another thing you’ll notice above is the type of the last column. We’ve wrapped it in MySQL++’s Null 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 Section 3.9, “Handling SQL Nulls”.

One of the generated constructors takes a reference to a Row, allowing you to easily populate a vector of stocks like so:

vector<stock> result; 
query.storein(result);

MySQL++ takes care of mapping result set data to SSQLS fields. The SSQLS doesn’t have to have the same number of fields as the result set, and the order of fields in the result set doesn’t have to match the order of fields in the SSQLS. Fields in the result set that don’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.

The general format of this set of macros is:

sql_create_#(NAME, COMPCOUNT, SETCOUNT, TYPE1, ITEM1, ... TYPE#, ITEM#)

Where # is the number of member variables, NAME is the name of the structure you wish to create, TYPEx is the type of a member variable, and ITEMx is that variable’s name.

The COMPCOUNT and SETCOUNT arguments are described in the next section.

sql_create_x adds member functions and operators to each SSQLS that allow you to compare one SSQLS instance to another. These functions compare the first COMPCOUNT fields in the structure. In the example above, COMPCOUNT is 1, so only the item field will be checked when comparing two stock structures.

This feature works best when your table’s “key” fields are the first ones in the SSQLS and you set COMPCOUNT 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.

COMPCOUNT must be at least 1. The current implementation of sql_create_x cannot create an SSQLS without comparison member functions.

Because our stock structure is less-than-comparable, you can use it in STL algorithms and containers that require this, such as STL’s associative containers:

std::set<stock> result;   
query.storein(result);
cout << result.lower_bound(stock("Hamburger"))->item << endl;

This will print the first item in the result set that begins with “Hamburger.”

The third parameter to sql_create_x is SETCOUNT. If this is nonzero, it adds an initialization constructor and a set() member function taking the given number of arguments, for setting the first N fields of the structure. For example, you could change the above example like so:

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

In addition to this 2-parameter constructor, this version of the stock SSQLS will have a similar 2-parameter set() member function.

The COMPCOUNT and SETCOUNT 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’s often convenient to be able to say something like x == stock("Hotdog"). This requires that there be a constructor taking COMPCOUNT arguments to create the temporary stock instance used in the comparison.

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 SETCOUNT. This suppresses the duplicate constructor you’d get if you used the COMPCOUNT value instead. This is most useful in very small SSQLSes, since it’s easier for the number of key fields to equal the number of fields you want to compare on:

sql_create_1(stock_item, 1, 0, mysqlpp::sql_char, item)

Up to this point, we haven’t been using all of the features in the SSQLS structures we’ve been generating. What else can we do with SSQLSes? Consider this:

query.insert(s);

This does exactly what you think it does: it builds an INSERT query to insert the contents of s into the database. You have only to call query::execute() to actually insert it. This is possible because SSQLSes have functions that Query can call to get the list of fields and such, which it uses to build the INSERT query. query::update() and query::replace() also rely on these features.

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:

stock::table("MyStockData");

It’s also possible to change the name of a table on a per-instance basis:

stock s;
s.instance_table("AlternateTable");

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 “superclass” for a given set of tables.

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.

It’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 #include that header in more than one module, you get a multiply-defined symbol error at link time.

The way around this is to define the preprocessor macro MYSQLPP_SSQLS_NO_STATICS in all but one 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.

Imagine we have a file my_ssqls.h which includes a sql_create_N macro call to define an SSQLS, and that that SSQLS is used in at least two modules. One we’ll call foo.cpp, and we’ll say it’s just a user of the SSQLS; it doesn’t “own” it. Another of the modules, my_ssqls.cpp uses the SSQLS more heavily, so we’ve called it the owner of the SSQLS. If there aren’t very many modules, this works nicely:

// File foo.cpp, which just uses the SSQLS, but doesn't "own" it:
#define MYSQLPP_SSQLS_NO_STATICS
#include "my_ssqls.h"

// File my_ssqls.cpp, which owns the SSQLS, so we just #include it directly
#include "my_ssqls.h"

If there are many modules that need the SSQLS, adding all those #defines can be a pain. In that case, it’s easier if you flip the above pattern on its head:

// File my_ssqls.h:
#if !defined(EXPAND_MY_SSQLS_STATICS)
#   define MYSQLPP_SSQLS_NO_STATICS
#endif
sql_create_X(Y, Z....) // the SSQLS definition

// File foo.cpp, a mere user of the SSQLS:
#include "my_ssqls.h"

// File my_ssqls.cpp, which owns the SSQLS:
#define EXPAND_MY_SSQLS_STATICS
#include "my_ssqls.h"

The sql_create 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 stock structure used in all the ssqls*.cpp examples:

// Basic form
template <class Manip>   
stock_value_list<Manip> value_list(cchar *d = ",",
  Manip m = mysqlpp::quote) const;  

template <class Manip>   
stock_field_list<Manip> field_list(cchar *d = ",",   
  Manip m = mysqlpp::do_nothing) const;  

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


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

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

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

...Plus the obvious equivalents for field_list() and equal_list()

Rather than try to learn what all of these methods do at once, let’s ease into the subject. Consider this code:

stock s("Dinner Rolls", 75, 0.95, 0.97, sql_date("1998-05-25"));   
cout << "Value list: " << s.value_list() << endl;  
cout << "Field list: " << s.field_list() << endl;  
cout << "Equal list: " << s.equal_list() << endl;

That would produce something like:

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'

That is, a “value list” is a list of data member values within a particular SSQLS instance, a “field list” is a list of the fields (columns) within that SSQLS, and an “equal list” is a list in the form of an SQL equals clause.

Just knowing that much, it shouldn’t surprise you to learn that Query::insert() is implemented more or less like this:

*this << "INSERT INTO " << v.table() << " (" << v.field_list() <<
    ") VALUES (" << v.value_list() << ")";

where ‘v’ is the SSQLS you’re asking the Query object to insert into the database.

Now let’s look at a complete example, which uses one of the more complicated forms of equal_list(). 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 examples/ssqls5.cpp:

#include "cmdline.h"
#include "printdata.h"
#include "stock.h"

#include <iostream>
#include <vector>

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, &db, &server, &user, &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<stock> res;
        query.storein(res);

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

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

    return 0;
}

This example uses the list form of equal_list(). The arguments stock_weight and stock_price are enum values equal to the position of these columns within the stock table. sql_create_x generates this enum for you automatically.

The boolean argument form of that equal_list() call would look like this:

query << "select * from stock where " <<
    res[0].equal_list(" and ", false, false, true, true, false);

It’s a little more verbose, as you can see. And if you want to get really complicated, use the vector form:

vector<bool> v(5, false);
v[stock_weight] = true;
v[stock_price] = true;
query << "select * from stock where " <<
    res[0].equal_list(" and ", v);

This form makes the most sense if you are building many other queries, and so can re-use that vector object.

Many of these methods accept manipulators and custom delimiters. The defaults are suitable for building SQL queries, but if you’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.

At this point, we’ve seen all the major aspects of the SSQLS feature. The final sections of this chapter look at some of the peripheral aspects.

There’s a more advanced SSQLS creation macro, which all the others are built on top of. Currently, the only feature it adds over what’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 Hungarian notation in your C++ program without changing the SQL database schema:

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

If you ever need to see the code that a given SSQLS declaration expands out to, use the utility doc/ssqls-pretty, like so:

doc/ssqls-pretty < myprog.cpp |less

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’s preprocessor is called cpp, and that its command line interface follows Unix conventions.)

If you run it from the top MySQL++ directory, as shown above, it will use the header files in the distribution’s lib subdirectory. Otherwise, it assumes the MySQL++ headers are in their default location, /usr/include/mysql++. If you want to use headers in some other location, you’ll need to change the directory name in the -I flag at the top of the script.

The SSQLS header ssqls.h is automatically generated by the Perl script ssqls.pl. Although it is possible to change this script to get additional functionality, most of the time it’s better to just derive a custom class from the stock SSQLS and add your additional functionality that way.

That said, ssqls.pl does have a few configurables you might want to tweak.

The first configurable value sets the maximum number of data members allowed in an SSQLS. It’s 25 out of the box, set by the $max_data_members variable at the top of ssqls.pl. Beware, making this value larger increases the size of ssqls.h 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’s really the best design. Almost always, having so many columns is a sign that you need to refactor the table.

The second configurable is the default floating point precision used for comparison. As described above (Section 5.2, “SSQLS Comparison and Initialization”) SSQLSes can be compared for equality. The only place this is tricky is with floating-point numbers, since rounding errors can make two “equal” 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 “human” 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.

There are actually two ways to change this threshold. If you need a different system-wide default, edit ssqls.pl and change the $fp_min_delta variable at the top of the file, then rebuild ssqls.h as described below. If you need different thresholds per file or per project, it’s better to set the C macro MYSQLPP_FP_MIN_DELTA instead. The Perl variable sets this macro’s default; if you give a different value before #including ssqls.h, it will use that instead.

To rebuild ssqls.h after changing ssqls.pl, you’ll need a Perl interpreter. The only modern Unixy system I’m aware of where Perl isn’t installed by default is Cygwin, and it’s just a setup.exe choice away there. You’ll probably only have to download and install a Perl interpreter if you’re on Windows and don’t want to use Cygwin.

If you’re on a system that uses autoconf, building MySQL++ automatically updates ssqls.h any time ssqls.pl changes. Otherwise, you’ll need to run the Perl interpreter by hand:

c:\mysql++> cd lib
c:\lib> perl ssqls.pl

It takes special care to use SSQLS with BLOB columns. It’s safest to declare the SSQLS field as of type mysqlpp::sql_blob. This is currently a typedef alias for String, 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 String object’s contents, without interference.

Because C++ strings handle binary data just fine, you might think you can use std::string instead of sql_blob, but the current design of String converts to std::string via a C string. As a result, the BLOB data is truncated at the first embedded null character during population of the SSQLS. There’s no way to fix that without completely redesigning either String or the SSQLS mechanism.

The sql_blob typedef may be changed to alias a different type in the future, so using it instead of String ensures that your code tracks these library changes automatically. Besides, String is only intended to be an internal mechanism within MySQL++. The only reason the layering is so thin here is because it’s the only way to prevent BLOB data from being corrupted while avoiding that looming redesign effort.

You can see this technique in action in the cgi_jpeg example:

#include <mysql++.h>
#include <ssqls.h>

#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) < 4) || memcmp(cgi_query, "id=", 3)) {
            std::cout << "Content-type: text/plain" << std::endl << std::endl;
            std::cout << "ERROR: Bad query string" << std::endl;
            return 1;
        }
        else {
            img_id = atoi(cgi_query + 3);
        }
    }
    else {
        std::cerr << "Put this program into a web server's cgi-bin "
                "directory, then" << std::endl;
        std::cerr << "invoke it with a URL like this:" << std::endl;
        std::cerr << std::endl;
        std::cerr << "    http://server.name.com/cgi-bin/cgi_jpeg?id=2" <<
                std::endl;
        std::cerr << std::endl;
        std::cerr << "This will retrieve the image with ID 2." << std::endl;
        std::cerr << std::endl;
        std::cerr << "You will probably have to change some of the #defines "
                "at the top of" << std::endl;
        std::cerr << "examples/cgi_jpeg.cpp to allow the lookup to work." <<
                std::endl;
        return 1;
    }

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

    return 0;
}

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’t removed this platform from the supported list entirely.)

If you do need SSQLS and are currently on Visual C++ 2003, you have these options: