Ada 95 Database Connectivity (ADBC)

Common Database API of the GNADE project for Ada 95

This Version:

$Header: /cvsroot/gnade/public_html/ado.html,v 1.20 2002/01/27 19:56:26 merdmann Exp $

Author(s):

Michael Erdmann <michael.erdmann@snafu.de>

Editor(s):

Michael Erdmann

Abstract

The GNADE project provides allready two ways of inter working between an application and a RMDBS:

• Using ODBC and embedded SQL
• Usage of native Ada 95 bindings (e.g. MySQL and PostgreSQL).

Since there there are also situations where it is not possible to use the ODBC interface a common API for accessing an RDBMS has to be provided. This interface is intended to be applicable for ODBC and all native database interfaces.

Status of this Document

This document is intended as a dicussion paper which contains only the concepts and general ideas of the ADO interface. All sections presented in this document are open to discussion and changes.

The document will be developed in the following stages:

• Stage 1 - Definition of the classes and there properties.
  During this phase the discussion will not show any implementation details for Ada 95. Only examples will be added in each sections to show the general idea .
  
• Stage 2 -Definition of the Ada 95 implementation.
  During this phase each section will be refined by adding the Ada 95 implementation of the API.

The document is fairly stable and parts of the implementation are ongoing. The current status may be viewd in the CVS. under /gnade/ado/.

Contents

Introduction

Defintions

Errorhandling and Exceptions

Hostvariables

Hostvaraible Types

Driver Objects

Connection Object

Queries

Result Sets

Result Rows

Introduction

The classical sequence of statements (pseudo code) using a data base in shown below:

 (0) empno : Integer_Parameter;
         .......
 (1) connection := Connect( username=>.., password=>.., database=>..., server=> ....);
 (2) statement := Prepare( connection, "select * from employees where empno = ?");
 (3) Bind( statement, 1, empno );
         .......
 (4) empno := 4711;
 (5) result := Execute( statement );
 (6) while not End_of( result ) loop
 (7)     Fetch( result );
 (8)     name := Get( result, 1 );
 (9)     boss := Get( result, 2 );
         .......
(10) loop;
(11) Drop( result );
(12) Close( connection );
 

Statement (1) establishes the connection between the client and the data base server. The result is a connection reference.

Statement (2) create a command which will be executed on the database server. Depending on the type of underlying data base or interface this command does nothing or send the query already to the data base server for preparation (parsing). The reason for this is that most of the queries are already known at initialization time of the application. To seperate parsing and execution may save time because the statement does not need to be parsed any more when it is executed.

Statement (3) binds the integer variable empno to the parameter 1 which is the qualifier for the empno in the relation.

Statement (4) gives the empno parameter a value and in (5) the query is executed. The execution returns a result set reference.

Satement (6) loops thought the result set and fetches record by record (7). The components (columns) of the result set are accessed via functions returning the correct data type.

Definitions

The server executes data queries formulated in SQL. These queries yielding so called result sets which are maintained at the server. The client may download these results record by record or in order to increase the efficiency in large blocks.

The client contains the actual application. In order to communicate with the data base server, the client has to connect to the server and authorize him self by means of user name and a password. Each connection plus all the state information in the server and client are called a session.

Object persistence means, that objects are available after the application has terminated.

Host variables are representations of SQL data elements in Ada 95.

Errorhandling and Exceptions

As a general concept, the API methods do not use result codes. If a precondition is violated, an API method may raise an exception.

Hostvariables (..ADBC.Hostvariable)

So called hostvariables are used for communication between the application and the RDBMS. The host variable class provides the base class for the implementation of several host variable type. A host variable has beside of its value adittional attributes as for example wether it is null or not.

Queries may contain so host variables as shown below:

select * from employees where emno = :emp_id;

The bind operation allows to bind host variables to the named host variables.

This package provides the attributes and methods as listed below:

Attributes:

Methods:

Exceptions:

Example:

  
   package Employees_Data is new Hostvariable( type => integer, statement => handle );
  
   empno : Hostvariable.Int;
   name  : Hostvariable.String(1..20);
   boss  : Hostvariable.String(1..50);
 
   handle := Prepare( "select empno, name  from employees where boss = :emp_no" );
   bind( handle, "emp_no", empno );

   rows := Execute( handle );
 
   ....
   Employees_Data.Statement( new_handle );

Hostvariable Types (..ADBC.Hostvariable.Types)

This package provides the implementation of host variables for the common SQL 92 types. The following types are available:

• SQL_String
• SQL_Integer
• SQL_Float

For each type the following interface is available. Since these implementation are based on the ..ADBC.Hostvariable package all methods documented there may be used as well.

Attributes:

Methods:

Exceptions:

Example:

 handle );
  
   

Driver Objects (ADO.Driver)

A driver objects are introduced in order to introduce an abstraction of the underlying data base. The usage of driver handles in the connection object allowes to perform a dynamic binding with the database during execution.

Attributes:

Methods:

Exceptions:

Example:

   with ADO;         use ADO;








   drv : ADO.Driver.Handle ...
   X   : Connection.Object( drv );
   ....
 

Connection Object (ADO.Connection)

A connection specifies the interface between an client and a server at the client side. The server is identified by the so called server address which referees to a host name.

Upon instantiation of an connection object, the connection is not established. It is only possible to establish a connection after all mandatory attributes have been set.

The connection object takes a driver handle a descriminant. This allows binding to the data base interface at execution time.

The connection object it self causes no explict exceptions, because the driver implementation generates all connection relevant exceptions by it self.

Attributes:

Methods:

Exceptions:

Example:

   with GNU.DB.ADO;     use GNU.DB.ADO

   drv : ADO.Driver.Handle ...
   X   : Connection.Object( drv );
   ....
   Connect( x, User => "gnade", password => "gnade.", database => "gnade_db"  );

Statements (ADO.Statement)

A statement is a command formulated in SQL to be executed by the underlying RDBMS. The statement class contains the syntactical elements of the statement broken up in a form that they can be handled by the driver.

A parameter is marked by a host variable indicator in the SQL command (e.g. select * from employees where name = :name; ). The statement breaks this string into chunks. Each chunk consits of a substring on the SQL statement and an varible name. The driver may use these sets to build a correct SQL query by performing the correct substituation (e.g. ODBC '?' or the literal for MySQL).

The instance is always created with reference to to the underlying connection.

Attributes:

Methods:

Exceptions:

Example:

   X : Connection.Object;

   Connect(X);






   declare
      v     : SQL_Integer;
      resul : Resultset_ID;
   begin
      S := Statement( q, "select * from employees where name = :empn" );
      Bind( s, "empno", v );     

      Value( v, 1234 );

      result := Execute( X, s ): 


      .....
   end ;

Result Sets (ADO.Resultset)

A result set is simply the collection of all record which are returned by a query. A query which returns 0 records is not assumed as an error. The result set maintains a so called read pointer, which point to the next element in the result set. In order to retrieve a row from the data base the Fetch method has to be used. This method supports also a block mode, where the Fetch operation may retrieve a block of rows in order to increase the performance of the client/server interface.

Attributes:

Methods:

Exceptions:

Example:

The example below read record by record the complete result set of a query

  
   subtype Name_Type is String(1..20);

   .....
   handle := Prepare( "select empno, name, firstname  from employees where boss = ?" );
  
   Result := Execute( handle );

   begin
      package D_Empno is new Numeric_Domain( result, "empno", number_type => Integer );
      package D_first is new String_Domain( result, "firstname", size => Name_Type'length);

      while not End_of_Result( result ) loop
         Fetch(Result);
         
         if not D_firstname.Is_Null then
            firstname := D_Firstname.Value;
            ....
         end if;
      loop;

Result Rows (ADO.Rows)

The result row contains one record out of the result set.. The between SQL formats and Ada 95 is done in this class.

Attributes:

Methods:

Exceptions:

Example:

      row  := Fetch( result );   -- fetch a result
      name := Get( row, 1 );
      boss := Get( row, 2 );

© 2001 Michael Erdmann