Velsen
Main St, Anywhere, USA 00000
1-111-222-3333

Hide Menu
gnatcoll_sqlite example
with Ada.Text_IO;         use Ada.Text_IO;
with GNATCOLL.SQL.Sqlite;
with GNATCOLL.SQL.Exec;   use GNATCOLL.SQL.Exec;

procedure Bis is
   -- Define the DB configuration descriptor and connection instance
   DB_Descr : Database_Description;
   DB_Conn  : Database_Connection;

   -- Query objects
   Query    : Prepared_Statement;
   Cursor   : Forward_Cursor;
begin
   -- 1. Setup the SQLite database environment pointing to your file
   --    (This creates the file if it does not already exist)
   DB_Descr := GNATCOLL.SQL.Sqlite.Setup ("example_database.db");

   -- 2. Establish a communication line (connection) to the database
   DB_Conn := DB_Descr.Build_Connection;

   -- 3. Execute basic DDL/DML statements directly on the connection
   Execute (DB_Conn, "CREATE TABLE IF NOT EXISTS users " &
                     "(id INTEGER PRIMARY KEY, name TEXT, role TEXT)");

   Execute (DB_Conn, "INSERT INTO users (name, role) VALUES ('Alice', 'Admin')");
   Execute (DB_Conn, "INSERT INTO users (name, role) VALUES ('Bob', 'Developer')");

   -- 4. Prepare and execute a parameterized SELECT query
   Query := Prepare ("SELECT id, name, role FROM users WHERE role = ?", Use_Cache => True);

   -- Bind criteria values into the '?' placeholder slots (0-indexed position)
   -- For this example, let's fetch all users, so we can ignore filtering or execute a fresh string:
   Query := Prepare ("SELECT id, name, role FROM users", Use_Cache => True);

   -- Fetch data using the cursor
   Cursor.Fetch (DB_Conn, Query);

   -- 5. Iterate through the query results
   Put_Line ("--- Database Records ---");
   while Has_Row (Cursor) loop
      -- Value mapping matches the requested column index (0 = id, 1 = name, 2 = role)
      Put_Line ("ID: " & Value (Cursor, 0) &
                " | Name: " & Value (Cursor, 1) &
                " | Role: " & Value (Cursor, 2));

      Next (Cursor);
   end loop;

   -- 6. Clean up resources explicitly
   Free (DB_Conn);
   Free (DB_Descr);

exception
   when E : others =>
      Put_Line ("An error occurred during database operations.");
      -- If a database descriptor or connection is still open, ensure they are handled.
end Bis;