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:

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:

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:

Attribute Description Ada 95 Type R/W
Is_Null Indicates that the viariable contains no valid value. Boolean R
Self Returns the pointer to the object it self. Handle R
Length Length of the data stored in the host variable. Positive R

Methods:

Method Description
Bind Bind a host variable to a name in the SQL statement

Exceptions:

Name Description
Is_Null This exception is raised if an attempt has been made to read the Value attribute..

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:

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:

Attribute Description Ada 95 Type M/O
Value Indicates that the viariable contains no valid value. See Types below R/W

Methods:

Method Description

Exceptions:

Name Description
.

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:

Attribute Description Ada 95 Type M/O
String M

Methods:

Method Description

Exceptions:

Method Description
Connection_Failure The connection has failed for some reason
Wrong_Database_Name The requested database does not exist
Empty_Resultset The resultset returned by the database system is empty
End_Of_Resultset There was an attempt to read beyond the resultset

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:

Attribute Description Ada 95 Type R/W
User The user name at the data base. String W
Password The password of the user to login to the data base server String W
Login_Time Defines the over all login time in seconds Integer
Server_Address Address of the server, normally a hostname in a LAN String W
Database_Name Name of a data base String W
Self The handle to the connection object it self. Handle R

Methods:

Method Description
Connect This establishes the connection to the data base system.
Disconnect Disconnect from the database server
Prepare Prepare a statement. This method creates a statement instance
Execute Execute a prepared statement

Exceptions:

Method Description

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:

Attribute Description Ada 95 Type R/W
R

Methods:

Method Description
Bind Bind a parameter to the given argument position.
Execute Execute the given SQL statement. If there is a previous query pending release context in the RDBMS. This method existis in two falwors. One which returns a statement handle in case there is a query executed and a second version which is used to executed commands which do not return any result set (e.g. SQL insert operations).
Prepare Prepare a statement and break it up into statement and argument chunks

Exceptions:

Name Description
Execution_Error A error during execution happend

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:

Attribute Description Ada 95 Type M/O
End_Of_Result Indicates if the fetch cursor has reached the end of the result set Boolean R
Mode The fetch mode controls the behavior of the cursor in the result set. Modes are:
  1. Next, Previous
  2. First, Next
R/W
Attribute This is the external name of a colon in a record of the result set. String R/W

Methods:

Method Description
Fetch Fetches the next record in the result set. It returns a row reference. The behavior of the fetch operations may be controlled by the fetch mode. This method return as a result a result row object..

Get Get the named field from the resultset where the read cursor is currently positioned on.

Exceptions:

Name Description
Server_Error
End_of_Resultset

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:

Attribute Description Ada 95 Type M/O

Methods:

Method Description

Exceptions:

Name Description
Wrong_Column The column requested in the Get/Set operation is not available
Row_Empty The row does not contain any result data, e.g. no Fetch operations has been executed previously.

Example:

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


2001 Michael Erdmann