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:
Statementtype represents the base type for all the SQL statements.
Query_Statementtype is intended to be used for database query statements and provides additional operations to retrieve results.
Update_Statementtype targets the database update statements and it provides specific operations to update fields. The
Update_Statementtype and is intended for database insertion.
Delete_Statementtype 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 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:
but the SQL cannot make any reference to names and must use the ? construct:
SELECT * FROM user WHERE name = ?
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.
The database query statement is represented by the
Create_Statement operation is provided on the
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
Add_Param procedures as follows:
Stmt.Bind_Param ("name", name);
Once all the parameters are defined, the query statement is executed by calling the
Several operations are provided to retrieve the result. First, the
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
procedure. The number of rows is also returned by the
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;
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
Listprocedure 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
class definition should come first before any
<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
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;
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.
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
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.
sql-count element represents an SQL query to indicate the total number
of elements if the SQL query was not limited.
sql-count XML element can have an optional
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_Count_Query operation that allows to configure the named query to be executed.
It also provides all the
Add_Param operations to allow giving the
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
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.
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
Query.Set_Count_Query (Samples.User.Model.Query_User_List); ... Count : Natural := ADO.Datasets.Get_Count (Session, Query);