Creating a DBMS Connection

Standard Connection

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

DalDb2Driver. To work with DB2 DBMS.
DalMsSql2008Driver. To work with Microsoft SQL Server 2008 DBMS.
DalMsSql2012Driver. To work with Microsoft SQL Server DBMS 2012\2014.
DalOleDbDrivers. To work with the collection of OLEDB drivers installed in the operating system.
DalOrcl8Driver. To work with Oracle DBMS.
DalTrdtDriver. To work with Teradata DBMS.
DalPostgresDriver. To work with PostgreSQL DBMS.
DalSQLiteDriver. To work with SQLite DBMS.

TIP. For details about supported DBMS versions, see theSupported DBMS article.

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;
    //Connection descriptions
    ORCLDescriptor := ORCLDriver.CreateDescriptor;
    MSSQLDescriptor := MSSQLDriver.CreateDescriptor;

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

Var
    //...
    ORCLDescriptor, MSSQLDescriptor: IDalConnectionDescriptor;
    ORCLParams, MSSQLParams: IDalConnectionDescriptorParams;
    //...
Begin
    //...
    //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 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;
    //Connection descriptions
    ORCLDescriptor := ORCLDriver.CreateDescriptor;
    //Determine whether a connection for asynchronous execution of queries is created
    (ORCLDescriptor As IDalConnectionDescriptor2).ConnectionType := DalConnectionType.Async;

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

Var
    //...
    ORCLDriver: IDalDriver;
    ORCLDescriptor: IDalConnectionDescriptor;
    ORCLParams: IDalConnectionDescriptorParams;
    ORCLConnect: IDalConnection;
    //...
Begin
    //...
    ORCLDriver := New DalOrcl8Driver.Create;
    //Connection descriptions
    ORCLDescriptor := ORCLDriver.CreateDescriptor;
    //Determine whether a connection for asynchronous execution of queries is created
    (ORCLDescriptor As IDalConnectionDescriptor2).ConnectionType := DalConnectionType.Async;
    //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;
    //Connection for executing asynchronous queries
    ORCLAsyncConnect := (ORCLConnect As IDalConnection2).Clone(DalConnectionType.Async);
    //...

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