Embedded SQL
Embedded SQL ( embedded SQL, abbreviated ESQL ) standardizes the syntax for inserting SQL commands in programming languages. The embedding in Ada, C, C , COBOL, Fortran, MUMPS, Pascal, and PL / I will in Part 2 Foundation ( SQL / Foundation) and embedding in Java in Part 10 Object Language Bindings ( SQL / OLB ) of the SQL standards ISO / IEC 9075 specifies.
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 code of the host language, which is then translated by the compiler. (Some COBOL compilers contain a built-in 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 provided by each database vendor.
Because ESQL is standardized, with ESQL programs should - be used as long as no proprietary properties of the SQL dialect - be compatible with databases from different manufacturers, but only at the level of source code. This means that must be recompiled when changing the database make, sometimes even the version update the same database, the source code by the ESQL precompiler and compiler and linked against the current library. This differs ESQL of concepts with similar purpose such as ODBC or JDBC, where the change of the database ( in theory) be replaced only one driver and the compiled program can be used. However, there is at least one COBOL compiler, which converts the ESQL statements into calls to ODBC.
The advantage of this approach, 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 can not make any type checking.
Syntax
ESQL statements shall consist of:
EXEC SQL
The communication between SQL and the application program made through the program variables. If these program variables used in an SQL expression, a colon is put forward.
Example:
EXEC SQL SELECT first name, last name INTO: first name,: last_name FROM employee table WHERE = pnr: pnr; Before executing the SQL command variable ' pnr ' with a value must be assigned in the ambient language. After executing the SQL command, the variables are ' first name ' and ' last name ' assigned values - if ever a record was found.
In a database table columns can be defined with null values . In most programming languages ( with the exception of PL / SQL), variables can not be assigned zero values , but a variable is always assigned any value.
If you want to access table columns that contain null values , then indicator variables must be used. You have to be created of type int. This variable is set to a value less than 0 if the column in the database contains Null.
Example:
EXEC SQL SELECT first name, last name INTO: first name: i_vorname, : last_name: i_nachname FROM employee table WHERE = pnr: pnr; The variables ' first name ' and ' surname ' be here - used to hold the read values - as in the example above. In addition, enter the indicator variables ' i_vorname ' and ' i_nachname ' whether the first name and the last name was ever present. For example, if only the last name was registered and the first name unknown (zero) was, then was ' i_nachname ' to 0 and ' i_vorname ' -1 assigned.