Click here to Skip to main content
Click here to Skip to main content

SQL WHERE - the easy way

, 18 Feb 2002 Ms-PL
Rate this:
Please Sign up or sign in to vote.
Creating SQL WHERE clauses can be a pain. This class grants relief.
<!-- Download Links -->

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);
// or even worse
strcat(sql, " WHERE (article=");
strcat(sql, _itoa(nArticle, buf, 10));
strcat(sql, ") AND (subject='");
strcat(sql, pstrSubject);
strcat(sql, ")");
// and so on and so forth...

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(); 
// should read "WHERE (article = 1234) AND (subject = 'CodeProject') ";

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:

  1. "fieldXY IS NULL"
  2. "fieldXY exp value" where exp is any valid SQL operator (<, >, =,...)
  3. "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(); 
// should contain " WHERE (article = 1234) AND (subject = 'CodeProject') "

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);
// logical operation OR
w = article1 | article2;		
// logical operation AND
w &= subject & priority;		

The resulting WHERE clause is: " WHERE ((article IS NULL) OR (article = -1)) AND ((subject = 'CodeProject') AND (priority = 2))"


Tested under Windows XP Pro, with VC6 and MFC. Supports fully UNICODE or MBCS. STL version is up to the reader Smile | :)


This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL)


About the Author

No Biography provided

Comments and Discussions

Questionstd::stringstream ?? PinmemberBrett Gmoser19-Apr-07 13:34 
QuestionLatest Code ??? Pinmemberbachi19-Feb-04 5:08 
AnswerRe: Latest Code ??? PinmemberSaurweinAndreas19-Feb-04 5:17 
Generalsmall help Pinmemberbachi19-Feb-04 7:13 
Generalhandling differences in sql PinsussVachooho18-Aug-03 15:12 
GeneralRe: handling differences in sql PinmemberSaurweinAndreas21-Aug-03 4:17 
QuestionCwhere 2.0 ? PinmemberJonathan de Halleux23-Jul-03 13:24 
GeneralGoing further PinmemberJonathan de Halleux12-Jul-03 8:13 
GeneralRe: Going further PinmemberSaurweinAndreas16-Jul-03 4:45 
Generalsource coming PinmemberJonathan de Halleux16-Jul-03 5:37 
GeneralRe: source coming PinmemberSaurweinAndreas17-Jul-03 6:10 
Generalpreview PinmemberJonathan de Halleux18-Jul-03 8:59 
GeneralRe: preview PinmemberSaurweinAndreas18-Jul-03 11:19 
GeneralRe: preview PinmemberJonathan de Halleux18-Jul-03 18:26 
Generaluseful class PinmemberAnthony_Yio2-Jan-03 16:10 
GeneralA suggestion PinmemberJim Johnson19-Feb-02 23:20 
This looks like a useful class. I know that I've had cases where I've had code littered with WHERE clause builder routines, and something like this would help.
However, a suggestion that would improve it's usability a lot (at least for someone like me): Have you thought about changing the add method to handle quote characters in any data strings? Quite often I find that I'm taking input from users, and you need to handle cases like "O'Reilly". Since the class encapsulates building a WHERE clause, I'd have expected/wanted it to handle that transparently as well (i.e. turn O'Reilly into O''Reilly if that is what is needed).
GeneralDuplicate... PinmemberJim Johnson20-Feb-02 0:56 
GeneralRe: Duplicate... PinmemberAndreas Saurwein20-Feb-02 7:18 
GeneralThanks, PinmemberBrian V Shifrin19-Feb-02 21:22 
GeneralRe: Thanks, PinmemberAndreas Saurwein20-Feb-02 7:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.150326.1 | Last Updated 19 Feb 2002
Article Copyright 2002 by Andreas S. Franci Gonçalves
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid