SQL can be executed in both interactive as well as embedded mode. Many RDBMs users are never directly confronted with SQL constructs. They interact with applications which hide the complexity of SQL. These applications are written in general-purpose programming languages such as Java, C or COBOL combined with SQL statements. There are two broad options for using SQL statements in general purpose programming languages.
- Statically embedded SQL: the SQL Statements are directly embedded in a host language. Ex: SQLJ allows embedding a SQL statement in a Java host program.
- In dynamically embedded SQL, SQL statements passed as parameters to procedure calls from a call level interface. Ex: JDCB provides a set of procedures which allows the java program to communicate with the relational database.
To successfully execute SQL statement sin both settings, we need some additional languages constructs for the interaction between SQL and the host language.
A first important aspect concerns the use of host language variables in embedded SQL queries. A host language variable is a variable declared in the host language. It can be used in embedded SQL instructions to exchange data between SQL and the host language. A host variable used to pass database data to the application is called an output host variable. A host variable that is assigned a value by the application code, after which this value can be used in an SQL statement, is called an input host variable. Embedded SQL allows for host variables in any position where interactive SQL would allow for a constant.
Because SQL is a set-oriented language, the query result will generally comprise multiple tuples. Host languages such as Java, C or COBOL are essentially record-oriented. Therefore, they cannot handle more than one record/tuple at a time. To overcome this impedance mismatch, the cursor mechanisms was introduced. Cursors allow for the tuples that result from an SQL query to be presented to the application code one by one. A cursor is to be declared and associated with a query. After that, the cursor allows to navigate through the tuples that make up the result of the query and offer them one by one to the application.
JDBC
The file MyApp.java contains the java source code of the database application. He uses the JDBC API to call the JDBC driver manager wich will use the vendor-specific JDBC driver to communicate to the unerlying relational database. A key advantage of JDBC is that it provides a uniform access interface to verious underlying data sources. Given its popularity, many database vendors provide support for it such as Microsoft, Oracle, IBM, etc. It is also supported by MySQL.
SQLJ
SQL statements can also be statically embedded in a host language. SQLJ is an example of this. It is built on top of JDBC and allows to embed of SQL statements directly into Java programs. A preprocessor will then translate the SQL statement into Java or JDBC statements which are then further compiled by the java compiler to byte code. The preprocessor can perform syntax check, and Java SQL type matching and verifies the query against the database scheme at the desired time. This will not only result in fewer errors at runtime but it also improves the performance of the query.
SQL delimiters separate the SQL instructions, to be processed by the precompiler from the native host language instructions. Each host language has its own SQL delimiter.
