DOCUMENT:Q257171  03-MAY-2001  [visualc]
TITLE   :BUG: Double Brackets in SQL Statement with MFC May Cause Error
PRODUCT :Microsoft C Compiler
PROD/VER:winnt:6.0
OPER/SYS:
KEYWORDS:kbDatabase kbMFC kbODBC kbVC kbVC600bug kbVS600bug kbGrpDSVCDB kbDSupport

======================================================================
-------------------------------------------------------------------------------
The information in this article applies to:

 - The Microsoft Foundation Classes (MFC), used with:
    - Microsoft Visual C++, 32-bit Enterprise Edition, version 6.0 
    - Microsoft Visual C++, 32-bit Professional Edition, version 6.0 
    - Microsoft Visual C++, 32-bit Learning Edition, version 6.0 
-------------------------------------------------------------------------------

SYMPTOMS
========

An error may occur when, in order to preserve a set of square brackets, you add
an extra set of brackets to an SQL statement. For example:

   Select * from myview where [[tableA.field1]]

The error that is returned depends on which Open Database Connectivity (ODBC)
driver is used and on the structure of the SQL statement, but can include a
syntax error, an invalid table or field name, or a variety of other problems.

CAUSE
=====

The Microsoft Foundation Classes (MFC) ODBC function CDatabase::ReplaceBrackets
replaces square brackets in an SQL statement with a driver-dependent quote
character (SQL_IDENTIFIER_QUOTE_CHAR). The CDatabase::ReplaceBrackets function,
which is called by the CRecordset::Open function, is used frequently.

When double brackets are replaced with single brackets, the string length changes
but the CDatabase::ReplaceBrackets function does not make the necessary
adjustment. The SQL statement that is passed to the driver contains extra
characters at the end of the string, which can cause errors.

WORKAROUND

Pad the end of your SQL statement with one blank space for each double bracket
used. For example, in the following statement

   "Select * from myview where [[tableA.field1]]"

place two spaces at the end of the statement:

   "Select * from myview where [[tableA.field1]]  "

STATUS
======

Microsoft has confirmed that this is a bug in the Microsoft products that are
listed at the beginning of this article.

MORE INFORMATION
================

Steps to Reproduce Behavior
---------------------------

1. Create a table in a Microsoft SQL Server database that uses a keyword for a
   field name (for instance "key"). If the Data Source Name (DSN) setting or
   connection string has quoted identifiers turned off, double brackets are
   needed. (Note that this is just an example; there are a number of other
   situations where double brackets are needed as well.)

2. Create an MFC ODBC application with a recordset based on this new table and
   make the following call:

   keyRS.Open(AFX_DB_USE_DEFAULT_TYPE, "Select [[key]] from TableA");

   This call produces the following error:

   Invalid object name 'TableAeA'.
   Statement(s) could not be prepared.

3. Set a breakpoint on the call to the CDatabase::ReplaceBrackets function
   inside of the CRecordset::BuildSQL function. You will see that before the
   call, m_strSQL contains the following

   Select [[key]] from TableA 

   whereas after the call returns, m_strSQL contains the following:

   Select [key] from TableAeA

   The CDatabase::ReplaceBrackets function removed the extra set of brackets but
   didn't correctly terminate the string. The null terminator remains in its
   original position, which makes the table name invalid.


Additional query words: escape character square brace braces

======================================================================
Keywords          : kbDatabase kbMFC kbODBC kbVC kbVC600bug kbVS600bug kbGrpDSVCDB kbDSupport 
Technology        : kbAudDeveloper kbMFC
Version           : winnt:6.0
Issue type        : kbbug
Solution Type     : kbpending

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 2001.