Using Queries in Custom Applications

Article number: KB000016

General Information

Related blocks:

Description

There are several methods of executing database queries in a custom application: via standard programming interfaces or via the Query object from the repository.

Using Interfaces of the Dal Assembly

The first version is used to execute a query using the Dal assembly interfaces. This method has a number of drawbacks if complex queries are used:

Editing a query becomes more complex.
It is more difficult to read the code of the custom application.

Consider an example of parametric query execution, which adds a record to any database table. Fields values are passed by the function input parameters. Executing the example requires the Database object with the OBJ_DB identifier. Working result of a function is a number of added records.

Fore

Fore.NET

Example of extracting values. A function work result is a cursor with an output data set.

Fore

Fore.NET

Using the Query Repository Object

The second option assumes the use of the Query repository object with the required parameter. The application must include a function that allows for getting results of this query. Advantages of this method:

The query is stored in a known location, it can be easily edited.
The amount of application code is decreased and the debugging efficiency is increased.

The example of getting data by executing the Query object. Identifier in the repository query is passed in the QueryId function parameter and the ParamId query parameter identifier is passed in the ParamValue parameter value. The result of function operation is an opened instance of an object providing an access to the cached data.

Fore

Fore.NET

In the application the code looks as follows:

Var
Tasks: IDatasetInstance;
Begin
Tasks := RunQueryWithParam(< Query identifier >, "ID", < Parameter bvlue >);

To get access to data, use the IDatasetInstance.FieldsIDatasetInstance.Fields property or address the data source cache using the IDatasetInstance.OpenCached method:

Var
    Tasks: IDatasetInstance;
    TasksData: ICachedDataset;
Begin
    Tasks := RunQueryWithParam(< Query identifier >, "ID", < Parameter value >);
    TasksData := Tasks.OpenCached;

To pass the multiple value to the parameter, use the following structure:

Var
    Tasks: IDatasetInstance;
    Ls: ArrayList;
Begin
    Ls := New ArrayList.Create;
    For i := 1 To 2 Do
        Ls.Add(i);
    End For;
    Tasks := RunQueryWithParam(< Query identifier >, "ID", Ls.ToArray);

Constraints

The DBMS servers configuration constraints the number of simultaneously opened cursors that is why it is required to close not used cursors using the Close method.

If Microsoft SQL Server 2008 drivers are supported, the MARS (Multiple Active Result Set) mode is used. Using this mode enables the user to have more than one output dataset opened within a single connection. Somehow, it is not available to start a new transaction within this connection until data of one output dataset is not read up to the end. There are two ways to solve this problem:

Use different connections. To do this, create different Database objects in the repository or create a connection directly in the application code.
Read the output dataset to the end. Working with dataset which describes the IDatasetInstance interface, to read all records, open the cache and get the property of the following value: ICachedDataset.RecordCountAll. Addressing this property results in reading of all records.

Consider as well recommendations for development of applications functioning in the MARS mode. These recommendations are represented in the MSDN.