Creating a Standard Quote-Delimited String

&var.QUOTEDSTRING

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.