exec sql

Description

Marks the beginning of a SQL statement embedded in a host language program.

Syntax

exec sql [at connection_name] sql_statement end-exec

Parameters

at: Causes the SQL statement sql_statementto execute at the Adaptive Server connection connection_name.

connection_name: The connection name that identifies the Adaptive Server connection where sql_statement is to execute. The connection_name must be defined as a previous connect statement.

sql_statement: A Transact-SQL statement or other Embedded SQL statement.

Examples

Example 1

 EXEC SQL BEGIN DECLARE SECTION END-EXEC.

           01     SITE1     PIC X(25).

           01     SALES1     PIC S9(9) COMP.

      EXEC SQL END DECLARE SECTION END-EXEC.

      EXEC SQL CONNECT "user" identified by "password"

           AT server1 USING "server1" END-EXEC.

      EXEC SQL CONNECT "user" identified by "password"

           AT server2 USING "server2" END-EXEC.

      EXEC SQL AT server1 USE pubs2 END-EXEC.

      EXEC SQL AT server2 USE pubmast END-EXEC.

      EXEC SQL AT server1 SELECT count(*) FROM sales

                INTO :sales1 END-EXEC.

      MOVE "server1" TO SITE1.

      EXEC SQL SET CONNECTION server2 END-EXEC.

      EXEC SQL INSERT numsales VALUES (:SITE1, :SALES1) END-EXEC.

      EXEC SQL COMMIT WORK END-EXEC.

...

Usage

SQL statements embedded in a host language must begin with “exec sql”. The keywords exec sql can appear anywhere that a host language statement can begin.

The statement sql_statement can occupy one or more program lines; however, it must conform to host language rules for line breaks and continuation lines.

The at clause affects only the statement sql_statement. The clause does not affect subsequent SQL statements, and does not reset the current connection.

The at clause is not valid when sql_statement is one of the following SQL statements:

**Table 9-2: Statements that cannot use the at clause of exec sql**
allocate descriptor	begin declare section	connect
deallocate descriptor	declare cursor (dynamic)	end declare section
exit	get diagnostics	include file
include sqlca	set connection	set diagnostics
whenever

connection_name must be defined in a previous connect statement.

Each Embedded SQL statement must end with a terminator. In COBOL, the terminator is the keyword end-exec.