Managed Connections

Lesson 5 - Multiple Connections

Up until now, we’ve only used a single managed connection in our examples, but you can define multiple connections in the same configuration file. There are two reasons for doing so and we’ll refer to these as alternate connections and additional connections.

Alternate connections use the same connection ID, but may specify a different server, or database, or user credentials, or any combination of those parts of the connection string. By keeping the connection ID the same, the application never needs to be recompiled – Changing the information that makes up the full connection string paired with that connection ID will change the ultimate path to the data.

Additional connections are used when the application needs to access tables from different databases or servers simultaneously.

There is no practical limit to the number of additional or alternative connections supported in a single workspace.

Let’s take a look at how multiple connections are expressed in the configuration file and then work through some examples in our Cookbook workspace.

In this example we see there are two connections defined, but both use the same “cookbook” ID. Both use the same server, but different databases and credentials. 

Notice that the second connection also contains disabled=yes. This is to allow the connection class to automatically pick the proper connection because only one connection can be enabled at once.

In this example we see there are two connections defined, this time with one using “cookbook” and the other using “utensils”. Both use the same server, but different databases and credentials.

Since the connections do not use the same ID, both can be active at the same time. There is no need to disable one of them.

Next, we’ll experiment with multiple connections…

DEMONSTRATION

• We left our cookbook workspace with the single cookbook ID using the cookbook database and cookbook SQL login.
   

• Remember that earlier in lesson 3 we changed this connection from windows authentication to SQL authentication. This allowed us to easily experiment with two different access methods to the same data. Now we’ll use alternate connections to have both methods defined at the same time. We’ll use the Connection Manager to create the second connection.

• We use the same “cookbook” ID, server and database as the existing connection, but this time use Windows Authentication.
  

• Remember that any time we use the Connection Manager to create or edit a connection, we can easily test the connection before saving.

• If we press OK to save, the Connection Manager warns us that we cannot have two connections with the same ID active at the same time, so we’ll disable this one before exiting.
  

• The configuration file now contains two connections; both using the same ID, and the same server and database, but different credentials. We can decide which access we want to use at any time.
  

• If we remove the trusted connection setting in connection 2 and run the application, the login dialog is automatically displayed.

• If we then switch back to connection 1 and remove the PWD setting, the login dialog is also displayed, but this time with the cookbook UID displayed. Remember that it’s the use of the “Remember…” checkbox on the login dialog that determines if the appropriate user credentials are saved back to the configuration file.

• So far, both of our connections have used the same server and cookbook database. Next, we’ll look at how to set up another connection using a different, but matching database.

• In Microsoft SQL Management Studio we’ll use the same script that created the original cookbook database to set up a duplicate version named customer1.
  
  

• It is important that the databases contain the same table structures when using them with managed connections.

• Once complete, we can return to the cookbook workspace and set up a new connection to the customer1 database. We use Windows Authentication to check the new connection – be sure to enable it as the default connection before proceeding.
• The configuration file now contains three connections, all using the same “cookbook” ID and connection 3 accessing a different database.
  
• We can run the application (again, without recompiling) and edit the data to show it is from a different customer, not our development data. We can see that change reflected in SQL Management Studio.
  
• Next, we’ll also use SQL Management Studio to give our cookbook user rights to the customer1 database so we can use SQL Authentication to access it as well.
  
• You’ll notice that we use the Connection Manager to create and edit our additional connections – this makes it easy to test even before running the application and ensures that the entries are accurate. 
• We could have also edited the configuration file directly (or programmatically outside the Studio).
• Remember that if the database has the same table structure (including indexes), we can change the server, database and credentials at will. As long as the SQL driver and connection ID remain the same, no changes are needed at the application level.

In many cases, perhaps even most, all the data for an application is in a single database. But there are times when your application needs to access tables in secondary databases. Databases published by different providers is one type of scenario. Another common scenario is when the transactional data for multiple customers needs isolation (separate databases) but there is data shared between customers that is inefficient or inconvenient to duplicate.

Managed connections fully support these types of use by simply creating one or more connection IDs that point to the additional data. Once the connection is made, the tables in the secondary databases are fully accessible to your application and simply need to be added to the workspace filelist.

Let’s take a look at how easy it is to set up additional connections…

DEMONSTRATION

• Suppose we want to expand our cookbook application to access information about the cookware and accessories necessary for the recipes. We already have a database with this information, named Utensils. We can easily use an additional managed connection to that database and start using those tables in our application.
• In SQL Management Studio we see the Utensils database and the cookware and location tables it contains.
  
• Our cookbook workspace only has access to the tables in the main database (the original cookbook database or our alternate customer1 database). We’ll use the connection manager to set up an additional connection to the utensils database, using Windows authentication. 
• Notice that since this new connection is not using the same ID, both the cookbook and utensils connections can be enabled at the same time.
  
• Now that we have the connection established, we can add the tables in the utensils database to our workspace using the connect / repair wizard.
• We use managed connections.
• Select the utensils connection.
• Select “Connect New” because these tables have not been used in any DataFlex application before, so we don’t have intermediate files for them.
• Select the tables we want to access (cookware and location).
• Click next a couple of times and Finish to complete the table connections.
• We now have the cookware and location tables fully accessible in our workspace.
  
• You can see that they have different connection information.
• We can now use the data in those tables just as if they were in the primary database. 
• Once the application is complete, we can apply all the of managed connection capabilities to both the primary and secondary databases (so, different servers, databases and user credentials) – all without the need to recompile the application.
• In the next lesson, we’ll quickly touch on dynamic connections.