Using MySQL from ADA program

Using MySQL from ADA program

[MITRONIC]

Hello, I want to access a MySQL database from my ADA program. What is the
best way? Anyone who can give me an example?

/Christer

Re: Using MySQL from ADA program

[Adrian Knoth]

 MITRONIC <mitronic@swipnet.se> wrote:

> Hello, I want to access a MySQL database from my ADA program. What is the
> best way? Anyone who can give me an example?

I can't tell you what to use for MySQL, but for PostgreSQL there are
several possibilities which should be comparable to MySQL.

www.adapower.com surely has at least one thick binding to MySQL.
Usage should be simple, but at the cost of very much overhead.

Second way is to have a look at gnade, the GNU Ada Database Environment,
which might be found at iirc gnade.sourceforge.net

Third way is to find a direct implementation of libmysql written in Ada,
probably using socket() via Pragma-Import from C or something else.

I guess there is no libmysql avaible which is not using some C.

Now following an example for PostgreSQL which won't work for MySQL but
it might give you an overview, how it'd like to be...

        procedure do_work (dbname : in string) is
                package Pg is new Pg2("dbname=" & dbname & " user=adi");
                ergebnis : Pg.Result_Type;
                buffer : UString;
        begin
         loop
                Put_Line("Enter an SQL-Command or <CR> to stop:");
                Get_Line(buffer);
                exit when (buffer = "");
                Pg.Submit(buffer,ergebnis);
                for I in 0 .. ergebnis.Tuples_Qt-1 loop
                        for J in 0 .. ergebnis.Fields_Qt-1 loop
                                Pg.Get(ergebnis,I,J,buffer);
                                Put(buffer & ASCII.HT);
                        end loop;
                New_Line;
                end loop;
                Pg.Forget(ergebnis);
         end loop;
         Put_Line("Finished");
         Pg.Disconnect;
        end do_work;



-- 
mail: adi@thur.de  	http://adi.thur.de	PGP: v2-key via keyserver

Lieber Erstattung als Bestattung

Re: Using MySQL from ADA program

[Preben Randhol]

On Wed, 28 Nov 2001 20:01:02 +0100,  MITRONIC wrote:
> Hello, I want to access a MySQL database from my ADA program. What is the
> best way? Anyone who can give me an example?

Check the GNADE Project :  http://gnade.sourceforge.net/

Preben
-- 
 ()   Join the worldwide campaign to protect fundamental human rights.
'||}
{||'                                           http://www.amnesty.org/

Re: Using MySQL from ADA program

[Preben Randhol]

On Thu, 29 Nov 2001 16:31:47 +0000 (GMT), M. A. Alves wrote:
>   This message is in MIME format.  The first part should be readable text,
>   while the remaining parts are likely unreadable without MIME-aware tools.
>   Send mail to mime@docserver.cac.washington.edu for more info.
> 
> ---1463811583-1957161748-1007051507=:21287
> Content-Type: TEXT/PLAIN; charset=US-ASCII

Stop send this to usenet lists.

> 
> On Wed, 28 Nov 2001,  MITRONIC wrote:
>>
>> Hello, I want to access a MySQL database from my ADA program. What is the
>> best way? Anyone who can give me an example?
> 
> A couple of months ago I had to do the same thing.  The existing bindings
> would not install nicely and/or were too complicated to use.  So I did a
> better one ;-)  It is a single package imaginatively named Mysql.  I
> attach the spec.  It includes a usage guide.  Feel free to ask for the
> body.  It is free for non-comercial use.

Did you try GNADE? Isn't it better if there is one standard library for
doing database stuff than several half hearted hacks?

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

Preben
-- 
 ()   Join the worldwide campaign to protect fundamental human rights.
'||}
{||'                                           http://www.amnesty.org/

Re: Using MySQL from ADA program

[M. A. Alves]

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

Re: Using MySQL from ADA program

[Preben Randhol]

On Thu, 29 Nov 2001 19:00:23 +0000 (GMT), M. A. Alves wrote:
> 
> I _will_ test the OBDC bindings out there someday.

From: http://gnade.sourceforge.net/#status

Binding to MySQL which is intended to provide access to the non ODBC
features of the MySQL data base.

License: GMGPL

Preben
-- 
 ()   Join the worldwide campaign to protect fundamental human rights.
'||}
{||'                                           http://www.amnesty.org/

Re: Using MySQL from ADA program

[M. A. Alves]

On Thu, 29 Nov 2001, Preben Randhol wrote:
> . . . http://gnade.sourceforge.net . . .

Yes, GNADE seems very nice now.  Very recent developments.  Finally one
application is announced, GSQL.  I'll check it out.  Thanks.

-- 
   ,
 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

Re: Using MySQL from ADA program

[M. A. Alves]

On Fri, 30 Nov 2001, M. A. Alves wrote:
> On Thu, 29 Nov 2001, Preben Randhol wrote:
> > . . . http://gnade.sourceforge.net . . .
>
> Yes, GNADE seems very nice now. . .

. . . but, unfortunately, it won't even build.  <<File "gnu.ads" not
found.>>

I must confess I am still under the impression GNADE is not working
anywhere outside its foundry.

Cheers,

-- 
   ,
 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

Re: Using MySQL from ADA program

[Pascal Obry]

"M. A. Alves" <maa@liacc.up.pt> writes:

> On Fri, 30 Nov 2001, M. A. Alves wrote:
> > On Thu, 29 Nov 2001, Preben Randhol wrote:
> > > . . . http://gnade.sourceforge.net . . .
> >
> > Yes, GNADE seems very nice now. . .
> 
> . . . but, unfortunately, it won't even build.  <<File "gnu.ads" not
> found.>>
> 
> I must confess I am still under the impression GNADE is not working
> anywhere outside its foundry.

Certainly not. I have been able to build many versions. I've not tried the
latest one though.

Pascal.

-- 

--|------------------------------------------------------
--| Pascal Obry                           Team-Ada Member
--| 45, rue Gabriel Peri - 78114 Magny Les Hameaux FRANCE
--|------------------------------------------------------
--|         http://perso.wanadoo.fr/pascal.obry
--|
--| "The best way to travel is by means of imagination"

Re: Using MySQL from ADA program

[Juergen Pfeifer]

"Pascal Obry" <p.obry@wanadoo.fr> schrieb im Newsbeitrag
news:u8zcndgtf.fsf@wanadoo.fr...
>
> "M. A. Alves" <maa@liacc.up.pt> writes:
>
> > On Fri, 30 Nov 2001, M. A. Alves wrote:
> > > On Thu, 29 Nov 2001, Preben Randhol wrote:
> > > > . . . http://gnade.sourceforge.net . . .
> > >
> > > Yes, GNADE seems very nice now. . .
> >
> > . . . but, unfortunately, it won't even build.  <<File "gnu.ads" not
> > found.>>
> >
> > I must confess I am still under the impression GNADE is not working
> > anywhere outside its foundry.
>
> Certainly not. I have been able to build many versions. I've not tried the
> latest one though.
>
> Pascal.
>

Well, from time to time I try to build GNADE on machines where I only have
GNAT and the required basic GNU tools and it usually builds without
problems. I do this of course to ensure that it builds outside its
foundry;-)

Please note that the current focus of GNADE is the implementation of an
embedded SQL precompiler that is reasonable close to the ISO92 specs. All
the native bindings for MySQL or PostgreSQL are more or less placeholders
for future efforts. The ESQL preprocessor is built on the ODBC binding and
this was a good decision because we were able to interface with a variety of
different databases on different platforms without changing a single line of
source code.

Nevertheless ESQL is not the most elegant way to integrate generic database
support with Ada95, although it is a reasonable approach for smaller apps.
And ODBC is fine as long as you don't have to scale for thousands of
simultaneous connections to your backend. But the GNADE project is open for
the discussion of better approaches.

Personally my favourite scenario is still to have a GNAT.NET compiler for
the .NET platform and then simply to reuse the very rich ADO.NET
architecture for data access from Ada. My 2c :-)

J�rgen

Re: Using MySQL from ADA program

[Michael Erdmann]

Have a look at:

http://gnade.sourceforge.net/


Michael


MITRONIC schrieb:

> Hello, I want to access a MySQL database from my ADA program. What is the
> best way? Anyone who can give me an example?
>
> /Christer