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.

Thread safety

It is safe to use SQL_Database object and associated SQL_Query objects from any task (thread) till non of them are accessed simultaneously. So, SQL_Database and SQL_Query objects can be created in one thread and used in another, but any objects can't be used from different threads in the same time.

It is application's responsibility to protect object from access from different threads.

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;