Gem #130 : Type-Safe Database API - Part 2

Let's get started...

The first Gem in this series discussed how to write syntactically correct and type-safe SQL queries. We now need to execute these queries on the DBMS of choice and retrieve the results. This Gem explains how to use the DBMS-agnostic API in GNATColl to do this.

Currently, GNATColl provides support for two DBMS: PostgreSQL and SQLite. It is extensible to other DBMS by overriding certain primitive operations (see the documentation in gnatcoll-sql-exec.ads for more information).

When you compile GNATColl, it automatically detects whether any of the supported DBMS are installed on your system and optionally compiles the support for them. But, of course, when you link your application with GNATColl you do not want to systematically depend on every DBMS that is recognized by GNATColl (for instance both PostgreSQL and SQLite), just the one you actually need. Using project files, you can let the linker know which DBMS you need. Here is an example of a project file that adds support for SQLite:

with "gnatcoll_sqlite";
project Default is
   for Main use ("main.adb");
end Default;

In the Ada code, we now need to connect to the actual DBMS. This is the only place in the code that will explicitly mention which DBMS we are using. So porting your application from SQLite to PostgreSQL is merely a matter of changing this code. The rest of the application is unaffected.

with GNATCOLL.SQL.Sqlite;   -- or PostgreSQL
declare
   DB_Descr : GNATCOLL.SQL.Exec.Database_Description :=
      new GNATCOLL.SQL.Sqlite.Setup ("dbname.db");
   DB : GNATCOLL.SQL.Exec.Database_Connection;
begin
   ...
end;

The parameters to Setup depend on the DBMS. For instance, PostgreSQL lets you specify the host, username, and password.

DB_Descr is just a description of the future connections. To get an actual connection, there are a number of possibilities:

1. If your application is not multi-tasking, you can use the following:

  DB := DB_Descr.Build_Connection;  

2. If, however, you are using multitasking, it might be better to always retrieve the same connection from within a given task. For instance, a web server generally uses one task per HTTP query, and we can retrieve the task-specific connection with:

  DB := GNATCOLL.SQL.Exec.Get_Task_Connection (DB_Descr);  

3. Finally, it is possible to have a pool of such connections, which will be explained in the third part of this Gem series.

At this point, we still haven't connected to the DBMS, but this is done automatically the first time we execute a query. If for some reason the connection was broken (say, due to a network error), GNATColl automatically tries to reconnect a number of times before giving up.

Let's now execute our first query:

declare
   Q : constant SQL_Query := ... ;   -- See first Gem in the series
   R : Forward_Cursor;
begin
   R.Fetch (Connection => DB, Query => Q);
end;

R will contain the actual result. There are two possible types for R: a Forward_Cursor can only be iterated one row at a time; when you move to the next row, the previous one is lost for good. In exchange for this limitation, GNATCOLL.SQL does not have to retrieve all results in memory at once, which can be more efficient if you intend to stop the iteration early. The second type is a Direct_Cursor, which retrieves all rows at once, keeps them in memory, and lets you traverse them forward or backward, or even jump to a specific row directly. This is more flexible, but requires more memory in your application.

Printing the results can be done using the familiar cursor-based loop idiom:

   while Has_Row (R) loop
      Put_Line (Integer'Image (Integer_Value (R, 0))  -- First field
                & ' ' & Value (R, 1));  -- Second field, as a string
      Next (R);
   end loop;

We need to know the index of each field in the query, and whether we need to retrieve it as an integer, a string, a Boolean, etc. The third Gem will show a solution that is safer and results in a compile-time error when we use incorrect types.

Here are two additional capabilities provided by GNATCOLL.SQL.Exec, which are fully described in the documentation:

* Automatic transactions

When the SQL command modifies the database (using an INSERT or an UPDATE, for instance), GNATColl will automatically start a transaction on the DBMS so that all such changes are grouped and either all executed or all discarded. For this it provides two primitive operations on a connection: Commit and Rollback.

* Prepared and parameterized queries

As we mentioned in the first Gem, creating the query in the first place is less efficient (but safer) than using a string. The solution is to prepare the query. Preparation can occur either on the client (so GNATColl converts it to a string just once, and then reuses it every time you send the same query), or directly on the DBMS server (which will then parse the string and prepare its execution so that multiple executions are much faster). Of course, it is not often that you want to execute the exact same query, so the latter is only useful when part of the query can be substituted via parameters. Here is an example of such a query:

  declare
     Q : constant SQL_Query := SQL_Select
        (Fields => Table1.Field2 & Table2.Field3,
         From => Table1 & Table2,
         Where => Table1.Field1 = Table2.Field3
            and Table1.Field1 = Integer_Param (1));
     Prepared_Q : constant Prepared_Statement :=
        Prepare (Q, On_Server => True);
  begin
     ...
  end; 

There is a lot going on here: Q itself indicates that it will need one parameter (index 1) that is an integer; Prepare_Q is then the same as Q, but will be prepared on the server. At the point of declaration, there is no connection, so obviously the query is not prepared on the server yet. What's more, GNATColl needs to prepare it for each connection to the database, not just once. So this preparation will take place the first time the query is executed for a specific connection.

When you execute Prepared_Q you need to provide the parameter value, as in:

  R.Fetch (DB, Prepared_Q, Params => (1 => +23)); -- first execution
  R.Fetch (DB, Prepared_Q, Params => (1 => +34)); -- second execution

The second execution will be much faster than the first. As a point of comparison: when passing the SQL_Query directly and using a Direct_Cursor, it took 4.05s to execute the same query 100_000 times with SQLite; the same query prepared on the client took 2.50s; finally, the same query prepared on the server, as above, took only 0.55s. Preparing queries can thus have a very significant impact on your application's performance.

To be continued in part 3 ...