使用Mysqlpp

Recent projects require using mysqlpp as a C++<->mysql middleware.

This blog records basic usage and advice on using the library.

Most words are extracted from official docs and reference link is left at the end of this blog.

Considering limited time, translating to Chinese is temporarily put on hold.

connection

Like other library, you need to get a connection from your platform to db server first.

Connection obj derives from OptionalException

query

1. query object, stream support provided

2. template queries, which means prepared sql statement

3. ssqls, C++structure <–> sql schemas , orm mechanism

result set

1. field data stored in class String like std::string, conversion operators provided to any C type;
2. MySQL++ provides with classes like DateTime, to be initialized from a MySQL DATETIME string.
3. custom behavior when bad conversions occurs: throw exceptions or set a warning flag

queries that don’t return data

queries like create table ...;, update ... don’t pull data from tables;

an instance of class SimpleResult is given to signal if the operation is successful as well as number of rows impacted(if any)

return MySQL++ Data Structures

use Query::store()to return an object of StoreQueryResult, which derives from std::vector<mysqlpp:Row> , of course with random access support.

each Row obj is also like a vector——std::vector<String>, each String corresponds with one field data.

operation:

result[1][4] (all indexes start from 0)

result[1]["price"](access a field by field name)

Specialized SQL structures

with ssqls:

vector<stock> v;
query << "SELECT * FROM stock";
query.storein(v);
for (vector<stock>::iterator it = v.begin(); it != v.end(); ++it) {
  cout << "Price: " << it->price << endl;
}

no ssqls but after mysql++ v3

use Row directly

vector<mysqlpp::Row> v;
query << "SELECT * FROM stock";
query.storein(v);
for (vector<mysqlpp::Row>::iterator it = v.begin(); it != v.end(); ++it) {
  cout << "Price: " << it->at("price") << endl;
}

exceptions

library throws exceptions whenever an error occurs.

You can config library to set error flag instead, but exceptions will carry more information

the lib may trigger many kind of exceptions, all distinguishable with a single try block

classDiagram
	std_exception <|--Exception
	Exception<|--library_exception

when exceptions suppressed, return either an error code or an obj (test as false), or have the obj’s error flag set.

disable exceptions on a connection means everything flows on it should not throw any exceptions

passing false to the constructor of a connection obj

it’s possible to have exceptions most of the time but suppress them in some code blocks.

to do that, create a code block to encapsulate your code and create an instance of NoExceptions which take OptionalExceptions derivatives. The obj will store error flag and when it’s destroyed, it restores throwing exception mode.

mysqlpp::Connection con; // default ctor, so exceptions enabled

//code block
{
  mysqlpp::NoExceptions ne(con);
  if (!con.select_db("a_db_that_might_not_exist_yet")) {
    // Our DB doesn’t exist yet, so create and select it here; no need
    // to push handling of this case way off in an exception handler.
  }
}

cases where exceptions are thrown unconditionlly:

1. index out of bound(Row[$+\infty$]) BadIndex、 no such field Row[“no_such_field”] BadFieldName, these are situations after mysql++ 3.0.7; in the past std::range_error
2. String will throw BadConversioin if conversion is improper. 1.23 to int ×, 1.00 to int √
3. template queries with insufficient parameters BadParamCount
4. a C++ type that mysqlpp doesn’t know how to convert to sql. TypeLookupFailed

Quoting and Escaping

you need to tell mysqlpp explicitly when additional quoting and escaping are necessary.

string s = "Hotdog Buns";
query << "SELECT * FROM stock WHERE item = " << quote_only << s; 

–>

SELECT * FROM stock WHERE item = 'Hotdog Buns' 

quote_only add a single quotes pairs to the next thing inserted to the stream.

escape is provided too, with the same mechanism

most widely used:

quote = escape + quote_only

string s = "Frank’s Brand Hotdog Buns";
query << "SELECT * FROM stock WHERE item = " << quote << s; 

->

SELECT * FROM stock WHERE item = 'Frank''s Brand Hotdog Buns' 

types

db only stores numbers and text strings.

things travel between your app and db are only strings. including sql language

after v3.0.2, no data loss in sql float and double reported, but data loss in sql decimal type still exists

lib/sql_types.h defines data types that are compatible with sql.

Those types starts with sql_, in the middle are "\_".join(sql_type_name.split(" ")), if with _null at tail, it means this type is compatible with sql null value

you can also use C++ types instead, like int vs mysql::sql_tinyint_unsigned

advantages with mysql++ type:

1. space efficiency
2. portability and cross platfrom
3. clarity
4. compatibility

no exotic data types provided yet, like geospatial types

sql null

modifier

null in sql is a modifier to allow you store null value to the column while nothing like it exists in C++

null support is a template in mysqlpp

mysqlpp::Null<mysqlpp::sql_tinyint_unsigned> myfield;

//after v3.1
mysqlpp::sql_tinyint_unsigned_null myfield;

unique value

mysqlpp provide global null obj to represent sql null value.

myfield = mysqlpp::null;

special string

SQLTypeAdapter

convert given data to sql_string, mostly used by class String, users don’t use it often

String

represents a sql string, with a bunch of type conversion functions overloaded.

Binary types

currently not concerned

Transactions

create a Transaction object at the start of your code block,

when everything you finished, you call obj.commit()

if the object is destroyed before you call commit() ( out of scope or exception thrown ), it rolls back automatically

mysql server kills deadlock by rollback the second transaction.

exception_instance.errnum() -> ER_LOCK_DEADLOCK

Query type

Query::execute() for queries that don’t pull out data but only receives execution result: like create table

Query::store() : for queries that give the result in smaller memory scale. it receives all results and store all of them on the machine your app is running on, please consider the run time memory cost when the result set gets larger.

Query::storein(std containers): put the result into the container, but the container must be instantiated with ssqls type.

Query::user(): tell sql server to return the results one line each time to be processed linearly

auto query=conn.query("select * from stock");
mysqlpp::UseQueryResult res = query.use()
while (mysqlpp::Row row = res.fetch_row()) {
                cout << setw(30) << row["item"] << ' ' <<
                        setw(9) << row["num"] << ' ' <<
                        setw(9) << row["weight"] << ' ' <<
                        setw(9) << row["price"] << ' ' <<
                        setw(9) << row["sdate"] <<
                        endl;
            }

Sometimes you need to pull some records out of sql server and filter them in C++

Query::use_if()

// Define a functor for testing primality.
struct is_prime
{
    bool operator()(const stock& s)
    {
        if ((s.num == 2) || (s.num == 3)) {
            return true;    // 2 and 3 are trivial cases
        }
        else if ((s.num < 2) || ((s.num % 2) == 0)) {
            return false;   // can't be prime if < 2 or even
        }
        else {
            // The only possibility left is that it's divisible by an
            // odd number that's less than or equal to its square root.
            for (int i = 3; i <= sqrt(double(s.num)); i += 2) {
                if ((s.num % i) == 0) {
                    return false;
                }
            }
            return true;
        }
    }
};

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

        // Collect the stock items with prime quantities
        std::vector<stock> results;
        mysqlpp::Query query = con.query();
        query.store_if(results, stock(), is_prime());
    
    	//iterate
    	std::vector<stock>::const_iterator it;
        for (it = results.begin(); it != results.end(); ++it) {
            print_stock_row(it->item.c_str(), it->num, it->weight,
                    it->price, it->sDate);
        }
    
}

Template Query

example:

query << "select (%2:field1, %3:field2) from stock where %1:wheref = %0q:what";
query.parse();

parameter form:

%###(modifier)(:name_of_parameter)(:)
// always starts with a '%'
// ### is the index of the parameter
// the last ':' is trivial, if there is really a ':' in name, input 2 ':'s

modifer	Description
%	Print an actual “%”
“”	Don’t quote or escape no matter what.
q	This will escape the item using the MySQL C API function mysql_escape_string() and add single quotes around it as necessary, depending on the type of the value you use.
Q	Quote but don’t escape based on the same rules as for “q”. This can save a bit of processing time if you know the strings will never need quoting

at run time:

StoreQueryResult res = query.store("Dinner Rolls", "item", "item", "price")

–>

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

set default params

set default params of template query

query.template_defaults[1] = "item";
query.template_defaults["wheref"] = "item";

if you keep fields less likely to change and set its default value:

query.template_defaults["field1"] = "item"; 
query.template_defaults["field2"] = "price"; 

—>

no need to pass them all the time

StoreQueryResult res1 = query.store("Hamburger Buns", "item"); 
StoreQueryResult res2 = query.store(1.25, "price"); 

Specialized SQL Structures

This feature let’s you define a C++ structure that matches the from of your sql table.

But it gives more than column-member var mapping.

To define such a structure, you must use a macron in ssqls.h

sql_create

suppose a sql scheme is like:

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)

you can create a cpp structure like this

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 declares a data strucure stock in C++, which has a data member for each SQL column, with the same names.

The structure also has many member functions, operators and hidden data members.

general form of macron

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

here # after sql_create_ is the number of all data members.

compcount and setcount will be introduced in the coming paragraphs.

ssqls comparison and initialization

compcount=xxxmeans when 2 ssqls are compared, the first xxx fields will be compared, must be larger than 0;

setcount=yyy means when yyy is given >=0, the macron will additionally add a set()member function and a constructor which both take yyy parameters and set those parameters to the first yyy field.

note:

compcount and setcount can’t be equal, because compcount will gen a constructor that takes comcount params.

retrieve data

Connection con(..);con.connect();
vector<stock> res;
auto query = con.query("select item,description from stock");
...
query.storein(res);

you can also define a subset of the schema, it’s ok.

Even a superset is also acceptable.

sql_create_1(stock_subset,
  1, 0,
  string, item)
  
vector<stock_subset> res;
query.storein(res);
// ...etc...

add data

insert a single row

the insert method will take care of quoting and escaping automatically.

stock row("Hot Dogs", 100, 1.5,
                numeric_limits<double>::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 << "Query: " << query << endl;

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

insert many rows

vector<stock> lots_of_stuff;
...populate the vector somehow...
query.insert(lots_of_stuff.begin(), lots_of_stuff.end()).execute();

you can chain Query operations like in the last line above, because its methods return *this where that makes sense.

modify data

mysqlpp::StoreQueryResult res = query.store();
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 << "Query: " << query << endl;

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

use an ssqls in mutiple modules

if you extract the definition to a header file and include it wherever you need this structure, you will get a link error because the structure has static members to store meta info, such as table name and the list of the field names.

solution:

define preprocessor macron MYSQLPP_SSQLS_NO_STATICS in all file you need but one that owns the structure.

Connection Pool

when programming multithreadedly in a relatively smaller thread number scale, and each thread is accessing the db frequent enough, just let each thread have its own connection.

when 2 many threads are asking for connection or threads rarely access the db, connection pools are supposed to be used.

mysqlpp provides an interface of ConnectionPool, to instantiate it , one needs to overload 3 functions:

1. create(): create a conn with host, username, pswd …
2. destroy() : destroy a connection obj, since the superclass don’t know if the obj is allocated by new or on run time stack
3. max_idle_time(): configure max idle time

Make the connection pool singleton.

To make getting a connection RAII, use mysqlpp::ScopedConnection(*Connection, bool) when a thread need a connection, which will point to the real connection obj.

Reference

2. Overview (tangentsoft.com)

3. Tutorial (tangentsoft.com)

4. Template Queries (tangentsoft.com)

5. Specialized SQL Structures (tangentsoft.com)

7. Using MySQL++ in a Multithreaded Program (tangentsoft.com)