SQL Strings with C++

Recently I was looking for a way to build SQL strings using C++. There is surprisingly little found on the net.

Some examples of the C++ syntax that will be supported by the following code are shown below:

dbStr.SELECT("idModule").FROM("Module").WHERE("name", "=", name);
dbStr.INSERT("Module").INTO({"idModule","name"}).VALUES({nullptr, name});
dbStr.UPDATE("Module").SET({"idModule","name"}, {nullptr, name});

I used upper case because C++ has "and" as a keyword.

These examples use method chaining or the named parameter idiom. Most of this code is pre C++11 code, but it is neat to use initializer lists to fill column names and column values.

One of the advantages of this code is that the values are type safe. Another advantage is that it is possible to add more run-time state checking if desired.

The main class contains the following declarations.

    class DbString:public std::string
    {
    public:
        DbString()
            {}
        DbString(char const * const str):
            std::string(str)
            {}
	// Appends "SELECT column"
        DbString &SELECT(char const *column);

        // Appends "INSERT INTO table"
        DbString &INSERT(char const *table);
        // Appends " FROM "
        DbString &FROM(char const *table);
        // Appends "(columnName1, columnName2)"
        DbString &INTO(DbNames const &columnNames);
        // Appends "VALUES (columnValue1, columnValue2)"
        DbString &VALUES(DbValues const &columnValues);

        // Appends "UPDATE table"
        DbString &UPDATE(char const *table);
	// Appends " SET column1, column2 = value1, value2"
	// The number of names and values must match.
        DbString &SET(DbNames const &names, DbValues const &values);

        // Appends " WHERE columnName operStr colVal"
        DbString &WHERE(char const *columnName, char const *operStr,
            DbValue colVal);
        // Appends " AND columnName operStr colVal"
        DbString &AND(char const *columnName, char const *operStr,
            DbValue colVal);
        // Appends a semicolon to the returned string.
        std::string getDbStr() const;

    private:
        // Prevent usage. Use getDbStr instead. This is undefined.
        char const *c_str();
    };

The string values passed in to DbValue will be stored in the std::string so that they are contained within quotes. The column names are merely strings without any conversions.

    class DbValue:public std::string
        {
        public:
	    /// This converts the integer into a string and appends to DbValue.
	    /// @param val The value to convert into a string.
            DbValue(int val)
                { appendInt(val); }
            /// @param val Use nullptr to indicate NULL or pass in a string that
	    ///            will be enclosed within quotes.
            DbValue(char const *val);
        };

    typedef std::vector<std::string> DbNames;
    typedef std::vector<DbValue> DbValues;

The vector then allows building something like the following:

    DbValues vals = { nullptr, 0, "test" };

These values will create strings in the vector such as:

Value	Length in bytes
NULL	4 + null terminator
0	1 + null terminator
"test"	6 + null terminator

Some of the functions look like the folowing, where the append functions append strings to the DbString.

    DbString &DbString::SELECT(char const *column)
        {
        append("SELECT ");
        append(column);
        return *this;
        }

The SET function does a bit more, but still uses the basic idea. The INTO function is pretty similar, but just appends enclosing parenthesis, and does not append values.

    DbString &DbString::SET(DbNames const &columns, DbValues const &values)
        {
        append(" SET ");
        size_t numColumns = std::min(columns.size(), values.size());
        for(size_t i=0; i<numColumns; i++)
            {
            if(i != 0)
                {
                append(",");
                }
            append(columns[i]);
            append("=");
            append(values[i]);
            }
        return *this;
        }

The getDbStr function simply appends a semicolon.

The complete source code can be found at the Oovaide project.