Fixing ODBC Filter Strings

SQLFix prepares an string for insertion into an ODBC filter string’s WHERE clause.
It checks a string for occurrences of the single-quote character(‘), and replaces each
occurrence with two single quotes (”). This allows a string with a single quote to
be used in WHERE clause, which is necessary because WHERE operands are delineated by the
single quote character, making escapement of embedded single quotes necessary.

The first parameter is a NULL terminated string that contains the text string that will be
reformatted. Also, if parameter bIsLike is TRUE, then all occurrences of the
underscore & percent characters are surrounded by square brackets (a_b.ps
becomes a[_]b.ps). This causes ODBC to view these characters as non-wildcards.

Following is a short example of how SQLFix might be used in your code:

BOOL GetARecord()
{
 CString strName(_T("Fred's Bank"));
 CString strPath(_T("a_b.ps"));
 CMyRecordset myset;

 myset.m_strFilter.Format(_T("[Name] = '%s' and [Path] Like '%%\%s'"),
 SQLFix(strName), SQLFix(strPath, TRUE));
 if (myset.Open())
 ...
}

In the above example, myset.m_strFilter would be "[Name] = ‘Fred”s Bank’
and [Path] Like ‘%%\a[_]b.ps"
And here’s the function:

CString SQLFix(LPCTSTR szText, BOOL bIsLike = FALSE)
// escapes illegal ODBC characters '%_
{
 // quickly determine if we have illegal characters
 if (_tcschr(szText,_T(''')) == NULL
  && (!bIsLike || _tcspbrk(szText,_T("%_")) == NULL))
  return CString(szText);

 // Searches for "'" and replaces it with "''"
 CString strTemp("");
 int nLength = _tcslen(szText);
 bool bDoLike;

 for ( register int count = 0 ; count < nLength ; count++ )
 {
  // % and _ must be surrounded by square brackets in a LIKE statement
  bDoLike = (bIsLike && (szText[ count ] == _T('_') || szText[ count ] == _T('%')));
  if (bDoLike)
   strTemp += _T('[');

  // add the source character
  strTemp += szText[ count ];

  // single quotes must be escaped with a second single quote
  if( szText[ count ] == _T(''') )
   strTemp += _T(''');

  if (bDoLike)
   strTemp += _T(']');
 }
 return strTemp;
}

Date Last Updated: May 17, 1999