Change Variable Data Sources

Overview

The data source is the definition of the database connection which includes:

• IP address
• DB type
• DB driver name
• User and password
• Special considerations, such as a library list

Multiple datasources can be defined in the global Datasource Configuration file found from the Admin Menu -> Datasources -> Runtime Datasources (/mrcjava/WEB-INF/classes/mrc-spring-context.xml).

In each dictionary configuration file found in the Admin Menu -> Dictionary Configuration -> Runtime Application Settings (/mrcjava/WEB-INF/classes/DD/mrc_servlet_config.xml), you can assign a single datasource for all applications within that dictionary with the Datasource Connector.

You can override that default datasource connector at the program level by specifying another datasource in the application properties (/mrcjava/WEB-INF/classes/DD/IXXXXXp.xml).

Sometimes, though, as the application designer, you might prefer to set the datasource conditionally. An example of that would be a case where you would like to allow the end user to select a company to work in. In these cases, it is possible to present the user with several companies that they may work in. Behind the scenes, the user’s company selection will set the datasource for the current browser session. The differences in the datasource might be minor, different only because of a library list or database definition, or major, with different IP and DB types. Subsequent applications, then, will use the selected datasource instead of a pre-defined one for the current browser session.

In the example above, you can see that despite the thousands of records, the user ALEXANDER can only see the 3 companies (or data connections) to which he is assigned. Clicking on the Datasource name will set the specific database for the user’s session.

Configuration

Enable Variable Datasource

Add the variable datasource servlet entry to the web.xml.

Edit the /mrcjava/WEB-INF/web.xml file in a text editor and add the following entry at the end of the file before the </web-app> tag:

<servlet> 
<servlet-name>SetDataInSession</servlet-name>  
<servlet-class>SetDataInSession</servlet-class> 
</servlet> 
<servlet-mapping>  
<servlet-name>SetDataInSession</servlet-name>  
<url-pattern>/servlet/SetDataInSession</url-pattern>  
</servlet-mapping>

Once done, make sure to save the file and restart Tomcat.

As the web.xml is a global file, this step only needs to be done the very first time the Variable Datasources is being configured, and does not need to repeated per dictionary. Before adding to the web.xml, make sure this entry does not already exist by doing a search for ‘SetDataInSession’.

Define Datasources

From the Admin Menu -> Datasources -> Runtime Datasources, find and copy the existing datasource you wish to work with. Define the ID parameter as a value that can be derived in the Datasource Selection screen. Use “ms_sqlserver_comp1” and “ms_sqlserver_comp2” for example, if choosing between company 1 and 2.

The DB prefix such as “ms_sqlserver”,”mysql”, “as400_remote” is required. The suffix will make it unique to the desired datasource.

Configure Dictionary

Copy the file /mrcjava/mrcclasses/mrcdatasource.xml into the /mrcjava/WEB-INF/classes/DD directory.

Configure the entry_url with the DataSource Selection screen that you will generate in the next step, such as DD.I00010s.

Define any applications that should be excluded from the variable datasource logic, instead of using the default datasource connection. Use the applications tag for that purpose, with an app entry for each application to exclude. If you create the DataSource Selection screen in that same Data Dictionary, be sure to exclude that application, or you will cause an infinite loop of redirection.

Datasource Selection Screen

Create a Multiple Row Data List retrieval over a file that selects records by user employing the row level security logic or it might be a list of the same options available to all users.

Paint the application to include a link on every row like the following to set the datasource:

SetDataInSession?data_src=ms_sqlserver_comp1&redirect_url=DD.mrcMenu

The syntax, including the href tag and the text, might look like this:<a href="SetDataInSession?data_src=ms_sqlserver_${row.DATASOURCE?url}&redirect_url=DD.mrcMenu">Company 10</a>

DATASOURCE will be substituted for the field name of the column that stores your datasource suffix.

End User Screen Flow

1. mrcSignon (end user authentication, optional)
2. Dataource Selection screen (DD.I00010s, for example)
3. DD.mrcMenu (application or menu of applications)

Notes

If the datasource definitions will only be different due to the library list setup, then it will be necessary to configure the applications to avoid hard-coding the data library name in the SQL statement. That is done by setting the tablename_fmt parameter to “1” (Table name only) in the Data Dictionary properties file: mrc_servlet_config.xml. This will instruct the applications to use only the table name in the SQL, not the data library name which was used at development time, allowing the library list to determine which data library to use. If you have any individual applications that should use their data library name in the SQL statement, you can add that parameter at the program level and set it appropriately there.