Definition
Embedded SQL refers to SQL statements you execute or declare from within a client application.
- 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.
- 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
- 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.
- 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.
- Static Embedded SQL Statements
- Dynamic Embedded SQL Statements
- Teradata® Preprocessor2 for Embedded SQL Programmer Guide, B035-2446 for examples of embedded SQL applications in the supported client languages.
- SQL Cursors for cursor declarations.
- Static Embedded SQL Statements for all other embedded SQL declaratives.
For more information on cursors, see SQL Cursors.
- “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.
See ACTIVITY_COUNT for more information on ACTIVITY_COUNT.