String Delimiters - Preprocessor2 for Embedded SQL

Teradata Preprocessor2 for Embedded SQL Programmer Guide

Product
Preprocessor2 for Embedded SQL
Release Number
15.10
Language
English (United States)
Last Update
2018-10-07
dita:id
B035-2446
lifecycle
previous
Product Category
Teradata Tools and Utilities

The APOST/QUOTE and APOSTSQL/QUOTESQL options control PL/I string recognition and generation, and the precompiler generates code appropriate for PL/I string usage.

The only values permitted for PL/I are APOST and APOSTSQL.

 

Option

Description

APOST

Native PL/I strings are enclosed by apostrophes (’).

The precompiler encloses all strings recognized by PL/I in apostrophes.

APOSTSQL

SQL strings are delimited by apostrophes (‘).

The escape character is quotation marks (“).

These options are described in Chapter 2: “Connecting to the Database and Invoking PP2.”

The APOSTSQL option controls multistatement request delimiting. As an example, with APOST/APOSTSQL, the application is the following:

EXEC SQL
      DECLARE cursor CURSOR FOR
      ’SELECT * FROM table
      WHERE COL1 = ’’ABCD’’;
      UPDATE “table one” SET COL2 = ’’ABCD’’’;

Delimited Identifiers and Character String Literals

Teradata SQL for Teradata Database 12.00 and later extends ANSI-compliant Unicode delimited identifiers and character string literals. This enables the Data Dictionary to function the same, whether or not Japanese character set support is enabled or not. Additionally, object names with multi-byte character sets can be shared between sessions that have non-Japanese character sets.

The Unicode delimited identifier is U&" ". The Unicode character string literal is U&' '. These new identifiers and literals are produced where ' 'XN and ' 'XC notation is used.

For example, prior to Teradata Database 12.00, the syntax to represent a Kanji string was:

SELECT * FROM '8273826082618250'XN; 
/* This hexadecimal string represents the Kanji1 string using the KANJIEUC_0U character set*/

For Teradata Database 12.00 and later, the syntax is:

SELECT * FROM U&"\FF34\FF21\FF22\FF11"; 
/* for post V2R12.0.0 Unicode Data Dictionary, the internal hex is based on UNICODE hex for object name   */

In the above example, backslash (\) is the escape character. The default escape character depends on the session charset, but will be backslash (\) or yen sign for all supported character sets. The above SQL can also be written as:

SELECT * FROM U&"$FF34$FF21$FF22$FF11" UESCAPE('$');

where $ is the escape character.

User Defined Functions

User Defined Functions (UDFs) are valid in embedded SQL statements, but CREATE and REPLACE FUNCTION are not valid in PP2 applications. For further information, see SQL Data Definition Language (B035‑1144).