Syntax Element      In PL/I
prefix              EXEC SQL
terminator          ;
target              label constant or
                    PL/I label variable

Variable Definition Syntax

PL/I variable definition =
    { DCL | DECLARE }
    { host identifier | (host identifier.,..) }
    type spec [ character representation...] ;

type spec =
    { { CHAR | CHARACTER } [VARYING] (length)
    [CHARACTER SET [IS] character set name] }
  | { BIT [VARYING] (length) }
  | { type fixed decimal ( precision [ , scale ] ) }
  | { type fixed binary [ ( precision ) ] }
  | { type fixed binary ( precision ) }

type fixed decimal =
    { DEC | DECIMAL } FIXED | FIXED { DEC | DECIMAL }

type fixed binary =
    { BIN | BINARY } FIXED | FIXED { BIN | BINARY }

type float binary =
    { BIN | BINARY } FLOAT | FLOAT { BIN | BINARY }

SQL Type                 PL/I Type
BIT(length)              BIT(length)
BIT VARYING(length)      BIT VARYING(length)
CHAR(length)             CHAR(length)
DECIMAL                  FIXED DECIMAL
  (precision, scale)       (precision, scale)
FLOAT(precision)         FLOAT BINARY(precision)
INTEGER                  FIXED BINARY
SMALLINT                 FIXED BINARY
VARCHAR(length)          CHAR VARCHAR(length)
SQLCODE                  FIXED BINARY (PP)
                         where PP = the
                         implementation defined
                         precision for SQLCODE.
SQLSTATE                 CHAR (5)
indicator                FIXED BINARY

Syntax Element      In PHP
prefix              $sql = "
terminator          ";
target              unsigned integer (must
                    correspond to a valid label)

Variable Definition Syntax

PHP variable definition =
host identifier... : type spec;

type spec =
    BIGINT | BINARY | BIT | CHAR | DATETIME | DECIMAL
  | FLOAT | INT | NUMERIC | REAL | SMALLDATETIME
  | SMALLINT | TIMESTAMP | TINYINT | VARCHAR

SQL Type            PHP Type
BITINT              String
BINARY              Stream
BIT                 Integer
CHAR                String
DATETIME            Datetime
DECIMAL             String
FLOAT               Float
INT                 Integer
NUMERIC             String
REAL                Float
SMALLDATETIME       Datetime
SMALLINT            Integer
TIMESTAMP           String
TINYINT             Integer
VARCHAR             Stream
SQLCODE             INTEGER
SQLSTATE            CHAR(5)
indicator           INTEGER

Syntax Element      In MUMPS
prefix              &SQL(
terminator          )
target              statement label

Variable Definition Syntax

MUMPS variable definition =
    { numeric variable | character variable };

MUMPS character variable =
    VARCHAR { host identifier (length) }...

MUMPS numeric variable =
    { DEC [ ( precision [ , scale ] ) ] }
  | INT
  | REAL }
    host identifier...

SQL Type            MUMPS Type
CHAR                VARCHAR
DECIMAL             DEC
INTEGER             INT
REAL                REAL
VARCHAR             VARCHAR
SQLCODE             INTEGER
SQLSTATE            VARCHAR (5)
indicator           INT

Syntax Element      In Fortran
prefix              SQLstr = "
terminator          ";
target              label

Variable Definition Syntax

Type variableName = value;

    where Type is:
    boolean | char | double | float | int | long | short | string

SQL Type            Java Type
BIGINT              long
BLOB(n)             byte
CHAR(1)             char
CHAR                string
CLOB(n)             string
DATE                date
DOUBLE              double
GRAPHIC             string
INTEGER             int
REAL                float
SMALLINT            short
TIME                time
TIMESTAMP           timestamp
VARCHAR             string
VARGRAPHIC          string
SQLCODE             int
SQLSTATE            char
indicator           int

Syntax Element      In Fortran
prefix              EXEC SQL
terminator          none
target              unsigned integer (must be
                    the label of a statement
                    that appears in the same
                    program unit as the
                    WHENEVER clause)

Variable Definition Syntax

Fortran variable definition =
    type spec host identifier.,..

Fortran type spec =
    {BIT [ * length]}
  | { CHARACTER [ * length]
    [CHARACTER SET [IS] character set name] }
  | DOUBLE PRECISION
  | INTEGER
  | REAL

SQL Type            Fortran Type
BIT(n)              CHARACTER*L3
CHAR(n)             CHARACTER*n
DATE                CHARACTER*10
DECIMAL(p,s)        REAL*8
DOUBLE              REAL*8
INTEGER             INTEGER*4
NUMERIC(p,s)        REAL*8
REAL                REAL*4
SMALLINT            INTEGER*2
TIME                CHARACTER*8
TIMESTAMP           CHARACTER*26
VARCHAR(n)          requires conversion
SQLCODE             INTEGER
SQLSTATE            CHARACTER
indicator           INTEGER*4

Syntax Element      In COBOL
prefix              EXEC SQL
terminator          END-EXEC
target              section name or
                    unqualified paragraph name

Variable Definition Syntax

COBOL variable definition =
    {01|77} COBOL host identifier type spec [ character
    representation... ] .

COBOL type spec =
    bit type | character type | integer type | numeric type

COBOL binary integer =
    { PIC | PICTURE } [IS] { X [(length)] }...
    [ USAGE [IS] ] BINARY

COBOL bit type =
    { PIC | PICTURE } [IS] { B [(length)] }...

COBOL character type =
    [CHARACTER SET [IS] character set name]
    { PIC | PICTURE } [IS] { X [(length)] }...

COBOL computational integer =
    { PIC | PICTURE } [IS] S{9 [(length)] }...
    [ USAGE [IS] ] { COMP | COMPUTATIONAL }

COBOL integer type =
    binary integer | computational integer

COBOL numeric type =
    { PIC | PICTURE } [IS] S{9 [(length)] }...
    [ V [{9 [(length)] }...] ] | V{9 [(length)] }...
    [ USAGE [IS] ] DISPLAY SIGN LEADING SEPARATE

SQL Type                 COBOL Type
BIT                      requires conversion
BIT(n)                   PIC X(1)
CHAR(n)                  PIC X(n)
DATE                     PIC X(5)
DECIMAL(p,s)             PIC V9(s) COMP-3
DOUBLE PRECISION         COMP-2
FLOAT                    COMP-1 ( 6 digits of precision)
                         COMP-2 (16 digits of precision)
GRAPHIC(n)               PIC G(n) DISPLAY-1
INTEGER                  PIC S9(9) COMP
LONG VARGRAPHIC          05  name-group.
 (up to 16357 DBCS)          49 name-length PIC S9(9) COMP.
                             49 NAME PIC G(n) DISPLAY-1.
NUMERIC(p,s)             PIC S9(p-s)
REAL                     COMP-1
SMALLINT                 PIC S9(4) COMP
TIME                     PIC X(4)
TIMESTAMP                PIC X(11)
VARCHAR(n)               05  name-group.
 (up to 32767 chars)         49  name-length PIC S9(9) COMP.
                             49 NAME PIC X(n).
VARGRAPHIC(n)            05 name-group.
 (up to 127 DBCS)            49 name-length PIC S9(4) COMP.
                             49 NAME PIC G(n) DISPLAY-1.
SQLCODE                  computational integer
SQLSTATE                 character type
indicator                binary integer

Syntax Element      In C#
prefix              EXEC SQL
terminator          ;
target              label

Variable Definition Syntax

C# variable definition =
    [auto | extern | static] [const | volatile]
    C numeric variable | C character variable
  | C derived variable;

C# numeric variable =
    {long | short | int | decimal | double}
    {variable name [initial value] }.,..

C# character variable =
    char [CHARACTER SET [IS] character set name]
    {variable name {length} [initial value] }.,..

C# derived variable =
    { VARCHAR [CHARACTER SET [IS] character set name]
    {variable name {length} [= character representation... ] }.,..} |
    { BIT{variable name {length} [= character representation... ] }.,..}

The length is the fixed length or the maximum varying
length of the string, and the character representation
sequence is an optional initial value.

SQL Type            C# Type
BIGINT              long
BIT                 bool
CHAR                string
DATE                byte[]
DECIMAL             decimal
FLOAT               double
INTEGER             int
SMALLINT            short
TIME                byte[]
TIMESTAMP           byte[]
TINYINT             byte
VARCHAR             string
SQLCODE             long
SQLSTATE            char 6
indicator           short or long

Syntax Element      In C and C++
prefix              EXEC SQL
terminator          ;
target              label

Variable Definition Syntax

C and C++ variable definition =
    [auto | extern | static] [const | volatile]
    C numeric variable | C character variable
  | C derived variable;

C and C++ numeric variable =
    {long | short | float | double}
    {variable name [initial value] }.,..

C and C++ character variable =
    char [CHARACTER SET [IS] character set name]
    {variable name {length} [initial value] }.,..

C and C++ derived variable =
    { VARCHAR [CHARACTER SET [IS] character set name]
    {variable name {length} [= character representation... ] }.,..} |
    { BIT{variable name {length} [= character representation... ] }.,..}

The length is the fixed length or the maximum varying
length of the string, and the character representation
sequence is an optional initial value.

SQL Type            C and C++ Type
BIGINT              long long
CHAR(1)             char
CHAR(n)             char(n+1)
CLOB(n)             clob(n)
DATE                char(11) with null-terminator
DOUBLE              double
INTEGER             long int
REAL                float
SMALLINT            short int
TIME                char(9) with null-terminator
TIMESTAMP           char(27) with null-terminator
VARCHAR(n)          char[n+1]
SQLCODE             long
SQLSTATE            char 6
indicator           short or long

Syntax Element           In Ada
prefix                   EXEC SQL
terminator               ;
target                   label_name

Variable Definition Syntax

Ada variable definition =
    Ada variable name.,.. : Ada type spec
    [ = character representation...]

Ada type spec is given below.
The = character representation... sequence
  is an optional initial value.

The Ada variable definition is specified within the scope
of Ada WITH and USE clauses that specify the following:

with SQL_STANDARD;
use SQL_STANDARD;
use SQL_STANDARD.CHARACTER_SET;

This will import the following built-in SQL type definitions:

SQL_STANDARD.BIT (1..length)
SQL_STANDARD.CHAR [CHARACTER SET [IS]
  character set name] (1..length)
SQL_STANDARD.DOUBLE_PRECISION
SQL_STANDARD.INT
SQL_STANDARD.REAL
SQL_STANDARD.SMALLINT
SQL_STANDARD.SQLCODE_TYPE
SQL_STANDARD.SQLSTATE_TYPE

SQL Type                Ada Type
BIT(length)             BIT (1..length)
CHAR(length)            CHAR (1..length)
DOUBLE PRECISION        DOUBLE_PRECISION
INT                     INT
REAL                    REAL
SMALLINT                SMALLINT
SQLCODE                 SQLCODE_TYPE or INTEGER
SQLSTATE                SQLSTATE_TYPE or CHAR(5)
indicator               INDICATOR_TYPE

If you wish to UPDATE or DELETE a row in a host program, the DECLARE CURSOR must include FOR UPDATE. If columns are not stated, all columns can be updated. Syntax is:

     EXEC SQL
       DECLARE cursor_name [CURSOR FOR]
        SELECT column1, column2 ...
          FROM table_name ...
         WHERE ...
          [FOR UPDATE [OF column1, column2 ...]]
     END-EXEC

This SELECT FOR UPDATE can select only one table or view. DISTINCT, ORDER BY, and UNION can not be used. The DECLARE CURSOR FOR UPDATE would look like this:

     EXEC SQL
       DECLARE VENDOR_CSR
        SELECT VENDOR_NAME, STATUS
          FROM VENDOR_TBL
         WHERE COUNTRY = :SEARCH-COUNTRY
           FOR UPDATE OF STATUS
     END-EXEC

A column that is to be updated does not have to appear in the SELECT statement. The OPEN, FETCH, and CLOSE statements used for UPDATE or DELETE are identical to those used for retrieval. However, UPDATE or DELETE must have the phrase WHERE CURRENT OF. If WHERE CURRENT OF is not stated, the entire table will be updated or deleted.

     EXEC SQL
       UPDATE table_name
          SET column1 = value1,
              column2 = value2 ...
        WHERE CURRENT OF cursor_name
     END-EXEC

     EXEC SQL
       DELETE table_name
        WHERE CURRENT OF cursor_name
     END-EXEC

     EXEC SQL
       UPDATE VENDOR_TBL
          SET STATUS = :WS-STATUS
        WHERE CURRENT OF VENDOR_CSR
     END-EXEC