Embedded SQL Overview | Teradata Vantage - Embedded SQL Overview - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

Definition

Embedded SQL refers to SQL statements you execute or declare from within a client application.

An embedded Teradata SQL client program consists of the following:
  • Client programming language statements.
  • One or more embedded SQL statements.
  • Depending on the host language, one or more embedded SQL declare sections.

    SQL declare sections are optional in COBOL and PL/I, but must be used in C.

The special prefix, EXEC SQL, distinguishes the SQL language statements embedded into the application program from the host programming language.

Embedded SQL statements must follow the rules of the host programming language concerning statement continuation and termination, construction of variable names, and so on. Aside from these rules, embedded SQL is host language-independent.

Special SQL Statements for Embedded SQL

Embedded SQL requires many SQL language constructs that are not supported for the interactive use of the language.

However, with few exceptions, you can use any SQL statement that can be executed interactively in an embedded SQL application. Exceptions include:
  • Non-ANSI Teradata extensions ECHO and USING
  • CREATE FUNCTION and REPLACE FUNCTION
  • The following query logging statements: BEGIN QUERY LOGGING, END QUERY LOGGING, FLUSH QUERY LOGGING, and REPLACE QUERY LOGGING
Embedded SQL includes the following SQL components:
  • Direct, or interactive, SQL
  • Extensions providing host variable support
  • Statements supporting the following constructs to support embedded SQL: Declaratives, Dynamic SQL, and Cursors

Supported Host Languages

  • C
  • COBOL
  • PL/I

Preprocessing Embedded SQL Statements

Because client programming languages do not understand SQL, a precompiler or preprocessor must preprocess SQL-containing source code to first comment out and then convert the SQL language elements into CLIv2 calls, before compiling them with the appropriate C, COBOL, or PL/I compiler.

Preprocessor2 is the Teradata Database precompiler and runtime SQL statement manager.

Data Returning Statements

A data returning statement is an embedded SQL statement that returns one or more rows of data to the program.

The data returning SQL statements are:
  • CHECKPOINT
  • COMMENT (Comment Returning Form)
  • EXPLAIN
  • HELP
  • SELECT
  • SHOW

Each data returning statement must specify the host output variables into which the returned data is placed.

IF … THEN …
no more than one row of data can be returned you can use an INTO clause with the statement to specify the host variables.
more than one row is expected use the selection cursor method and do not specify the INTO clause.
a data returning statement is executed dynamically you must define a dynamic cursor, regardless of the number of response rows expected.

Rules

  • EXEC SQL must prefix all embedded SQL statements.
  • You must terminate all embedded SQL statements. The terminator depends on the client application language.
FOR this language … The SQL terminator is …
COBOL END-EXEC
C ;
PL/I
  • Any executable SQL statement can appear anywhere that an executable client application language statement can appear.
  • Embedded SQL statements can reference host variables.
  • You must define a host variable between BEGIN DECLARE SECTION and END DECLARE SECTION statements.
  • You must define a host variable before any SQL statement reference to it.
  • You should draw all host variables from the same domain as their target columns.
  • UDTs are not specifically supported for any form of embedded SQL.

    However, embedded SQL applications can use SQL statements that reference UDTs, provided that the UDTs have a defined tosql or fromsql transform as appropriate.

    You must have, at minimum, the UDTUSAGE privilege on any UDT columns you access from an application.

    Additionally, the application must send and receive UDT data in the form of its external (non-UDT) data type.

  • Host variables and columns can have the same names.
  • All embedded SQL programs must contain one or both of the SQLSTATE and SQLCODE host variables to communicate status between the Teradata Database and the client application:

    The ANSI/ISO SQL-92 standard deprecates the use of SQLCODE and the ANSI/ISO SQL-99 standard no longer supports it; therefore, you should use the SQLSTATE variable for any applications that you intend to run under ANSI mode.

    You might also find it useful to include an ACTIVITY_COUNT result code variable in your embedded SQL applications.

  • You should always test the value for SQLCODE or SQLSTATE (or both) after executing an embedded SQL statement.

Related Topics

For information about the selection cursor method, see SQL Cursors.

For more information on embedded SQL, see:
For more information on declaratives, see:

For more information on cursors, see SQL Cursors.

For more information on UDTs, see:
  • “CREATE TRANSFORM” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
  • Teradata Vantage™ - SQL Data Control Language, B035-1149 for details about the privileges required to access and manipulate UDT column values.
For more information on SQLCODE and SQLSTATE, see:

See ACTIVITY_COUNT for more information on ACTIVITY_COUNT.