GNADE User's Guide: GNADE, The GNat Ada Database Environment; Version 1.4.2; Document Revision $Revision: 1.40 $ | ||
---|---|---|
Prev | Chapter 11. Embedded SQL Syntax Specification | Next |
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 11-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;