Introduction

In part 1, we defined a variadic template function to set up and run prepared SQL statements that are impervious to injection attacks. In this post, we’ll define the second half of our library that will automatically convert and store results from the query in a typesafe matter.

Previously, we defined a variadic templated function that bound all of the input parameters for use in prepared statements. Here, we’ll use two separate functions: one to bind the output parameters from the prepared statement so that we have somewhere to store the results, and one to convert those output parameters to a tuple.

Code

Binding the output

We’ll first define a variadic templated function to bind output parameters to the prepared statement. When MySQL runs the statement, it will store the results in these parameters. The MySQL needs to know the types of each of the parameters when they are bound, so we’ll define partial template specializations for each data type.

We want to be able to partially specialize our templates so that we can have specializations for unique_ptr and shared_ptr types. C++ doesn’t allow you to partially specialize functions (that’s what overloading is for), so we’ll define a templated class with a single static method in order to use partial template specialization.

We’ll start with the least specialized template. The function will take a MySQL bind structure, a buffer in which to save the results from the query, and structure that MySQL will set if the result of the query is null . For the least specialized version, we’ll tell MySQL to try to convert the result to a string, and then rely on boost::lexical_cast to convert the string back to a relevant type later.

template <typename T> class OutputBinderParameterSetter { public: static void setParameter( MYSQL_BIND* const bind, std::vector<int8_t>* const buffer, my_bool* const isNullFlag ) { bind->buffer_type = MYSQL_TYPE_STRING; if (0 == buffer->size()) { // 20 is an arbitrary default. If the buffer is too short, we // will resize it later and refetch the results from MySQL buffer->resize(20); } bind->buffer = buffer->data(); // data() is new in C++11 bind->is_null = isNullFlag; bind->buffer_length = buffer->size(); } };

The full specializations for the normal C++ types will come next. Unlike before, we know the exact required buffer size by using the sizeof operator on the type, so we won’t need to tell MySQL the buffer size. We will need to set an is_unsigned flag for integral types.

template <> class OutputBinderParameterSetter<uint32_t> { public: static void setParameter( MYSQL_BIND* const bind, std::vector<int8_t>* const buffer, my_bool* const isNullFlag ) { bind->buffer_type = MYSQL_TYPE_LONG; buffer->resize(sizeof uint32_t); bind->buffer = buffer->data(); // data() is new in C++11 buffer->is_null = isNullFlag; bind->is_unsigned = 1; } };

This pattern will be repeated for the other integral types. The partial specialization for float and double are similar, but you don’t need to set the is_unsigned flag.

We also need to define partial specializations for shared_ptr and unique_ptr types. There’s nothing special to do when we’re just binding the output parameters, so we’ll just forward to the full specializations.

template <typename T> class OutputBinderParameterSetter<shared_ptr<T>> { public: static void setParameter( MYSQL_BIND* const bind, std::vector<int8_t>* const buffer, my_bool* const isNullFlag ) { OutputBinderParameterSetter<T>::setParameter(bind, buffer, isNullFlag); } };

Our final specializations are for pointer types. Because C++11 added shared_ptr and unique_ptr , there’s little good reason to handle memory manually. To discourage uses from using raw pointers, we’ll define a partial template specialization that will fail to compile and give an informative message. We’ll use another C++11 feature, static_assert , to accomplish this. We don’t want the compiler to produce an error message unless the user is actually trying to instantiate the specialization, so we’ll use the type as part of the assert.

template <typename T> class OutputBinderParameterSetter<T*>> { public: static void setParameter( MYSQL_BIND* const, std::vector<int8_t>* const, my_bool* const ) { static_assert( // C++ guarantees that the sizeof any type >= 0, // so this will always give a compile time error sizeof(T) < 0, "Raw pointers are not supported; use std::shared_ptr" " or std::unique_ptr instead"); } };

Converting the output

Next we’ll define a variadic templated function to convert the results from the executed query into the types from the tuple that the user has passed in. Like before, we want to be able to partially specialize our templates.

We’ll start with the generic version that we will fall back to if no other specializations are a better match. In this case, we’ll fall back to boost::lexical_cast to convert the buffer to the correct type, and the type’s operator=(T&&) . It’s worth noting that we handle null values with our shared_ptr and unique_ptr specializations, so if we do encounter a null value here, then that’s a hard error.

template <typename T> class OutputBinderResultSetter { void setResult( T* const value, const MYSQL_BIND& bind ) { if (*bind.is_null) { throw MySqlException("Null value handled with non-pointer type"); } *value = boost::lexical_cast<T>(static_cast<char*>(bind.buffer)); } };

With that out of the way, we can start defining the full specializations for the normal types. Similar to the generic version, we’ll throw an exception if the result is unexpectedly null .

template <> class OutputBinderResultSetter<uint32_t> { public: static void setResult( uint32_t* const value, const MYSQL_BIND& bind ) { if (*bind.is_null) { throw MySqlException("Null value handled with non-pointer type"); } *value = *static_cast<const uint32_t*>(bind.buffer); } };

We also need to define partial specializations for shared_ptr and unique_ptr types. In the other specializations, encountering null values is an error, but here, we can just set the pointer type to NULL and delete any object that may have been owned by the pointer. Otherwise, we just fall through to the full specialization.

template <typename T> class OutputBinderResultSetter<std::shared_ptr<T>> { public: static void setResult( std::shared_ptr<T>* const value, const MYSQL_BIND& bind ) { if (*bind.is_null) { value->reset(); } T* newObject = new T; OutputBinderResultSetter<T>::setResult(newObject, bind); *value = std::shared_ptr<T>(newObject); } };

Our final specializations are for pointer types. Like before, we want to prevent users from using raw pointers, so we’ll use static_assert to raise a compile-time error.

template <typename T> class OutputBinderResultSetter<T*> { public: static void setResult( T** const, const MYSQL_BIND& ) { static_assert( sizeof(T) < 0, "Raw pointers are not supported; use std::shared_ptr" " or std::unique_ptr instead"); } };

Putting it all together

Now that we have the specializations for binding and converting the parameters, we can write the function that runs a statement and saves the results into a vector of tuple s.For the sake of brevity, I’m going to remove out a lot of error checking and just leave comments in their place.

We’ll define a variadic templated function that takes a MySQL statement that’s already been prepared (i.e. has already had the input parameters bound to it), and a vector of tuple s. At this point, we have 2 options: we could directly append new rows to the vector that the user provides, or we could replace whatever was in the vector with the results from the successfully run query. Doing the first is convenient because users can run multiple statements in a row and append the results to the same vector , while the second is nice because we can support the strong guarantee. With the first, we could save several rows before encountering a data error, leaving the data set inconsistent. Here, we’ll take the second choice.

template <typename T> void bindParameters( std::vector const mysqlBindParameters, std::vector<std::vector>* const buffers, std::vector* const nullFlags, int_<I>, const Tuple* const unused ) { OutputBinderParameterSetter< typename std::tuple_element<I>::type >::setParameter( &mysqlBindParameters->at(I), &buffers->at(I), &nullFlags->at(I)); bindParameters( mysqlBindParameters, buffers, nullFlags, int_<I>(), unused); } template <> void bindParameters( std::vector* const, std::vector<std::vector>* const, std::vector* const, int_, const Tuple* const ) { } template void setResults( MYSQL_STMT* const statement, std::vector<std::tuple>* const results ) { // Allocate space for the bind parameters, buffers, // buffer lengths, and null flags const auto fieldCount = mysql_stmt_field_count(statement); std::vector parameters(fieldCount); std::vector<std::vector> buffers(fieldCount); std::vector lengths(fieldCount); std::vector nullFlags(fieldCount); const std::tuple* unused = nullptr; // Bind all of the output parameters bindParameters( ¶meters, &buffers, &nullFlags, int_(), unused); for (size_t i = 0; i < fieldCount; ++i) { parameters.at(i).length = &lengths.at(i); } mysql_stmt_bind_result(statement, parameters->data()); mysql_stmt_execute(statement); std::vector<std::tuple> tempResults; int fetchStatus = mysql_stmt_fetch(statement); // Keep fetching results until there are no more while (0 == fetchStatus) { std::tuple rowTuple; setResultTuple( &rowTuple, parameters, int_(sizeof...(Args) - 1)); tempResults.push_back(std::move(rowTuple)); fetchStatus = mysql_stmt_fetch(statement); } *results = std::move(tempResults); }

Now we can define the runQuery function in the MySQL class. This function is a little more complicated than our previous runCommand function, because we’re going to define a template with two variadic arguments: one for the input bind parameters, and one for the type of the output tuple (i.e. the types of the columns from the query).

template <typename... InputArgs, typename... OutputArgs> void runQuery( std::vector<std::tuple<OutputArgs...>>* const results, const char* const query, const InputArgs&... args ) const { MYSQL_STMT* const statement = mysql_stmt_init(connection_); const size_t length = ::strlen(query); mysql_stmt_prepare(statement, query, length); // SELECTs should always return something, so if this // is 0, the user tried to run a command, e.g. UPDATE if (0 != mysql_stmt_field_count(statement)) { throw MySqlException("Tried to run command statement with runQuery"); } const size_t parameterCount = mysql_stmt_param_count(statement); if (sizeof...(InputArgs) != parameterCount) { std::string errorMessage("Incorrect number of parameters; query required "); errorMessage += boost::lexical_cast<std::string>(parameterCount); errorMessage += " but "; errorMessage += boost::lexical_cast<std::string>(sizeof...(args)); errorMessage += " parameters were provided."; throw MySqlException(errorMessage); } std::vector<MYSQL_BIND> inputBindParameters(parameterCount); InputBinder<0, Args...> binder; binder.bind(&inputBindParameters, args...); setResults<OutputArgs...>(statement, results); }

And that’s it!

Next time, we’ll look at how we can use another C++11 feature, user defined literals, to check at compile time that the number of expected arguments in our prepared statements matches the number of provided arguments.