General Rules for Embedding SQL

Embedded SQL commands must appear in certain locations within the Pascal program. Each embedded SQL command must be accompanied by a prefix and followed by a semicolon. Comments may be placed within an embedded command, and non-numeric literals in embedded commands may be continued from one line to another.

An embedded SQL command has no maximum length. A dynamic SQL command can be no longer than 2048 bytes as a literal string, but the maximum size of the host variable is 32,762.

Location of SQL Commands

SQL commands must be put into the following specific areas:

BEGIN DECLARE SECTION and END DECLARE SECTION may appear in any declaration section in a main program. In a subprogram, these commands cannot appear in the global declaration section.
INCLUDE SQLCA should be in the global declaration section of the main program. INCLUDE SQLDA should be in the global declaration of any main or in a declaration section of a procedure.
All other SQL commands may appear in any statement part.

Prefix and Suffix

Precede each SQL command with the prefix EXEC SQL and terminate each SQL command with a semicolon (;). The entire prefix, EXEC SQL, must be specified on one line. For example, the following examples are legal:

   EXEC SQL SELECT PartName INTO :PartName

           FROM Purchdb.Parts WHERE PartNumber = :PartNumber;





   EXEC SQL SELECT PartName

            INTO :PartName

            FROM Purchdb.Parts

            WHERE PartNumber = :PartNumber;

However, the following is not legal:

   EXEC

     SQL SELECT PartName INTO :PartName

           FROM Purchdb.Parts WHERE PartNumber = :PartNumber;

Punctuation

SQL commands are always terminated with a semicolon (;).

Pascal Comments

You may insert Pascal comment lines within or between embedded SQL commands. A comment begins with { and closes with } or begins with (* and closes with *), as follows:

   EXEC SQL SELECT PartNumber, PartName

      {  put the data into the following host variables  }

   INTO :PartNumber, :PartName

      (* find the data in the following table *)

   FROM Purchdb.Parts

      {  retrieve only data that satisfies this search condition *)

   WHERE PartNumber = :PartNumber;

      (* end of command }

ALLBASE/SQL Comments

SQL comments can be inserted in any line of an SQL statement, except the last line, by prefixing the comment character with at least one space followed by two hyphens followed by one space:

EXEC SQL SELECT * FROM PurchDB.Parts   -- This code selects Parts Table values

         WHERE SalesPrice > 500.0;

The comment terminates at the end of the current line. (The decimal point in the 500 improves performance when being compared to SalesPrice, which also has a decimal; no data type conversion is necessary.)

General Rules for Embedding SQL

Technical documentation

» Table of Contents

» Index

Location of SQL Commands

Prefix and Suffix

Punctuation

Pascal Comments

ALLBASE/SQL Comments