Version 18 (modified by 9 years ago) ( diff ) | ,
---|
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;