Creating SQL WHERE clauses
When developing C++ code to query SQL databases, one ends up with code which contains lots of ugly and cluttering statements like this:
char buf[256];
sprintf(buf, "WHERE (article=%i) AND (subject='%s%')", nArticle, pstrSubject);
strcat(sql, buf);
strcat(sql, " WHERE (article=");
strcat(sql, _itoa(nArticle, buf, 10));
strcat(sql, ") AND (subject='");
strcat(sql, pstrSubject);
strcat(sql, ")");
Most of you have seen that sort of thing. I created the CWhere
class to simplify creation of WHERE clauses and cleanup code. The resulting code, using the CWhere
class, looks like this:
CWhere w(_T("article"), 1234);
w.Add(_T("subject"), _T("CodeProject");
mySQLstring += w.GetWhereClause();
The class implements various methods to create the final string. Basically it all ends in providing several conversions of various datatypes to strings.
Currently the class contains all functions needed to create the following kind of clauses:
"fieldXY IS NULL"
"fieldXY exp value"
where exp is any valid SQL operator (<, >, =,...)
"fieldXY BETWEEN value1 AND value2"
Also, these clauses can be cascaded with AND
and OR
statements. The class takes care of nice SQL formatting, paranthesis, brackets and spaces. You can immediately use the resulting string for concatenation or other string operations. It contains leading and trailing spaces and all expressions are enclosed in brackets.
The class will provide correct strings for Unicode SQL statements too when the application is compiled using the UNICODE
define.
Using the class
Lets start with the most common usage: creating a instance of CWhere
with the first expression supplied in the constructor:
CWhere w(_T("article"), 1234);
This will create the variable w
already containing "WHERE (article = 1234)". Now lets add another field:
w.Add(_T("subject"), _T("CodeProject");
This will add a " AND (subject = 'CodeProject')" to the variable. Finally, use the WHERE clause and add it to an existing SQL statement created somewhere else:
mySQLstring += w.GetWhereClause();
If the presence of the keyword WHERE
is not needed or not wanted, just use GetWhereClausePlain()
.
Logical operations AND, OR
To simplify the creation of complex clauses containing AND
or OR
combinations, the operators &, |, &= and |= are overloaded. You can use them in a very intuitive way to create logical operations with the CWhere
class.
CWhere w, article1, article2("article", -1L), subject("subject", pstrSubject), priority("priority", 2);
w = article1 | article2;
w &= subject & priority;
The resulting WHERE clause is: " WHERE ((article IS NULL) OR (article = -1)) AND ((subject = 'CodeProject') AND (priority = 2))"
Compatibility
Tested under Windows XP Pro, with VC6 and MFC. Supports fully UNICODE or MBCS. STL version is up to the reader :-)
Software Smith, Blacksmith, Repeat Founder, Austrian, Asgardian.