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.
Is a Dialogue Manager variable.
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.
The following -TYPE command accepts quoted or unquoted input and displays quoted output.
-TYPE THE QUOTED VALUE IS: &E.QUOTEDSTRING
The output is:
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.
Any other variation results in:
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:
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:
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.
(FOC257) MISSING QUOTE MARKS: value;