1
\$\begingroup\$

We have this old MFC code here which uses CString and format specifiers to generate an SQL statement and then execute that SQL.

Something like this:

CString SQL;
SQL.Format(
        "INSERT INTO vehicleData (\
            vehicleData_Id, engineCapacity, engineType) \
        VALUES (%d, %s, %s, %s)",
        strtoDB( id                                     ),
        strtoDB( Vehicle->getEngineCapacity()           ),
        lngtoDB( Vehicle->getEngineType()               ),
        strtoDB( Vehicle->getEngineNo()                 ),
    );

But then with WAY more fields. This code is dependant on the types of the properties added to the DB and it is error prone when you have more than 20 properties to add. You can easily mistake a %d for a %s etc.

So this screams for templates. Specialize the behaviour depending on the type and make the number of parameters to add variable....variable....variadic.

So yeah, this screams variadic templates and I came up with this:

template<typename... Args>
CString generate_sql_insert(const char *s, Args... args)
{
    std::stringstream ss;

    ss << s << " (";

    interpolate_direct(ss, args...) << ")";

    return ss.str().c_str();
}

template<typename T, typename... Args>
std::stringstream &interpolate_direct(std::stringstream &s, T value, Args... args)
{
    interpolate_direct(s, value) << ",";
    interpolate_direct(s, args...);

    return s;
}


template<typename T>
std::stringstream &interpolate_direct(std::stringstream &s, T value)
{
    if constexpr (std::is_pointer<T>::value &&
        is_char_type<std::remove_cv_t<std::remove_pointer<T>::type>>::value)
    {
        s << "'" << value << "'";
    }
    else if constexpr (std::is_same<T, CString>::value)
    {
        if (isDefault(value))
            s << "NULL";
        else
            s << "'" << value.GetBuffer(0) << "'";
    }
    else if constexpr (std::is_same<T, COleDateTime>::value)
    {
        if (isDefault(value))
            s << "NULL";
        else
            s << "'" <<
                 std::setfill('0') << std::setw(4) << value.GetYear()   << '-' << 
                 std::setfill('0') << std::setw(2) << value.GetMonth()  << '-' <<
                 std::setfill('0') << std::setw(2) << value.GetDay()    << ' ' <<
                 std::setfill('0') << std::setw(2) << value.GetHour()   << ':' <<
                 std::setfill('0') << std::setw(2) << value.GetMinute() << ':' <<
                 std::setfill('0') << std::setw(2) << value.GetSecond() << "'";
    }
    else if constexpr (std::is_same<T, bool>::value)
    {
        s << value;
    }
    else if constexpr (std::is_floating_point<T>::value)
    {
        if (isDefault(value))
            s << "NULL";
        else
            s << "'" << std::fixed << std::setprecision(6) << value << "'";
    }
    else
    {
        if (isDefault(value))
            s << "NULL";
        else
            s << value;
    }

    return s;
}

This code basically loop unrolls all the writes to the stream and you use it like this:

CString SQL = generate_sql_insert(
            "INSERT INTO vehicleData ("
            "vehicleData_Id, engineCapacity, engineType, engineNo,"
        ") VALUES",
        id,
        Vehicle->getEngineCapacity(),
        Vehicle->getEngineType(),
        Vehicle->getEngineNo(),
        Vehicle->getOwner());

generate_sql_insert will loop unroll all the writes to the stream:

ss << "INSERT INTO..... VALUES";
ss << "(";
ss << 1;
ss << "2";
ss << 3.101;
//etc
ss << ")";

Is there any room for improvement on this system? Are there "better" options available for generating huge SQL query strings?

\$\endgroup\$
  • \$\begingroup\$ Have you considered using regular streams? stringstream ss; ss << "INSERT INTO ..." << id << Vehicle->getEngineCapacity() << Vehicle->getEngineType() << ...;? This requires every type you use to have an ostream &operator <<(ostream &os, const Type &type);. If that clashes with existing code (because printing something and adding it to a query is different) then you can make your own class SQL_query_stream that has a member stringstream and overload SQL_query_stream &operator <<. This keeps knowledge about types more local. \$\endgroup\$ – nwp Dec 1 '17 at 14:04
  • \$\begingroup\$ Rather than using strings have you thought about a sql command class? An SQL command class would let you specify the operation, name the table, name any fields and clauses and generate the SQL for you. Examples of these exist in both VB.net and C#. \$\endgroup\$ – pacmaninbw Dec 1 '17 at 14:53
  • \$\begingroup\$ I guess you want fmt. \$\endgroup\$ – Incomputable Dec 1 '17 at 20:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.