Chapter 22. MySQL bindings

Table of Contents
The MYSQL API
Building programs with MySQL

The following example is stored under ./samples/mysql. It requires the installed client libraries of the MySQL product.

The MYSQL API

An instance of the MySQL.Object type represents the data base on application level. All operations on the data base are performed on this data type.

Each issued query is identified by a query id which issued to refer to the query.

Every program has to connect and authorize at the data base. This is done by the Methods User, Password and Connect as shown in the example below.

The data base is selected by the primitive Select_DB.

Example 22-1. MySQL native binding - Connecting to the database.


with GNU.DB.MySQL;           use GNU.DB.MySQL;
with GNU.DB;                 use GNU.DB ;


   dBase      : MySQL.Object;
   qId        : MySQL.Query_ID;

begin
   Initialize( dBase );

   User(     dBase, "gnade" );
   Password( dBase, "" );
   Connect(  dBase, "localhost" );

   Select_DB( dBase, "testdb" );
   
       

A query is send by the method Query to the data base. The query string is a normal SQL query or DML command. The result set of a query is described by a so called query identifier with the type Query_ID. The result set is generated at the time where the Query method is executed.

Example 22-2. MySQL native binding - Executing a query

   .....
   qID := Query( dBase, "select * from Test where id='Otto'");
   Put_Line( "Nbr of Rows:" & Integer'Image(Nbr_of_Rows(dBase, qID)) );
   ....
      

The result set may be reed out by means of the Next method as it is shown below.

Example 22-3. MySQL native binding - Accessing the result set

      ........
      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;
      .........
      

After the result set has been processed the query context has to be returned to MySQL via the Drop_Query method.

If the application intends to disconnect completely, the data base instance should be Finalized as shown below.

Example 22-4. MySQL native binding - Dropping the query

   .....
   Drop_Query( dBase, qID );

   Finalize( dBase );
   .....
      

NOTE: All data types are allocated a part of the result set. The only known deviation are blobs. The function Get_Blob_Field returns a dynamically allocated by array which need the be deallocated by the application explicitly by means for the Free procedure. If this is not done your application will loose memory.