SQL Database Access

SQL Database Access module provides simple generic API to access to SQL databases. Currently supported databases:

• ​MySQL is the world's most popular open source database software (driver notes);
• ​Oracle is a well known commercial database management system (driver notes);
• ​PostgreSQL is a powerful, open source object-relational database system (driver notes);
• ​SQLite3 is a in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine (driver notes).
• ​Firebird is a relational database offering many ANSI SQL standard features that runs on Linux, Windows, and a variety of Unix platforms (driver notes);

API contains two packages. The SQL.Databases​ package provides SQL_Database type to connect to database server and manage transactions, and the SQL.Queries​ package provides SQL_Query type to prepare, bind, execute queries and retrieve results.

Example

This example creates database, creates table in the database, fills it and retrieve values from it.

with Ada.Wide_Wide_Text_IO;

with League.Strings;
with League.Holders.Floats;
with League.Holders.Integers;
--with Matreshka.Internals.SQL_Drivers.Firebird.Factory;
--with Matreshka.Internals.SQL_Drivers.MySQL.Factory;
--with Matreshka.Internals.SQL_Drivers.Oracle.Factory;
--with Matreshka.Internals.SQL_Drivers.PostgreSQL.Factory;
with Matreshka.Internals.SQL_Drivers.SQLite3.Factory;
with SQL.Databases;
with SQL.Options;
with SQL.Queries;

procedure Simple_SQL is

   function "+"
    (Item : Wide_Wide_String) return League.Strings.Universal_String
       renames League.Strings.To_Universal_String;

--   DB_Driver : constant League.Strings.Universal_String := +"MYSQL";
   --  MySQL

--   DB_Type : constant League.Strings.Universal_String := +"ORACLE";
--   DB_Name : constant League.Strings.Universal_String := +"scott/tiger@db";
   --  Oracle

--   DB_Driver : constant League.Strings.Universal_String := +"POSTGRESQL";
   --  PostgreSQL

   DB_Driver : constant League.Strings.Universal_String := +"SQLITE3";
   --  SQLite3

--   DB_Driver  : constant League.Strings.Universal_String := +"FIREBIRD";
--   DB_Options : constant League.Strings.Universal_String :=
--     +"SYSDBA/masterkey@localhost:/tmp/aaa";
   --  Firebird

   DB_Options : SQL.Options.SQL_Options;

begin
   --  MySQL

--   DB_Options.Set (+"database", +"test");

   --  PostgreSQL

   --  SQLite3

   DB_Options.Set (+"filename", +"test.db");

   declare
      D : aliased SQL.Databases.SQL_Database
        := SQL.Databases.Create (DB_Driver, DB_Options);

   begin
      D.Open;

      declare
         Q : SQL.Queries.SQL_Query := D.Query;

      begin
         Q.Prepare
          (+"CREATE TABLE point (x INTEGER, y CHARACTER VARYING (6), z FLOAT)");
         Q.Execute;
      end;

      declare
         Q : SQL.Queries.SQL_Query := D.Query;

      begin
         Q.Prepare (+"INSERT INTO point (x, y, z) VALUES (:x, :y, :z)");
         Q.Bind_Value (+":z", League.Holders.Floats.To_Holder (4.5));
         Q.Bind_Value (+":y", League.Holders.To_Holder (+"xyz"));
         Q.Bind_Value (+":x", League.Holders.Integers.To_Holder (5));
         Q.Execute;
      end;

      declare
         Q : aliased SQL.Queries.SQL_Query := D.Query;

      begin
         Q.Prepare (+"SELECT x, y, z FROM point");
         Q.Execute;

         while Q.Next loop
            Ada.Wide_Wide_Text_IO.Put_Line
             (Integer'Wide_Wide_Image
               (League.Holders.Integers.Element (Q.Value (1)))
                & ":"
                & League.Holders.Element (Q.Value (2)).To_Wide_Wide_String
                & ":"
                & Float'Wide_Wide_Image
                   (League.Holders.Floats.Element (Q.Value (3))));
         end loop;
      end;

      D.Close;
   end;
end Simple_SQL;