Using Queries in Custom Applications

Article number: KB000016

General Information

Related blocks:

Fore
The repository objects Query and Procedure

Description

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

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

Function ChangeData(Value1: Variant; Value2: Variant): Integer;
Var
    Mb: IMetabase;
    DbIns: IDatabaseInstance;
    Command: IDalCommand;
    CommandParams: IDalCommandParams;
    SQL: String = "";
    Result: Integer;
Begin
    Mb := MetabaseClass.Active;
    DbIns := Mb.ItemById("OBJ_DB").Open(Null) As IDatabaseInstance;
    SQL := "Insert into ... values (:Val1, :Val2)";
    Command := DbIns.Connection.CreateCommand(SQL);
    Command.Parse;
    CommandParams := Command.Params;
    CommandParams.Item(0).Value := Value1;
    CommandParams.Item(1).Value := Value2;
    Result := Command.Execute;
    Return Result;
End Function ChangeData;

Function ChangeData(MB: IMetabase; Value1: Object; Value2: Object): Integer;
Var
    DbIns: IDatabaseInstance;
    Command: IDalCommand;
    CommandParams: IDalCommandParams;
    SQL: String = "";
    Result: Integer;
Begin
    DbIns := Mb.ItemById["OBJ_DB"].Open(Null) As IDatabaseInstance;
    SQL := "Insert into ... values (:Val1, :Val2)";
    Command := DbIns.Connection.CreateCommand(SQL);
    Command.Parse();
    CommandParams := Command.Params;
    CommandParams.Item[0].Value := Value1;
    CommandParams.Item[1].Value := Value2;
    Result := Command.Execute();
    Return Result;
End Function;

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

Fore Fore.NET

Function ReadData: IDalCursor;
Var
    Mb: IMetabase;
    DbIns: IDatabaseInstance;
    Com: IDalCommand;
    SQL: String;
    Result: IDalCursor;
Begin
    Mb := MetabaseClass.Active;
    DbIns := Mb.ItemById("OBJ_DB").Open(Null) As IDatabaseInstance;
    SQL := "Select ...";
    Com := DbIns.Connection.CreateCommand(SQL);
    Result := Com.CreateCursor;
    Return Result;
End Function ReadData;

Function ReadData(MB: IMetabase): IDalCursor;
Var
    DbIns: IDatabaseInstance;
    Com: IDalCommand;
    SQL: String;
    Result: IDalCursor;
Begin
    DbIns := Mb.ItemById["OBJ_DB"].Open(Null) As IDatabaseInstance;
    SQL := "Select ...";
    Com := DbIns.Connection.CreateCommand(SQL);
    Result := Com.CreateCursor();
    Return Result;
End Function;

Using Application Object

The second option assumes the use of the repository objects Query or Procedure with necessary parameter sets. The application must include a function that allows for getting results of this query. Advantages of this method:

The repository object is stored in a known location and 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

Public Function RunQueryWithParam(QueryId: String; ParamId: String; ParamValue: Variant): IDatasetInstance;
Var
    Mb: IMetabase;
    QueryKey: Integer;
    Desc: IMetabaseObjectDescriptor;
    Pars: IMetabaseObjectParamValues;
Begin
    Mb := MetabaseClass.Active;
    QueryKey := Mb.GetObjectKeyById(QueryId);
    If QueryKey = -1 Then
        Return Null;
    End If;
    Desc := Mb.Item(QueryKey);
    Pars := Desc.Params.CreateEmptyValues;
    Pars.FindById(ParamId).Value := ParamValue;
    Return Desc.Open(Pars) As IDatasetInstance;
End Function RunQueryWithParam;

Public Function RunQueryWithParam(MB: IMetabase; QueryId: String; ParamId: String; ParamValue: Object): IDatasetInstance;
Var
    QueryKey: UInteger;
    Desc: IMetabaseObjectDescriptor;
    Pars: IMetabaseObjectParamValues;
Begin
    QueryKey := Mb.GetObjectKeyById(QueryId);
    If QueryKey = -1 Then
        Return Null;
    End If;
    Desc := Mb.Item[QueryKey];
    Pars := Desc.Params.CreateEmptyValues();
    Pars.FindById(ParamId).Value := ParamValue;
    Return Desc.Open(Pars) As IDatasetInstance;
End Function;

In the application the code looks as follows:

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

To get access to data, use the IDatasetInstance.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 various 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.

When creating queries, take into account the syntax that is used on working with a specific DBMS. The repository objects Query and Procedure enable the user to specify syntax for several DBMS at the same time, which can be used on moving objects between repositories deployed in different DBMS.

The important are parameters of the Query and Procedure objects, which are used in SQL queries to return values. Such parameters must be explicitly cast to returned value type. It is related to the ability to transform data of different DBMS and also to the features of work of platform drivers. The example of query with parameter type transformation:

select cast(:param as <type>) as param