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:
Creating a Connection ID
Accessing Tables Using 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:
Using a driver configuration file requires no programming code changes or recompilation. However, it is less secure because the login information used is stored in a plain text driver configuration file.
Creating a connection ID programmatically does require code changes and recompilation, but typically just once. It is more secure because the login information is compiled and not visible in plain text files.
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.
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.
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.
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
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
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.