Chapter 13. Introduction to Embedded SQL

The GNU Embedded SQL Translator for Ada 95 reads a Ada 95 source file containing an Ada 95 package which contains embedded SQL Commands. A typical code fragment which is embedded into a normal Ada 95 source text is shown below:

Example 13-1. Example for Embedded SQL

          EXEC SQL AT DB01x
	    SELECT LOCATION INTO :dep_location 
	       FROM departments
	       WHERE DEPTNO = :depno ;
 
         if SQLCODE not in SQL_STANDARD.NOT_FOUND then
            Put_Line( 
               "Employee : " & Trim(To_String(Name),Right)  & -- bug 
               " working in dep. " & INT'Image(depno)       & --
               " located at " & Trim(To_String(dep_location),Right) );
         end if;
         ..............
  
Embedded SQL commands are always preceded by the string EXEC SQL. According to ISO/92 all text following until the semicolon forms the query which has to be send to the DBCS. The communication between the SQL query and the application code is done by means of so called host variables. A host variable contains either a parameter as input to a query or the result of a query after the query has been executed by the DBCS. A host variable in an SQL query is marked by a preceding colon (':'). Host variables are declared in a specially marked declare section, where the ISO/92 standard allows only a limited number of data types which may be used for host variables. These data types are defined in the package SQL_STANDARD.

In order to communicate to data bases, ESQL uses in each ESQL statement an optional data base identifier. This identifier is assigned by means of a connect statement to a data base as shown below. First of all is the connection identifier declared to be DB01.

          ........
   EXEC SQL DECLARE DB01 DATABASE   ;
          .........
   begin
      EXEC SQL CONNECT "gnade"
                     IDENTIFIED BY "xxxxxxx"
                     BY DB01
                     TO "DEMO_DB" ;
   end;                                            
Later, during the initialization of the package, we connect as user "gnade" with the password "xxxxxx" to the database "DEMO_DB". The connection which will be used will be referred as DB01 in all ESQL statements. The name DEMO_DB refers to the data source name in the ODBC setup.