MySQL bindings for Ada 95

Contents

• Abstract
• Package Description
• Package Usage
• Restrictions
• Example
• Package Specification
• Download

Abstract

This articles describes a binding to the MySQL data base as it is available for the Linux environement. The implementation depends on the MySQL client interface. The interface defined in this package does not depend on the MySQL interfaces. As a result applications written for this binding do not depend on the underlying data base.

Package description

The basic protocol for accessing a data bases which is supplorted by the DB interface looks like this:

• Connect to the server as a user.
• Select a Data Base
• Perform Query, perform manipulations etc..
• Drop Query
• Disconnect from server

SQL Queries sent as SQL command strings to the server. The MySQL server response with a result context where all components are represented as strings. The translation of these strings into Ada 95 data types is performed by the MySQL bindings.

Usage

The connection to the data base server is established by means of the Connect method. The server connection is represented by the DB.Object data type. The typical startup code looks like this:

Initialize( dBase );
User( dBase, "ACDE");
Connect( dBase, "localhost");

This fragment initializes the data object and establishes connection the data base server user user ACDE on the local host. If the connect failes, the Connect_Error exception is thrown. After the connection has been establihed, the data base TestDB is selected:

Select_DB( dBase, "TestDB" );   

Queries are performed by sending SQL queries to the server as text strings. I.e a query for the table test which returns all components of the table:

   qID := Query( dBase, "select * from Test");

   while true loop
      begin
         ... Do some job with the query result
         Next( dBase, qID );                   -- get next row of the query

      exception
         when Field_Parse_Error => 
            Put_Line("Field parse error");
            Next( dBase, qID );
         when Others =>
            raise;
      end;
    end loop;

This fragment simply performs a query and then processes all tuples found as a result of the query. It is strongly recomended to handle the exceptions caused by the Next operation correctly. The No_More_Rows exceptions is thrown by the Next primitive if no more result tupples are available.

The excpetion Field_Parse_Error is thrown by the Field methods which are used to access the contents of a result row if for some reason the data in the data bases is missformated. The following field types are supported:

Notes

Restrictions

• The current implementation does not comply to the ASCL rules.
• Several types are not yet supported. (supported types )

System Dependencies

Example

This examaple connects to the MySQL server and performs some work on all tupples of the table Test in the database named TestDB.

with Ada.Text_IO;            use Ada.Text_IO;
with Ada.Strings.Unbounded;  use Ada.Strings.Unbounded;
with Ada.Calendar;           use Ada.Calendar;
with DB; use DB;
procedure Test is
   dBase      : DB.Object;
   qId        : DB.Query_ID;
   Nbr_Tuples : Integer := 0;
begin	
   Initialize( dBase );
   User( dBase,      "ACDE");
   Connect( dBase,   "localhost" );      

   Select_DB( dBase, "TestDB" );
   --------------------------------------------------------------------------------------

   begin   
      qID := Query( dBase, "select * from Test");
      while true loop
         declare
	      Insert_Time : Time;
	   begin
 	       Nbr_Tuples := Nbr_Tuples + 1;
	       Put_Line( "'" & To_String( String_Field( dBase, qId, "id" ) ) & "'" );
    	       Insert_Time := Date_Field( dBase, qID, 2);
             Next( dBase, qID );
	    exception
	       when Field_Parse_Error =>
	          Put_Line("Field parse error");
	          Next( dBase, qID );
	       when Others =>
	          raise;
	    end;
      end loop;
   exception
      when Field_Type_Error =>
      	   Put_Line("Field type error");
      when No_Query_Result =>
      	   Put_Line("Query Failed");
      when No_More_Rows =>
         Put_Line( "End of Query Tuples:" & Integer'Image( Nbr_Tuples ) );

      when The_Error : Others =>
         Put_Line( "An error has occured at:" & Integer'Image( Nbr_Tuples )  );

   end;
   Drop_Query( dBase, qID );
   Finalize( dBase );
end Test;

Package Specifications

with Ada.Strings.Unbounded; use Ada.Strings.Unbounded;
with Ada.Calendar;          use Ada.Calendar;

package DB is
   ---=====================================================================---
   ---===             C O M P O N E N T   I N T E R F A C E             ===---
   ---=====================================================================---

   type Object is tagged private;
   type Handle is access Object'Class;

   procedure Initialize( this  : in out Object'Class);
   procedure Finalize( this : in out Object'Class );

   Usage_Error     : exception;
   Not_Initialized : exception;

   type    Query_ID is new Positive range 1..10;
   subtype Field_Number is Integer range 1..100;
   subtype Blob is String;

   No_Such_Name    : exception;
   ---=====================================================================---
   ---===                  A T T R I B U T E S                       ===---
   ---=====================================================================---

   procedure User( this : in out Object'Class;  Name : in String );


   ---=====================================================================---
   ---===                     M E T H O D S                             ===---
   ---=====================================================================---


   ---------------------------------------------------------------------------
   --| Description    : Connect to the given server, use the user name which
   --|              	has been set by the User Attribute.
   --| Preconditions  : none.
   --| Postconditions : Server is connected
   --| Exceptions     :
   --| Note           :
   ---------------------------------------------------------------------------  
   procedure Connect( this : in out Object'Class; Server : in String );

   Connect_Failure : exception;

   ---------------------------------------------------------------------------
   --| Description    : Disconnect from the server.
   --| Preconditions  : connected to the server
   --| Postconditions : disconnected from the server
   --| Exceptions     : None
   --| Note           : -
   ---------------------------------------------------------------------------

   procedure Disconnect( this : in out Object'Class );

   ---------------------------------------------------------------------------
   --| Description    :	Select a database at the server
   --| Preconditions  :	Connected to a server
   --| Postconditions : 
   --| Exceptions     : 
   --| Note           : 
   ---------------------------------------------------------------------------

   procedure Select_DB( this : in out Object'Class; Name : in String ); 

   
   ---------------------------------------------------------------------------
   --| Description    : Perform an sql query. Input is a normal mysql query
   --|                  string.
   --| Preconditions  :
   --| Postconditions :
   --| Exceptions     :
   --| Note           :
   ---------------------------------------------------------------------------                                                                  

   function Query( this: in Object'Class; query : in String ) return Query_ID;

   No_Query_Result : exception;
   ---------------------------------------------------------------------------
   --| Description    : Drop the query
   --| Preconditions  :
   --| Postconditions :
   --| Exceptions     :
   --| Note           :
   ---------------------------------------------------------------------------                                                               

   procedure Drop_Query( this : in out Object'Class; id : in Query_ID );

   ---------------------------------------------------------------------------
   --| Description    : Perform an sql query.
   --| Preconditions  :
   --| Postconditions :
   --| Exceptions     :
   --| Note           :
   ---------------------------------------------------------------------------                

   procedure Next( this : in out Object'Class; query : in Query_ID);

   No_More_Rows : exception;
   ---------------------------------------------------------------------------
   --| Description    : Get the field number from a name.
   --| Preconditions  :
   --| Postconditions :
   --| Exceptions     :
   --| Note           :
   ---------------------------------------------------------------------------  

   function Get_Field_Number( this : in Object; id : in Query_ID; name : in String )
                                                            return Field_Number;					     

   ---------------------------------------------------------------------------
   --| Description    : Get the field of the current colume and return the 
   --|                  specified data type. Access via field number or a 
   --|                  field name is provided for all data types
   --| Preconditions  :				  
   --| Postconditions :
   --| Exceptions     :
   --| Note           :
   ---------------------------------------------------------------------------

   Unknown_Field     : exception;
   Field_Type_Error  : exception;
   Field_Parse_Error : exception;   
   function String_Field( this : in Object; id : in Query_ID; name : in String ) 
                                                      return Unbounded_String;				              

   function String_Field( this    : in Object; 
                          id      : in Query_ID;
                          fNumber : in Field_Number ) return Unbounded_String;

            
   function Date_Field( this      : in Object; 
                        id        : in Query_ID; 
                        fNumber   : in Field_Number ) return Tim
   function Date_Field( this      : in Object; 
                        id        : in Query_ID; 
                        name      : in String ) return Time;
           
   function Blob_Field( this      : in Object; 
                        id        : in Query_ID; 
                        fNumber   : in Field_Number ) return Blob;
   function Blob_Field( this      : in Object;
                        id        : in Query_ID; 
                        name      : in String ) return Blob;

   function Integer_Field( this   : in Object; 
                        id        : in Query_ID; 
                        fNumber   : in Field_Number ) return Integer;
   function Integer_Field( this      : in Object; 
                        id        : in Query_ID; 
                        name      : in String ) return Integer;

   function Float_Field( this      : in Object; 
                         id        : in Query_ID; 
                         fNumber   : in Field_Number ) return Float;
   function Float_Field( this      : in Object; 
                         id        : in Query_ID; 
                         name      : in String ) return Float;


end DB;