INF: QUOTED_IDENTIFIER and Strings with Single Quotation Marks (156501)

MORE INFORMATION

Starting with SQL Server 6.0, you can use SET QUOTED_IDENTIFIER ON to have SQL Server start enforcing the ANSI SQL rules regarding quotation marks. According to the ANSI rules, double quotation marks are reserved for delimiting identifiers (such as database, table, or column names), while single quotation marks are reserved for delimiting character strings being used as data. For example, consider the following statement:

   select * from "authors" where "au_lname" = 'White'

With this option enabled, it is not legal to enclose the character string 'White' in double quotation marks. ANSI SQL interprets "White" as an identifier, not a character string.

According to ANSI SQL, when a character string includes a single quotation mark or an apostrophe, it should be represented by two single quotes, as in the following example:

   select * from authors where au_lname = 'O''Brien'

This means that applications must parse character strings for single quotation marks before building the strings into SQL commands, and add a second single quotation mark.

ODBC can be used in a way that eliminates the need to first parse character strings for single quotation marks. When an ODBC application uses SQLBindParameter() to bind character program variables to parameter markers in SQL commands, the character strings are not enclosed in either single or double quotation marks. There is no need for the application to have to parse the strings for single quotation marks. The following is an example:

   UCHAR *szCol1[41]="";
   SDWORD pcbVal1 = SQL_NTS;
   SQLPrepare(hstmt,
          (UCHAR *)"select * from authors where au_lname = ?", SQL_NTS);
   SQLBindParameter(hstmt1,
                 1,
                 SQL_PARAM_INPUT,
                 SQL_C_CHAR,
                 SQL_CHAR,
                 40,
                 0,
                 szCol1,
                 sizeof(szCol1),
                 &amp;pcbVal1);
   strcpy(szCol1, "O'Brien");
   SQLExecute(hstmt1);

Note that while the strcpy command contains the string "O'Brien," C only puts the characters O'Brien in the variable szCol1. The double quotation marks in this case are the string delimiters for C, and have nothing to do with SQL.

The SQL Server ODBC driver and Microsoft OLE DB Provider for SQL Server automatically set QUOTED_IDENTIFIER to ON when connecting. This can be configured in ODBC data sources, in ODBC connection attributes, or in OLE DB connection properties. SET QUOTED_IDENTIFIER defaults to OFF for connections from B-Library applications.

INF: QUOTED_IDENTIFIER and Strings with Single Quotation Marks (156501)

SUMMARY

MORE INFORMATION