#if !defined(_CWHERE_INCLUDED_)
#define _CWHERE_INCLUDED
//
// Simple class to encapsulate building a SQL WHERE clause
//
// (C) 2002 Andreas Saurwein, saurwein@uniwares.com
// You are granted the right to use, reuse and modify the source in any
// way except commercially selling the class.
//
//
// usage: for one-expression type wheres use the constructor version
//
// CWhere mywhere(L"myfield", 100, L">") or
// CWhere mywhere(fieldvar, L"text")
//
// and/or contruct the clause using Add()
//
// mywhere.Add(L"nullfield");
// mywhere.AddBetween(L"complexfield", date1, date2);
//
// Add with only the fieldname parameter will add a "field IS NULL" clause
// Add with fieldname and one parameter will add a "field=param" clause
// Add with fieldname, one variant parameter and a wchar param will add
// "field"+wchar param+variant param
// e.g. Add(L"field", L"test%", L" LIKE ") gives "field LIKE 'test%'"
//
// additionally you can create clauses with OR / AND logic by using the
// logical operators &, |, &= and |=
//
// The operators & and &= will create a AND clause from the supplied clauses
// while the operators | and |= will create a OR clause.
//
// Sample:
// You want "WHERE (field1 OR field2) AND (field3 AND field4)" where
// fieldX is each a CWhere clause
//
// CWhere another;
// another = field1 | field2; // logical operation OR
// another &= field3 & field4; // logical operation AND
//
// since the data parameters are variants you can stuff in whatever you can
// stuff into a variant and can be converted to VT_BSTR.
//
#include <comdef.h>
class CWhere
{
public:
CWhere(){};
//
// the plain PSTR variations
//
// field IS NULL
CWhere(TCHAR* field)
{ Add(field); };
// "field exp vt", default to "field = vt"
// use the template version for "normal" datatypes like int, long, float, double, etc.
template <class T> CWhere(const TCHAR* field, T vt, const TCHAR* exp=_T("="))
{ _variant_t v((T)vt); Add(field, v, exp); }
// field BETWEEN vt1 AND vt2
/*
// unfortunately this doesnt work. the compiler cant resolve the ambiguity.
template <class T1, class T2> CWhere(const TCHAR* field, const T1 vt1, const T2 vt2)
{ _variant_t v1((T1)vt1); _variant_t v2((T2)vt2); Add(field, v1, v2); }
*/
//
// the CString variations
//
CWhere(const CString& field)
{ Add(field); };
template <class T> CWhere(const CString& field, T vt, const TCHAR* exp=_T("="))
{ _variant_t v((T)vt) ; Add(field, v, exp); }
/*
template <class T1, class T2> CWhere(const CString& field, const T1 vt1, const T2 vt2)
{ _variant_t v1((T1)vt1); _variant_t v2((T2)vt2); Add(field, v1, v2); }
*/
enum
{
cwNone, cwOr, cwAnd, cwOrGroup, cwAndGroup
};
CWhere(const CWhere& a)
{
this->operator=(a); // assign ourself a copy of it
}
CWhere& operator=(const CWhere& a)
{
if(&a != this)
{
m_sWhere.RemoveAll();
m_sWhere.Append(a.m_sWhere);
m_nOp.RemoveAll();
m_nOp.Append(a.m_nOp);
}
return *this;
}
CWhere& operator&(const CWhere& a) { return this->operator&=(a); }
CWhere& operator|(const CWhere& a) { return this->operator|=(a); }
CWhere& operator&=(const CWhere& a)
{
if(m_sWhere.GetSize() == 0)
return this->operator=(a);
if(m_sWhere.GetSize() > 1)
{
// wrap existing ones into brackets, add "AND"
m_sWhere.InsertAt(0, CString(_T("("))); m_nOp.InsertAt(0, cwAndGroup);
m_nOp[m_nOp.GetSize()-1] = cwNone; // remove it, its the last in its group
m_sWhere.Add(CString(_T(")"))); m_nOp.Add(cwAnd);
// wrap new ones into brackets
m_sWhere.Add(CString(_T("("))); m_nOp.Add(cwAndGroup);
m_sWhere.Append(a.m_sWhere);
m_nOp.Append(a.m_nOp);
m_nOp[m_nOp.GetSize()-1] = cwNone; // remove it, its the last in its group
m_sWhere.Add(CString(_T(")"))); m_nOp.Add(cwAndGroup);
}
else if(m_sWhere.GetSize() == 1)
{
// just add it and set the default operation
m_nOp[0] = cwAnd;
m_sWhere.Append(a.m_sWhere);
m_nOp.Append(a.m_nOp);
}
return *this;
}
CWhere& operator|=(const CWhere& a)
{
if(m_sWhere.GetSize() == 0)
return this->operator=(a);
if(m_sWhere.GetSize() > 1)
{
// wrap existing ones into brackets, add "AND"
m_sWhere.InsertAt(0, CString(_T("("))); m_nOp.InsertAt(0, cwOrGroup);
m_nOp[m_nOp.GetSize()-1] = cwNone; // remove it, its the last in its group
m_sWhere.Add(CString(_T(")"))); m_nOp.Add(cwOr);
// wrap new ones into brackets
m_sWhere.Add(CString(_T("("))); m_nOp.Add(cwOrGroup);
m_sWhere.Append(a.m_sWhere);
m_nOp.Append(a.m_nOp);
m_nOp[m_nOp.GetSize()-1] = cwNone; // remove it, its the last in its group
m_sWhere.Add(CString(_T(")"))); m_nOp.Add(cwOrGroup);
}
else if(m_sWhere.GetSize() == 1)
{
// just add it and set the default operation
m_nOp[0] = cwOr;
m_sWhere.Append(a.m_sWhere);
m_nOp.Append(a.m_nOp);
}
return *this;
}
virtual ~CWhere(){};
// WHERE field IS NULL
void Add(const TCHAR* field)
{
CString w(_T("("));
w += field;
w += _T(" IS NULL");
w += _T(")");
m_sWhere.Add(w);
m_nOp.Add(cwAnd);
}
// WHERE (field = vt)
void Add(const TCHAR* field, const _variant_t& vt, const TCHAR* exp=_T("="))
{
CString w(_T("("));
w += field;
w += _T(" ");
w += exp; // to be on the save side we enclose it in spaces
w += _T(" ");
if(vt.vt == VT_BSTR)
{
#if defined(_UNICODE) || defined(UNICODE)
w += _T("N'");
#else
w += _T("'");
#endif
w += _bstr_t(vt).operator const TCHAR*();
w += _T("'");
}
else
{
try
{ _variant_t vtx(vt); vtx.ChangeType(VT_BSTR);
w += _bstr_t(vtx).operator const TCHAR*();
}
catch(...)
{
w += _T("<conversion failed>");
}
}
w += _T(")");
m_sWhere.Add(w);
m_nOp.Add(cwAnd);
}
// WHERE field BETWEEN vt1 AND vt2
void AddBetween(const TCHAR* field, const _variant_t& vt1, const _variant_t& vt2)
{
CString w(_T("("));
w += field;
w += _T(" BETWEEN ");
if(vt1.vt == VT_BSTR)
{
#if defined(_UNICODE) || defined(UNICODE)
w += _T("N'");
#else
w += _T("'");
#endif
w += _bstr_t(vt1).operator const TCHAR*();
w += _T("'");
}
else
{
try {_variant_t vtx(vt1); vtx.ChangeType(VT_BSTR); w += _bstr_t(vtx).operator const TCHAR*(); }
catch(...) { w += _T("<conversion failed>");}
}
w+= _T(" AND ");
if(vt2.vt == VT_BSTR)
{
#if defined(_UNICODE) || defined(UNICODE)
w += _T("N'");
#else
w += _T("'");
#endif
w += _bstr_t(vt2).operator const TCHAR*();
w += _T("'");
}
else
{
try {_variant_t vtx(vt2); vtx.ChangeType(VT_BSTR); w += _bstr_t(vtx).operator const TCHAR*();}
catch(...) { w += _T("<conversion failed>");}
}
w += _T(")");
m_sWhere.Add(w);
m_nOp.Add(cwAnd);
}
// get WHERE clause without the " WHERE "
CString GetClausePlain()
{
int and = m_sWhere.GetSize()-1;
if(and < 0)
return CString();
CString whereclause;
for(int i = 0; i <= and; i++)
{
whereclause += m_sWhere[i];
if(i < and)
{
switch(m_nOp[i])
{
case cwAnd:
whereclause += _T(" AND ");
break;
case cwOr:
whereclause += _T(" OR ");
break;
case cwAndGroup:
break;
case cwOrGroup:
break;
default:
break;
}
}
}
whereclause += _T(" ");
#ifdef _VERBOSE_DEBUG
OutputDebugString(whereclause);
OutputDebugString(_T("\r\n"));
#endif
return whereclause;
}
// get the complete clause including " WHERE "
CString GetClause()
{
return CString(_T(" WHERE ")) + GetClausePlain();
}
private:
CStringArray m_sWhere;
CWordArray m_nOp;
};
#endif
Submit an article or tip