Dynamic SQL

Currently only the syntax for dynamic SQL is supported. The idea of dynamic SQL is that the application can generate a query by generating a string. This query is executed by the data base and the application may access the result set. This can be achieved by either using the ODBC bindings directly or by using the dynamic SQL constructs as they are provided by the embedded SQL translator.

The name of a statement (<statement_name>) is defined in a DECLARE clause. Each dynamic SQL command is identified by such a name.

As for ODBC, the esql translator provides a prepare and an execute method. With the prepare clause the query is sent to the underlying data base system, but no result set is yet created. This very much comparable with declaring a cursor. After the query has been prepared, the query is executed by means of the execute clause.

     <dynamic sql clause > ::= 
            <prepare clause> 
          | <execute clause>
          | <close statement clause>
          ;
   

The prepare clause takes as input the statement name and the query string, which is simply a Ada 95 string variable. Any parameters in the query are marked by means of a '?' character. The host variables of the parameters are listed in the USING clause the the prepare statement.

     <prepare clause> ::= 
           'PREPARE' <statement_name>
              'FROM' { <name> | <string> }
              [ 'USING' <hostvars> ]
           ;
   

The execute clause takes the name of the statement as input for execution. If the USING section in the prepare clause was not included, the parameters of the statement may be assigned latest at this point via the USING clause in this statement.

     <execute_clause> ::= 
           'EXECUTE' <statement_name>
           [ 'USING' <hostvars> ]
           ;
   

The close statement clause is used to close the cursor associated with the statement it sef.

     <close statement clause > ::= 
           'CLOSE' 'STATEMENT' <statement_name>
           ;
   

The result set of the execute is accessed via the FETCH clause as for normal cursors as shown in the following example.

Example 14-6. Using dynamic SQL

   
   EXEC SQL END DECLARE SECTION END-EXEC
 
   EXEC SQL DECLARE test_sql  STATEMENT ;
 
   S   : constant String := "SELECT NAME FROM employees WHERE EMPNO = ?";
 
begin
 
   EXEC SQL CONNECT $DBUSER
            IDENTIFIED BY $DBPASSWD
            BY DB01
            TO $DBSOURCE ;
 
   EXEC SQL AT DB01
      PREPARE test_sql
      FROM S
      USING :EMPNO ;
 
   EMPNO := 5;
   EXEC SQL AT DB01
      EXECUTE test_sql
      USING :NAME :NAME_IND ;
 
   loop
      EXEC SQL AT DB01
         FETCH USING STATEMENT test_sql
         INTO  :name :name_ind  ;
 
         exit when SQLCODE in SQL_STANDARD.NOT_FOUND;
 
         Put_Line( "Result " & To_String( name ) );
   end loop;

   CLOSE STATEMENT test_sql;