Enterprise Forms 4.1 introduces a new feature called External Database Connections (EDC), which essentially provides the ability to connect to external databases other than the default portal database. Not only it is possible to connect to other Microsoft SQL databases, EDC also support other database servers such as Oracle, MySQL, VistaDB, PostgresSQL and of course Microsoft Access.
This article discusses the process of enabling and configuring External Database Connections in Enterprise Forms. To setup External Database Connection you will need to
- Activate External Database Connection feature for the portal
- Add database connections and associate with form templates
Activate External Database Connection feature
To use EDC the feature needs to be enabled via a configuration settings which only a Host or SuperUser can have access to. The EDC feature is a per portal setting which needs to be enabled or disabled for each portal Enterprise Forms is being used. By default EDC is disabled and is required to be enabled by a Host user.
To enable EDC for the current portal
- Log in as Host or SuperUser
- Go to a portal page where an instance of the Form Template Manager module resides
- From the module's menu select "Advanced Portal Features" menu item. Advanced Portal Features menu item is only visible/accessible in Page Edit mode
- In Advanced Portal Features view, expand the Database Connections and Queries section and check the External Database Connection property.
- Click on Update to enable changes and enable External Database Connection for the current portal
NOTE: By default external database connection only support access to data via Direct Table or Stored Procedure. If acces to Raw SQL is required, you will need to individually enable Direct SQL Support for each of the external database. These Direct SQL Support options are only visible if the EDC feature is enabled.
Add/Edit Database Connections
Once External Data Connection has been enabled, the next step is to create new external database connections and assign to a form template. A database connection can be assigned to a single form template or all form templates in the system. Similar to enabling External Database Connections feature, editing database connections can only manage by Host or a SuperUser.
To Create an External Database Connection
- Login as Host or a SuperUser
- Goto a portal page containing an instance of the Form Template Manager module
- Select Database Connections from the module's menu. Note the menu item is only visible when page is in Edit module and External Database Connections has been enabled
- To start creating a database connection, in the Template Access drop down list, select a Form Template or (All Form Template) to assign or grant access to the database connection
- In the Database Server drop down list, select the type of the Database Server of the target database
- Provide a Name for the Connection Name.
- Provide a Connection String for the connection. An example Connection String is automatically generated when a Database Server is selected. Simply copy the sample connection string to the Connection String property and replace the sections within the curly brackets {} with parameters relavent to your environment. For additional information on database connection strings, click on the Click here link to obtain more information on database connection strings.
- Click Update to create the database connection. Upon creating the database connection, the system validates the connection string by connecting to database server. If the connection fails the system will return an error message and the connection will not be created. Thus make sure the database server is online and active before creating the connection.
NOTE: Once a database connection is created, the assigned form template will be able to use the database connection to create Queries Tokens
Continue to Part 2, Manage Database Query