SQL Database Access

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

• ​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).

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.Values.Floats;
with League.Values.Integers;
with SQL.Databases;
with SQL.Queries;

with Matreshka.Internals.SQL_Drivers.SQLite3.Factory;
pragma Unreferenced (Matreshka.Internals.SQL_Drivers.SQLite3.Factory);
--  Drivers are linked with application, application must 'with' factory
--  packages to make them available.

procedure Example is

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

   DB_Type : constant League.Strings.Universal_String := +"SQLITE3";
   DB_Name : constant League.Strings.Universal_String := +"test.db";

   D : aliased SQL.Databases.SQL_Database
     := SQL.Databases.Create (DB_Type, DB_Name);

begin
   D.Open;

   --  Create table

   declare
      Q : SQL.Queries.SQL_Query := SQL.Queries.Create (D);

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

   --  Fill data

   declare
      Q : SQL.Queries.SQL_Query := SQL.Queries.Create (D);

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

   --  Retrieve data from table

   declare
      Q : aliased SQL.Queries.SQL_Query := SQL.Queries.Create (D);

   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.Values.Integers.Get (Q.Value (1)))
             & ":"
             & League.Values.Get (Q.Value (2)).To_Wide_Wide_String
             & ":"
             & Float'Wide_Wide_Image
                (League.Values.Floats.Get (Q.Value (3))));
      end loop;
   end;
end Example;