ADO_Statements

Database statements

Database Statements

The ADO.Statements package provides high level operations to construct database statements and execute them. They allow to represent SQL statements (prepared or not) and provide support to execute them and retreive their result. The SQL statements are represented by several Ada types depending on their behavior:

• The Statement type represents the base type for all the SQL statements.

• The Query_Statement type is intended to be used for database query statements and provides additional operations to retrieve results.

• The Update_Statement type targets the database update statements and it provides specific operations to update fields. The Insert_Statement extends the Update_Statement type and is intended for database insertion.

• The Delete_Statement type is intended to be used to remove elements from the database.

The database statements are created by using the database session and by providing the SQL or the named query to be used.

Query Parameters

Query parameters are represented by the Parameter type which can represent almost all database types including boolean, numbers, strings, dates and blob. Parameters are put in a list represented by the Abstract_List or List types.

A parameter is added by using either the Bind_Param or the Add_Param operation. The Bind_Param operation allows to specify either the parameter name or its position. The Add_Param operation adds the parameter at end of the list and uses the last position. In most cases, it is easier to bind a parameter with a name as follows:

Query.Bind_Param ("name", "Joe");

and the SQL can use the following construct:

SELECT * FROM user WHERE name = :name

When the Add_Param is used, the parameter is not associated with any name but it as a position index. Setting a parameter is easier:

Query.Add_Param ("Joe");

but the SQL cannot make any reference to names and must use the ? construct:

SELECT * FROM user WHERE name = ?

Parameter Expander

The parameter expander is a mechanism that allows to replace or inject values in the SQL query by looking at an operation provided by the Expander interface. Such expander is useful to replace parameters that are global to a session or to an application.

Query Statements

The database query statement is represented by the Query_Statement type. The Create_Statement operation is provided on the Session type and it gets the SQL to execute as parameter. For example:

Stmt : ADO.Statements.Query_Statement := Session.Create_Statement
          ("SELECT * FROM user WHERE name = :name");

After the creation of the query statement, the parameters for the SQL query are provided by using either the Bind_Param or Add_Param procedures as follows:

Stmt.Bind_Param ("name", name);

Once all the parameters are defined, the query statement is executed by calling the Execute procedure:

Stmt.Execute;

Several operations are provided to retrieve the result. First, the Has_Elements function will indicate whether some database rows are available in the result. It is then possible to retrieve each row and proceed to the next one by calling the Next procedure. The number of rows is also returned by the Get_Row_Count function. A simple loop to iterate over the query result looks like:

while Stmt.Has_Elements loop
   Id := Stmt.Get_Identifier (1);
   ...
   Stmt.Next;
end loop;

Named Queries

Ada Database Objects provides a small framework which helps in using complex SQL queries in an application by using named queries. The benefit of the framework are the following:

• The SQL query result are directly mapped in Ada records,

• It is easy to change or tune an SQL query without re-building the application,

• The SQL query can be easily tuned for a given database.

The database query framework uses an XML query file:

• The XML query file defines a mapping that represents the result of SQL queries,

• The XML mapping is used by Dynamo code generator to generate an Ada record,

• The XML query file also defines a set of SQL queries, each query being identified by a unique name,

• The XML query file is read by the application to obtain the SQL query associated with a query name,

• The application uses the List procedure generated by Dynamo.

XML Query File

The XML query file uses the query-mapping root element. It should define at most one class mapping and several query definitions. The class definition should come first before any query definition.

<query-mapping>
   <class>...</class>
   <query>...</query>
</query-mapping>

SQL Result Mapping

The XML query mapping is very close to the database XML table mapping. The difference is that there is no need to specify any table name nor any SQL type. The XML query mapping is used to build an Ada record that correspond to query results. Unlike the database table mapping, the Ada record will not be tagged and its definition will expose all the record members directly.

The following XML query mapping:

<query-mapping>
  <class name='Samples.Model.User_Info'>
    <property name="name" type="String">
       <comment>the user name</comment>
    </property>
    <property name="email" type="String">
       <comment>the email address</comment>
    </property>
  </class>
</query-mapping>

will generate the following Ada record and it will instantiate the Ada container Vectors generic to provide a support for vectors of the record:

package Samples.Model is
   type User_Info is record
     Name  : Unbounded_String;
     Email : Unbounded_String;
   end record;
   package User_Info_Vectors is
      new Ada.Containers.Vectors (Index_Type   => Natural,
                                  Element_Type => User_Info,
                                  "="          => "=");
   subtype User_Info_Vector is User_Info_Vectors.Vector;
end Samples.Model;

A List operation is also generated and can be used to execute an SQL query and have the result mapped in the record.

The same query mapping can be used by different queries.

After writing or updating a query mapping, it is necessary to launch the Dynamo code generator to generate the corresponding Ada model.

SQL Queries

The XML query file defines a list of SQL queries that the application can use. Each query is associated with a unique name. The application will use that name to identify the SQL query to execute. For each query, the file also describes the SQL query pattern that must be used for the query execution.

<query-mapping>
  <query name='user-list' class='Samples.Model.User_Info'>
   <sql driver='mysql'>
     SELECT u.name, u.email FROM user AS u
   </sql>
   <sql driver='sqlite'>
      ...
   </sql>
   <sql-count driver='mysql'>
      SELECT COUNT(*) FROM user AS u
   </sql-count>
  </query>
</query-mapping>

The query contains basically two SQL patterns. The sql element represents the main SQL pattern. This is the SQL that is used by the List operation. In some cases, the result set returned by the query is limited to return only a maximum number of rows. This is often use in paginated lists.

The sql-count element represents an SQL query to indicate the total number of elements if the SQL query was not limited.

The sql and sql-count XML element can have an optional driver attribute. When defined, the attribute indicates the database driver name that is specific to the query. When empty or not defined, the SQL is not specific to a database driver.

For each query, the Dynamo code generator generates a query definition instance which can be used in the Ada code to be able to use the query. Such instance is static and readonly and serves as a reference when using the query. For the above query, the Dynamo code generator generates:

package Samples.User.Model is
   Query_User_List : constant ADO.Queries.Query_Definition_Access;
private
   ...
end Samples.User.Model;

When a new query is added, the Dynamo code generator must be launched to update the generated Ada code.

Using Named Queries

In order to use a named query, it is necessary to create a query context instance and initialize it. The query context holds the information about the query definition as well as the parameters to execute the query. It provides a Set_Query and Set_Count_Query operation that allows to configure the named query to be executed. It also provides all the Bind_Param and Add_Param operations to allow giving the query parameters.

with ADO.Sessions;
with ADO.Queries;
...
  Session : ADO.Sessions.Session := Factory.Get_Session;
  Query   : ADO.Queries.Context;
  Users   : Samples.User.Model.User_Info_Vector;
  ...
     Query.Set_Query (Samples.User.Model.Query_User_List);
     Samples.User.Model.List (Users, Session, Query);

To use the sql-count part of the query, you will use the Set_Count_Query with the same query definition. You will then create a query statement from the named query context and run the query. Since the query is expected to contain exactly one row, you can use the Get_Result_Integer to get the first row and column result. For example:

Query.Set_Count_Query (Samples.User.Model.Query_User_List);
...
Stmt : ADO.Statements.Query_Statement
  := Session.Create_Statement (Query);
...
Stmt.Execute;
...
Count : Natural := Stmt.Get_Result_Integer;

You may also use the ADO.Datasets.Get_Count operation which simplifies these steps in:

Query.Set_Count_Query (Samples.User.Model.Query_User_List);
...
Count : Natural := ADO.Datasets.Get_Count (Session, Query);

---

Generated by Dynamo from ado-statements.ads