In this article:

General Information

Description

Using Interfaces of the Dal Assembly

Using Application Object

Constraints

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 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:

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. The function results in the number of added records:

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(NullAs 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;
    Command.Close;
    Return Result;
End Function ChangeData;

Example of extracting values. The function results in the cursor with an output data set:

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

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 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 function results in the opened instance of an object providing an access to the cached data:

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;

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:

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

See also:

Developers Knowledge Base | The Dal Assembly | The Query Object