Re: Using MySQL from ADA program

["M. A. Alves" <maa@liacc.up.pt>]

On Thu, 29 Nov 2001, Preben Randhol wrote:
> Did you try GNADE? Isn't it better if there is one standard library for
> doing database stuff than several half hearted hacks?

Some years ago when I needed to bind Postgres I examined the landscape
very carefully---and decided to do it myself. Curiously enough, the
example for Postgres posted by Adrian Knoth previously on this thread uses
my solution.

Recently I needed to bind Mysql and now I just skimmed the landscape with
the previous pattern in mind.  The result was similarly dissatisfying, but
perhaps I missed something: you can surely correct my view by pointing out
one real example of an Ada program accessing a MySQL server via GNADE or
another solution (that is not my package).

I agree it is better to have one standard database library... that works.

I _will_ test the OBDC bindings out there someday.

> And please stop attaching BASE64 encoded attachments to usenet. Either
> send it by mail to the one that need it or give an URL.

I thought MIME was everywhere by now. Sorry. Here is the package spec in
plain text:

-- mysql.ads
-- (c) 2001 M�rio Amado Alves
-- Email: maa@liacc.up.pt

with System; use System;
with System.Address_To_Access_Conversions;
with Interfaces.C; use Interfaces.C;

package MySQL is

  type Connection_Type is private;
  type Action_Type is access procedure(Connection: in out Connection_Type);

  procedure Open
    (Connection : out Connection_Type  ;
     Host       : in  String  := ""    ;
     Port       : in  Natural := 0     ;
     Socket     : in  String  := ""    ;
     User       : in  String  := ""    ;
     Password   : in  String  := ""    ;
     Compress   : in  Boolean := False ;
     SSL        : in  Boolean := False ;
     ODBC       : in  Boolean := False );

  procedure Close
    (Connection : in out Connection_Type);

  procedure Execute
    (Connection : in out Connection_Type;
     Command : in String);

  procedure For_Each
    (Connection : in out Connection_Type;
     Action : in Action_Type);

  function Get
    (Connection : in Connection_Type;
     Field : in Positive)
    return String;

  function Escape(S : String) return String;

  -- PACKAGE-SPECIFIC EXCEPTIONS
  Break                   : exception;
  Null_Value              : exception;
  No_Room_For_Connection  : exception;

  -- EXCEPTIONS ASSOCIATED WITH ERROR CODES
  Unknown_Error           : exception;
  Socket_Create_Error     : exception;
  Connection_Error        : exception;
  Host_Error              : exception;
  IP_Socket_Error         : exception;
  Unknown_Host            : exception;
  Server_Gone             : exception;
  Version_Error           : exception;
  Out_Of_Memory           : exception;
  Wrong_Host_Info         : exception;
  Localhost_Connection    : exception;
  TCP_Connection          : exception;
  Server_Handshake_Error  : exception;
  Server_Lost             : exception;
  Commands_Out_Of_Sync    : exception;
  Named_Pipe_Connection   : exception;
  Named_Pipe_Wait_Error   : exception;
  Named_Pipe_Open_Error   : exception;
  Named_Pipe_State_Error  : exception;
  Cannot_Read_Charset     : exception;
  Net_Packet_Too_Large    : exception;

  -- BOTH!
  Undocumented_Error_Code : exception;

private

  type Array_Of_Lengths is array(Positive) of Unsigned_Long;
  type Array_Of_Values is array(Positive) of Address;

  package Convert_Lenghts is new
    System.Address_To_Access_Conversions(Array_Of_Lengths);

  package Convert_Values is new
    System.Address_To_Access_Conversions(Array_Of_Values);

  type Connection_Type is record
    C_Connection : Address;
    C_Result     : Address;
    Lengths      : Convert_Lenghts.Object_Pointer;
    Values       : Convert_Values.Object_Pointer;
  end record;

end MySQL;

-- USAGE GUIDE
--
-- This package provides communication with a MySQL.  Mandatory order
-- of subprogram calls:
--
--   Open --> Execute --> (For_Each --> Action --> (Get)) --> Close
--              /|\                                        |
--               |_________________________________________|
--
-- The sequence in parenthesis is associated with a row-returning Execute
-- call.  Violation of this is bound to raise Commands_Out_Of_Sync.
--
-- The Command parameter of Execute must be a valid SQL command. Violation
-- raises Undocumented_Error_Code with a message containing the code and
-- the C API message (in this order separated by a space).
--
-- Procedure For_Each iterates over all rows that have resulted from the
-- last call to Execute.  The user-defined Action procedure is invoked
-- upon each iteration.
--
-- Function Get is available for use in Action: in the course of each
-- iteration, Get returns the non-NULL value of the indexed field,
-- or else (i.e. the value is NULL) it raises Null_Value.  Note field
-- indexes start at 1.  Also, attention: there is no upper bound check
-- for field indexes!
--
-- Exception Break raised in Action exits the traversal immediately
-- (and it is not propagated).

-- REVISION HISTORY (see also body)
-- 2001-10-23: started (spec and body)
-- 2001-10-24: decided for single package and procedures Open/Close
-- 2001-10-25: fleshed Open, Close, Execute; defined exceptions
--   (defined associated named numbers and procedure Assess... in body)
-- 2001-10-29: decided for For_Each; completed and tested
-- 2001-10-31: cosmetics
-- 2001-11-06: function Escape
-- 2001-11-23: added exception No_Room_For_Connection; cosmetics

-- 
   ,
 M A R I O   data miner, LIACC, room 221   tel 351+226078830, ext 121
 A M A D O   Rua Campo Alegre, 823         fax 351+226003654
 A L V E S   P-4150-180 PORTO, Portugal    mob 351+939354002