dtl

Using Field Names with Spaces in DTL

Motivation

Most of the time, you will use database fields whose names contain alphanumeric characters and underscores. However, there are occasions where you may have field names that contain spaces such as FIRST NAME and LAST NAME in an EMPLOYEE table. This situation was rare enough that DTL versions 3.2.4 and earlier did not even take field names with spaces into account. However, now DTL supports this feature.

Quote Chars ... Database Independent Support

Field names with spaces must be marked off by a distinctive character used for that purpose. In Oracle, you have to enclose such identifiers with double quotes as in:

SELECT "FIRST NAME", "LAST NAME" FROM EMPLOYEE

Other DBMS's use different characters to set off these field names. Microsoft Access uses back ticks. So the same query there would be:

SELECT `FIRST NAME`, `LAST NAME` FROM EMPLOYEE

Without these quote marks, the SQL interpreter will get confused and return some kind of syntax error.

DTL uses a notion called quote chars to abstract these distinctive strings that set off field names from the rest of the query. DTL queries the underlying DBMS for the quote char it uses to denote the beginning and end of the field names. Thus, the above query would be represented in DTL with an expression like:

tstring QC = dbConn.GetQuoteChar();

tstring sqlQry = "SELECT " + QC + "FIRST NAME" + QC + ", " + QC + "LAST NAME" + QC + " FROM EMPLOYEE";

Note that dbConn.GetQuoteChar() returns a tstring denoting the quote char for the given DBConnection object (some databases might use a multicharacter string to denote the quote char).

Putting it All Together

For any DynamicDBView or DynamicIndexedDBView, DTL will surround all field names that contain a <space> character with quote marks automatically in the generation of the SQL query to protect them against unintended behavior. Without this safety measure, code using dynamic views that involve field names with spaces will crash. As the user may not know the field names in dynamic views, DTL has to take care to handle field name properly. For similar reasons, in the generated postfix clauses for IndexedDBView::replace() and select_update_iterator::replace() that determine which rows to update, DTL encloses field names containing a <space> within quote chars.

However, in the case of static DBViews, the user knows the field names in the underlying table(s). DTL leaves the responsibility of quoting field names to the user in the static DBView case. The user can manually add in the quote chars as needed by explicitly adding in the quotemarks (in the example below, listed as QC) around the field names in the BCA as in:

class EmployeeBCA : STD_::binary_function<BoundIOs &, Employee &, void>

{

 public;

    void operator()(BoundIOs &boundIOs, Employee &rowbuf)

    {

	   COLS[QC + _TEXT("FIRST NAME") + QC] == rowbuf.firstNm;

	   COLS[QC + _TEXT("LAST NAME")  + QC] == rowbuf.lastNm;

    }

};

Another way that is more painful is to write a BuildSpecialQry functor for that particular view. To enable the use of quote chars in such a query, all you have to do is call MakeDelimitedList() on the field names with QuoteChar as the prefix and the suffix. For example:

// inside BuildSpecialQry functor, SELECT case

Query += _TEXT("SELECT ");



// will build comma separated list of field names with the

// field names properly enclosed in quote chars, i. e.:

// "FIRST NAME", "LAST NAME"

// in the example, this string can then be used to generate:

// SELECT "FIRST NAME", "LAST NAME" FROM EMPLOYEE

Query += MakeDelimitedList(colNames, QuoteChar, QuoteChar, _TEXT(", ")); 



Query += _TEXT(" FROM ");



Query += MakeDelimitedList(tableNames);

Permission to use, copy, modify, distribute and sell this software and its documentation for any purpose is hereby granted without fee, provided that the above copyright notice appears in all copies and that both that copyright notice and this permission notice appear in supporting documentation. Corwin Joy and Michael Gradman make no representations about the suitability of this software for any purpose. It is provided "as is" without express or implied warranty.

This site written using the ORB.