Connection Handling

In order to connect to a data base, the data base identifier to be used has to be defined first. This identifier is a simple name which may be used in the AT clause of an embedded SQL statement and is declared by means of the "declare_db_clause". This clause will insert at the source where the clause is invoked a Ada statement declaring a connection object.

Syntax:
   
      <connect_clause> ::= 
              CONNECT [ user ]
                 [ BY <Connection> ]
                 [ TO <db_name>  ]
                 [ AS <name> ]
                 [ IDENTIFIED BY  <password> ] 
    
                 [ ON [COMMUNICATION|ATHORIZATION|OTHER] ERROR 
                      [RAISE|GOTO|DO] <target> ]     

     <declare_db_clause> ::= 
              DECLARE <name> DATABASE  
           
As shown in the example below, the declare_db_clause may be used in the argument list of a procedure.

Example 14-3. Using DB connections as procedure arguments

      
      procedure Print_Employee(
         His_Number : Integer;
         EXEC SQL DECLARE DB01x DATABASE   ) is
         ---
         ...........
         ---
      begin
         empno := INT(His_Number);
 
         EXEC SQL WHENEVER NOT FOUND DO Not_Found;
 
         EXEC SQL AT DB01x
            SELECT NAME, DEPTNO INTO :name, :depno
               FROM employees
               WHERE EMPNO = :empno ;
 
         .....................
      end Print_Employee;                                       
   
This construct allows to write library packages using data base connections as arguments.

The 'ON' clause is used to define the handling of errors which may occure during connection. Please note, that the execution of a procedure is straigth forward, which means after the procedure returns the execution continues after the connect statement!

Implementation Note: The data base connection variable inserted by this statement has the name GNADE_DB_<db_name> and is of the type ESQL_Support.CONNECTION_Handle. Such a name should never be used in the application code.

There are situation where you might want to build a general purpose package which contains ESQL statements. In such a situation you may either pass the data base as an argument of each procedure/function of this package as shown below:

   procedure ...(
      EXEC SQL DECLARE DB DATABASE ) is
   begin
      EXEC SQL AT DB ........ ;
   end X;
   

If this is to expensive you may implement a common procedure which sets the database globaly.

   procedure Initialize(
      EXEC SQL DECLARE DB DATABASE ) is
   begin
      EXEC SQL AT MYDB 
         DATABASE IS DB ;
   end Initialize;
   

Implementation Note: Using this approach special you have to ensure that always the correct database is active before calling any operation from your utility package. This approach should be used if you are dealing with only one data base.