Creating a Connection with DBMS

Standard Connection

Before creating a connection, first initialize the driver and prepare the connection description that specifies the connection parameters. To do this, depending on which DBMS is used on the server, create an object of one of the following classes:

DalDb2Driver. To work with the DB2 DBMS.
DalMsSql2008Driver. To work with the Microsoft SQL Server 2008 DBMS.
DalMsSql2012Driver. To work with the DBMS Microsoft SQL Server 2012\2014.
DalOleDbDrivers. To work with collection of OLEDB drivers installed in the operating system.
DalOrcl8Driver. To work with the Oracle 9.x\10.x\11.x DBMS.
DalTrdtDriver. To work with the DBMS Teradata 13 or later.
DalPostgresDriver. To work with the PostgreSQL version 9.1 or later.
DalSQLiteDriver. To work with the SQLite 3.8.1 DBMS.

Execute the IDalDriver.CreateDescriptor method for the obtained object:

Var
    //...
    ORCLDriver, MSSQLDriver: IDalDriver;
    ORCLDescriptor, MSSQLDescriptor: IDalConnectionDescriptor;
    //...
Begin
    //...
    ORCLDriver := New DalOrcl8Driver.Create;
    MSSQLDriver := New DalMsSql2008Driver.Create;
    //The connection descriptions
    ORCLDescriptor := ORCLDriver.CreateDescriptor;
    MSSQLDescriptor := MSSQLDriver.CreateDescriptor;

Specify the connection parameters in the obtained description in the IDalConnectionDescriptor.Params property. The list of parameters is created by Prognoz Platform 9 kernel individually for each driver, parameter names may differ:

Var
    //...
    ORCLDescriptor, MSSQLDescriptor: IDalConnectionDescriptor;
    ORCLParams, MSSQLParams: IDalConnectionDescriptorParams;
    //...
Begin
    //...
    //The connection parameters
    ORCLParams := ORCLDescriptor.Params;
    ORCLParams.Find("User Name").Value := "User";
    ORCLParams.Find("Password").Value := "Password";
    ORCLParams.Find("Host BSTR").Value := "ORCL_Server";
    ORCLParams.Find("Schema").Value := "PPRepository";
    MSSQLParams := MSSQLDescriptor.Params;
    MSSQLParams.Find("User Name").Value := "User";
    MSSQLParams.Find("Password").Value := "Password";
    MSSQLParams.Find("Host BSTR").Value := "MSSQL_Server";
    MSSQLParams.Find("Database").Value := "PPRepository";

A new connection is created after setting the parameters on calling the IDalConnectionDescriptor.CreateConnection method:

Var
    //...
    ORCLDescriptor, MSSQLDescriptor: IDalConnectionDescriptor;
    ORCLConnect, MSSQLConnect: IDalConnection;
    //...
Begin
    //...
    //Create a connection
    ORCLConnect := ORCLDescriptor.CreateConnection;
    MSSQLConnect := MSSQLDescriptor.CreateConnection;

The created connection can be used to get information on the DBMS objects, to control server transactions and to create a command for executing SQL queries to the server.

Connection for Executing Asynchronous Queries

When using the DBMS based on Oracle 9.x or later or MSSQL 2008, a specialized connection can be created within which asynchronous queries can be executed. Asynchronous execution enables the user to continue working in the application without waiting for result of the SQL query execution. To create such a connection, cast the connection description to the IDalConnectionDescriptor2 type and specify the DalConnectionType.Async in the IDalConnectionDescriptor2.ConnectionType property:

Var
    //...
    ORCLDriver: IDalDriver;
    ORCLDescriptor: IDalConnectionDescriptor;
    //...
Begin
    //...
    ORCLDriver := New DalOrcl8Driver.Create;
    //The connection descriptions
    ORCLDescriptor := ORCLDriver.CreateDescriptor;
    //Set an indicator for creating a connection for the asynchronous execution of queries
    (ORCLDescriptor As IDalConnectionDescriptor2).ConnectionType := DalConnectionType.Async;

A new connection is created after setting the connection parameters and executing the IDalConnectionDescriptor.CreateConnection method:

Var
    //...
    ORCLDriver: IDalDriver;
    ORCLDescriptor: IDalConnectionDescriptor;
    ORCLParams: IDalConnectionDescriptorParams;
    ORCLConnect: IDalConnection;
    //...
Begin
    //...
    ORCLDriver := New DalOrcl8Driver.Create;
    //The connection descriptions
    ORCLDescriptor := ORCLDriver.CreateDescriptor;
    //Set an indicator for creating a connection for the asynchronous execution of queries
    (ORCLDescriptor As IDalConnectionDescriptor2).ConnectionType := DalConnectionType.Async;
    //The connection parameters
    ORCLParams := ORCLDescriptor.Params;
    ORCLParams.Find("User Name").Value := "User";
    ORCLParams.Find("Password").Value := "Password";
    ORCLParams.Find("Host BSTR").Value := "ORCL_Server";
    ORCLParams.Find("Schema").Value := "PPRepository";
    //Create a connection
    ORCLConnect := ORCLDescriptor.CreateConnection;

If a standard connection is already created with the server, the connection can be modified using the IDalConnection2.Clone method:

Var
    //...
    ORCLDescriptor: IDalConnectionDescriptor;
    ORCLConnect, ORCLAsyncConnect: IDalConnection;
    //...
Begin
    //...
    //Create a connection
    ORCLConnect := ORCLDescriptor.CreateConnection;
    //The connection for executing  asynchronous queries
    ORCLAsyncConnect := (ORCLConnect As IDalConnection2).Clone(DalConnectionType.Async);
    //...

Further work with the created connection is described in the following sections: