Creating a Standard Quote-Delimited String

How to:

Reference:

Character strings must be enclosed in single quotation marks to be handled by most database engines. In addition, embedded single quotation marks are indicated by two contiguous single quotation marks. Quotation marks are required around variables containing delimiters, which include spaces and commas.

The QUOTEDSTRING suffix on a Dialogue Manager variable applies the following two conversions to the contents of the variable:

Dialogue Manager commands differ in their ability to handle character strings that are not enclosed in single quotation marks and contain embedded blanks. An explicit or implied -PROMPT command can read such a string. The entire input string is then enclosed in single quotation marks when operated on by .QUOTEDSTRING.

Note: When using the -SET command to reference a character string, ensure the character string is enclosed in single quotes to prevent errors.

Syntax: How to Create a Standard Quote-Delimited Character String

&var.QUOTEDSTRING

where:

&var

Is a Dialogue Manager variable.

Example: Creating a Standard Quote-Delimited Character String

The following example shows the results of the QUOTEDSTRING suffix on input strings.

-SET &A = ABC;
-SET &B = 'ABC';
-SET &C = O'BRIEN;
-SET &D = 'O'BRIEN';
-SET &E = 'O''BRIEN';
-SET &F = O''BRIEN;
-SET &G = OBRIEN';
-TYPE  ORIGINAL = &A QUOTED = &A.QUOTEDSTRING
-TYPE  ORIGINAL = &B QUOTED = &B.QUOTEDSTRING
-TYPE  ORIGINAL = &C QUOTED = &C.QUOTEDSTRING
-TYPE  ORIGINAL = &D QUOTED = &D.QUOTEDSTRING
-TYPE  ORIGINAL = &E QUOTED = &E.QUOTEDSTRING
-TYPE  ORIGINAL = &F QUOTED = &F.QUOTEDSTRING
-TYPE  ORIGINAL = &G QUOTED = &G.QUOTEDSTRING

The output is:

ORIGINAL = ABC        QUOTED = 'ABC'
ORIGINAL = ABC        QUOTED = 'ABC'
ORIGINAL = O'BRIEN    QUOTED = 'O''BRIEN'
ORIGINAL = O'BRIEN    QUOTED = 'O''BRIEN'
ORIGINAL = O'BRIEN    QUOTED = 'O''BRIEN'
ORIGINAL = O''BRIEN   QUOTED = 'O''''BRIEN'
ORIGINAL = OBRIEN'    QUOTED = 'OBRIEN'''

Note: The -SET command will remove single quotes around a string. Notice in the example above that the result of -SET &B = 'ABC' was changed to ORIGINAL = ABC (as shown in the output), prior to the QUOTEDSTRING conversion.

Example: Converting User Input to a Standard Quote-Delimited Character String

The following -TYPE command accepts quoted or unquoted input and displays quoted output.

-TYPE THE QUOTED VALUE IS: &E.QUOTEDSTRING

The output is:

Example: Using Quote-Delimited Strings With Relational Data Adapters

The following procedure creates an Oracle table named SQLVID from the VIDEOTRK data source.

TABLE FILE VIDEOTRK
SUM CUSTID EXPDATE PHONE STREET CITY STATE ZIP
   TRANSDATE PRODCODE TRANSCODE QUANTITY TRANSTOT
BY LASTNAME BY FIRSTNAME
WHERE LASTNAME NE 'NON-MEMBER'
ON TABLE HOLD
END
-RUN
CREATE FILE SQLVID
-RUN
MODIFY FILE SQLVID
FIXFORM FROM HOLD
DATA ON HOLD
END

Consider the following SQL Translator request:

SET TRACEUSER = ON
SET TRACEON = STMTRACE//CLIENT
SQL
SELECT *
FROM SQLVID WHERE LASTNAME  = &1.QUOTEDSTRING;
END

When this request is executed, you must enter a last name, in this case, O'BRIEN:

PLEASE SUPPLY VALUES REQUESTED
1=
O'BRIEN

In the generated SQL request, the character string used for the comparison is correctly enclosed in single quotation marks, and the embedded single quote is doubled:

SELECT SQLCOR01.CIN , SQLCOR01.LN , SQLCOR01.FN ,
SQLCOR01.EXDAT , SQLCOR01.TEL , SQLCOR01.STR , SQLCOR01.CITY ,
SQLCOR01.PROV , SQLCOR01.POSTAL_CODE , SQLCOR01.OUTDATE ,
SQLCOR01.PCOD , SQLCOR01.TCOD , SQLCOR01.NO , SQLCOR01.TTOT
FROM SQLVID SQLCOR01  WHERE SQLCOR01.LN  = 'O''BRIEN';

The following input variations are translated to the correct form of quoted string demonstrated in the trace.

'O'BRIEN'
'O''BRIEN'

Any other variation results in:

  • A valid string that does not match the database value and does not return any rows. For example, O''''BRIEN becomes 'O''''''''BRIEN' in the WHERE predicate.
  • An invalid string that produces one of the following messages:

    Error - Semi-colon or END expected

    Error - Missing or Misplaced quotes

    Error - (value entered) is not a valid column

    Error - Syntax error on line ... Unbalanced quotes

Strings without embedded single quotation marks can be entered without quotes or embedded in single quotation marks, either SMITH or 'SMITH'.

If you use &1 without the QUOTEDSTRING suffix in the request, acceptable input strings that retrieve O'Brien's record are:

'''O'''BRIEN'''
'''O''''BRIEN'''

Using &1 without the QUOTEDSTRING suffix, the acceptable form of a string without embedded single quotation marks is '''SMITH'''.

To make a string enclosed in single quotation marks acceptable without the QUOTEDSTRING suffix, use '&1' in the request. In this case, in order to retrieve the record for O'Brien, you must enter the string that would have resulted from the QUOTEDSTRING suffix:

'O''''BRIEN'

To enter a string without embedded single quotation marks using '&1', you can either omit the surrounding single quotation marks or include them: SMITH or 'SMITH'.

Note: The form '&1.QUOTEDSTRING' is not supported.

Reference: Usage Notes for Quote-Delimited Character Strings

  • QUOTEDSTRING must be used when passing parameter strings to drill-downs if the parameter contains multi-selected values. For an example, see Rules for a Multi-Select List of Values.
  • An unmatched single quotation mark at the beginning of a character string is treated as invalid input and generates the following message:
    (FOC257) MISSING QUOTE MARKS:  value;