DataFlex Connection IDs

Note

As of DataFlex 19.0, Connection IDs are handled via Managed Connections, making this page obsolete. All of these techniques still work, but they have been replaced with better techniques that are also managed via the Studio and other DataFlex tools. See the Managed Connections section for more.

 

The DataFlex Connection ID provides a way to give a connection string a logical identification. When specifying the connection, the logical identification is used rather than the actual connection string. This allows a setup where a program using the same set of tables in multiple databases only has one set of intermediate (INT) files.

In previous versions, if a program needed to access the same set of tables in multiple databases, for every database a set of intermediate files had to be created. This was a strain on maintenance efforts for such environments.

There are 2 steps involved in Using Connection IDs:

  1. Creating a Connection ID

  2. Accessing Tables Using a Connection ID

 

Step 1: Creating a Connection ID

Connection IDs can be created two different ways, by using a driver configuration file or programmatically. Both versions have pros and cons:

 

Creating Connection IDs Using a Driver Configuration File

You can use the DFConnectionID keyword in the driver configuration file to set a default connection id for the driver. That way all applications will use that same connection id. The DFConnectionID keyword must be set to three values separated by two commas (,) using this format:

DFConnectionID ID, ConnectString, Options

 

If, for example, we wanted to set up an ID called MyID to point to the Order database on our local server not using silent login, we would add the following line to the driver configuration file:

DFConnectionID MyID, SERVER=(local);DATABASE=Order, 0

If, we wanted to set up an ID called MyTestID to point to the SQL Server Northwind database on our local server not using silent login and Windows Authentication, we would add the following line to the MS SQL driver configuration file (MSSQLDRV.int):

DFConnectionID MyTestID, SERVER=(local);Trusted_Connection=yes;DATABASE=Northwind, 0

Note that any keyword specified in the configuration file will be set as the driver is loaded. After the driver is loaded, you may change the connection id setting by deleting that connection id and then re-creating it with the new settings in your program.

 

Creating Connection IDs Programmatically

A function in the cCLIHandler class allows the creation a DataFlex connection ID. Multiple connection IDs can be created. The function is called CreateConnectionID. See the cCLIHandler class reference for more information. To create a connection ID to the Order database on the local SQL Server using the DataFlex SQL Server Driver, one could use the following code:

Procedure CreateMyID

    Integer iResult

    Handle hoCLI

 

    Get Create U_cCLIHandler to hoCLI

    If (hoCLI <> 0) Begin

        Set psDriverID Of hoCLI To "MSSQLDRV"

        Get CreateConnectionID of hoCLI "MyID" ;

            "SERVER=(local);Trusted_Connection=yes;DATABASE=Order" 1 to iResult

        If (iResult) Begin

            Send Stop_Box "Something went wrong..."

        End

        Else Begin

            Send Stop_Box "Connection id created"

        End

        Send Destroy of hoCLI

    End

End_Procedure

Be sure to call this method before opening any tables. In a typical DataFlex Windows application, this means calling it before the ClientArea object, since views are Used inside it and views contain Data Dictionary classes, which open tables. Each table's INT file should contain the same connection ID as created in this method, the login information in which can then be used to log into the database server.

 

Step 2: Accessing Tables Using a Connection ID

Using DataFlex Connection IDs allows the use of a single set of table intermediate files (e.g. Customer.INT), because after Step 1 above, all of the connection information is stored in a Connection ID, not the table INT files. The program or driver configuration file creates a DataFlex connection id, say "MyID", and the intermediate files refer to this ID using the SERVER_NAME setting:

SERVER_NAME DFCONNID=MyID

The program must make sure the DataFlex connection ID is created before the tables are opened. See Creating Connection IDs Programmatically (above) for details.

 

Additional Information

 

Examining Existing Connection IDs

The read only driver level attributes DF_DRIVER_NUMBER_CONNECTION_IDS, DF_DRIVER_CONNECTION_ID, DF_DRIVER_CONNECTION_ID_STRING and DF_DRIVER_CONNECTION_ID_OPTIONS can be used to get information on defined DataFlex Connection IDs. To show all existing connection IDs, one could use the following code:

Function DriverIndex String sDriver Returns Integer

    String sCurrentDriver

    Integer iDriver iNumDrivers

 

    Get_Attribute DF_NUMBER_DRIVERS To iNumDrivers

    For iDriver From 1 To iNumDrivers

        Get_Attribute DF_DRIVER_NAME Of iDriver To sCurrentDriver

        If (Uppercase(sDriver) = Uppercase(sCurrentDriver)) Begin

            Function_Return iDriver

        End

    Loop

    Function_Return 0

End_Function

 

Procedure ShowConnectionIDs

    Integer iDriver iNumConn iConn iConnOptions

    String sIDs ConnString

 

    Get DriverIndex "MSSQLDRV" To iDriver

    If (iDriver = 0) Begin

        Showln "Driver not loaded"

        Procedure_Return

    End

    Get_Attribute DF_DRIVER_NUMBER_CONNECTION_IDS of iDriver to iNumConn

    Showln "Number of connection ids: " iNumConn

    For iConn From 0 to (iNumConn - 1)

        Get_Attribute DF_DRIVER_CONNECTION_ID of iDriver iConn to sID

        Get_Attribute DF_DRIVER_CONNECTION_ID_STRING of iDriver iConn to sConnString

        Get_Attribute DF_DRIVER_CONNECTION_ID_OPTIONS of iDriver iConn to iConnOptions

        Showln sID ", " sConnString ", " iConnOptions

    Loop

End_Procedure

 

Deleting Connection IDs

Individual connection ID settings cannot be edited. It is, however, possible to delete any existing connection ID using the DeleteConnectionID function. See the cCLIHandler class reference for more information. To delete the connection identified by the string "MyID", one could use the following code:

Procedure DeleteMyID

    Integer iResult

    Handle hoCLI

 

    Get Create U_cCLIHandler to hoCLI

    If (hoCLI <> 0) Begin

        Set psDriverID Of hoCLI To "MSSQLDRV"

        Get DeleteConnectionID of hoCLI "MYID" -1 to iResult

        If (iResult) Begin

            Send Stop_Box "Something went wrong..."

        End

        Else Begin

            Send Stop_Box "Connection deleted"

        End

        Send Destroy of hoCLI

    End

End_Procedure

 

 

Notes

If DataFlex Connection IDs are used to manage multiple databases, changing table definitions takes on an additional complexity. Once changes have been made to the tables in one database, the tables in other database are out of sync with the intermediate files. It is the programmer's responsibility to handle the updates of all tables in all databases. One approach is to use a set of database-specific intermediate files while the changes are being made, another approach is to create a program that makes the changes and run that against all databases.

This technique can be used to simplify deploying an application. It is the programmer's responsibility to ensure that the table definitions in the different databases actually are the same. If there are tables that do not have the same definition in different databases the behavior of the DataFlex Connection ID feature is undefined. It might work, it might crash, it might do anything in between; it is not defined.