Embedded SQL

Embedded SQL ( English for embedded SQL , ESQL for short ) standardizes the syntax for inserting SQL commands in programming languages. The embedding in Ada , C , C ++ , COBOL , Fortran , MUMPS , Pascal and PL / I is in Part 2 Foundation (SQL / Foundation) and the embedding in Java in Part 10 Object Language Bindings ( SQL / OLB ) of the SQL Standard ISO / IEC 9075 specified.

technical description

ESQL statements are inserted into the sequence of regular statements of the programming language (= host language). A precompiler converts the ESQL statements into normal host language code, which is then translated with its compiler . (Some COBOL compilers contain an integrated ESQL precompiler.) The converted code contains calls to the interface to the database . This interface must be linked as a library in the finished program . The library depends on the database used and is usually supplied by the respective database manufacturer.

Since ESQL is standardized, programs with ESQL - as long as no proprietary properties of the respective SQL dialect are used - should be compatible with databases from different manufacturers, but only at the level of the source code . This means that when the database brand changes, sometimes even when the version of the same database is updated, the source code must be recompiled by the ESQL precompiler and compiler and linked to the current library. This distinguishes ESQL from concepts with a similar purpose such as ODBC or JDBC , where when changing the database (in theory) only one driver is changed and the compiled program can continue to be used. However, there is at least one COBOL compiler that converts ESQL statements into calls to ODBC.

The advantage of this approach to the database connection is that at compile time not only the SQL syntax but also the type compatibility of the interface variables can be checked against the data types of the database system. Runtime-bound architectures such as ODBC , JDBC , ADO or ADO.NET cannot do a type check.

syntax

ESQL statements are made up of:

EXEC SQL <sql-statement> <terminator>   für Nicht-Java
#sql { <sql-statement> };               für Java

z. B. COBOL:

EXEC SQL <sql-statement> END-EXEC.

z. B. C, C ++, Ada:

EXEC SQL <sql-statement>;

programming

The communication between SQL and the application program takes place via program variables. If these program variables are used in an SQL expression, they are preceded by a colon.

Example:

EXEC SQL
SELECT vorname, nachname
INTO :vorname, :nachname
FROM mitarbeitertabelle
WHERE pnr = :pnr
;

Before executing the SQL command, the variable 'pnr' must be assigned a value in the surrounding programming language. After executing the SQL command, the variables 'first name' and 'last name' are assigned values - if a record was found at all.

Columns with Nullvalues can be defined in a database table . In most programming languages, variables cannot be assigned Nullvalues, but a variable is always assigned some value.

If you want to access table columns that also Nullcontain values, then indicator variables must be used. They must be created of the type int. This variable is set to a value less than 0 if the column is in the database Null.

Example:

EXEC SQL
SELECT vorname, nachname
INTO :vorname :i_vorname, :nachname :i_nachname
FROM mitarbeitertabelle
WHERE pnr = :pnr
;

The variables 'first name' and 'last name' are used here - as in the example above - to record the read values. In addition, the indicator variables 'i_first name' and 'i_lastname' indicate whether the first name and surname were even available. If z. NullE.g. only the last name was saved and the first name was unknown ( ), then 'i_lastname' was assigned 0 and 'i_first name' was assigned −1.

literature

ISO 9075 Part 1: Framework . 2nd Edition. ISO / IEC, 2003 ( PDF reference number: 9075-1: 2003 (E), sections: 4.8.1, 5.3.3.1).
ISO 9075 Part 2: Foundation . 2nd Edition. ISO / IEC, 2003 ( PDF reference number: 9075-2: 2003 (E), Chapter: 20 Embedded SQL).
ISO 9075 Part 3: Call-Level Interface . 3. Edition. ISO / IEC, 2003 ( PDF reference number: 9075-3: 2003 (E), Appendix B.1 (translation examples from embedded SQL to low-level library function calls)).

Web links

ECPG - Embedded SQL in C . PostgreSQL Documentation, (Description of the PostgreSQL implementation of Embedded SQL).


Instructions and Clauses	As • Case • Create • Delete • From • Having • Insert • Join • Merge • Null • Order by • Prepare • Select • Truncate • Union • Update • Where • With
variants	PL / SQL • Transact-SQL • OpenSQL • Embedded SQL • PostgreSQL