TransForm E-Forms Manager has the built-in ability to connect over ODBC or JDBC to SQL-compliant databases outside of the TransForm E-Forms Manager system. This allows you to provide highly customized and configurable integration with your organization's existing data repositories for purposes of form processing, automation, and authentication.
There are several TransForm E-Forms Manager features that involve integration with ODBC/JDBC and SQL-compliant databases, allowing you to take advantage of existing data repositories in order to populate forms with data, write form data to a database, or configure form user authentication with user information stored in the database.
Specific features that may involve database integration include:
Form Autofill: Automatic population of form fields with data from a database.
Autowrite: Automatic writing of form data to database tables upon form submission or based on workflow rules.
Database Authentication: One of the options when defining an authentication provider in TransForm E-Forms Manager that uses a database connection and query to authenticate form users.
One of the crucial steps in the configuration of each of these features is the selection of an ODBC or JDBC data source that TransForm E-Forms Manager will use to connect to a database in order to perform its functions. Once you have one or more data sources defined, you may proceed to the configuration of objects related to Autofill, Autowrite, and Database Authentication. Data sources are defined in the Database Integration area of TransForm E-Forms Manager.
Also configurable under the Database Integration interface are Autofill and Autowrite statements - fundamental components of these data integration features that may be shared across multiple form definitions, and which define the basic means through which TransForm E-Forms Manager retrieves information from, and writes information to, a database.
Select DB Integration from the Administration tab. This opens a screen containing two or three other tabs, labeled Autofill, Autowrite, and Data Sources. (The Autofill tab will not be present if you do not have the Autofill feature enabled.) The Autofill and Autowrite tabs allow you to view and edit the SQL statements used in these features. The Data Sources tab contains the ODBC DSN references or JDBC connection configurations that you need to configure as the first step in configuring data integration for each of these features.
In order to provide database-dependent integration with forms in the TransForm E-Forms Manager catalog, the system can be configured to connect to external databases. To connect to a database, TransForm E-Forms Manager relies on pre-configured connection settings based on ODBC or JDBC standards, and using appropriate drivers that you are responsible for installing on the system. The creation of an ODBC data source involves the use of an ODBC DSN that you define outside of the TransForm E-Forms Manager environment. This allows you to make use of existing ODBC connection settings, and simplifies the TransForm E-Forms Manager configuration process by requiring you to only provide basic information about your ODBC connections within TransForm E-Forms Manager. Creating a JDBC data source involves placing the JDBC driver file(s) in the correct location on the system then specifying connection parameters from the TransForm E-Forms Manager interface. Detailed instructions for creation of both types of Data Source are as follows.
An ODBC Data Source is simply a reference to an existing ODBC DSN (Data Source Name) that has already been created in your Windows environment. This reference provides TransForm E-Forms Manager with a Windows DSN that holds the actual connection settings, and may also provide database authentication credentials if they are not automatically supplied by your ODBC DSN.
If you already have a database connection defined as an ODBC DSN in your Windows environment, and you want to use it for e-form data integration, you only need to know the exact name of the DSN (and possibly a database user ID and password). If you do not already have an ODBC DSN defined for your database connection, you must first create it. Then you may follow the steps below to configure a TransForm E-Forms Manager Data Source to establish the connections.
Go to your Windows Control Panel, and choose Administrative Tools -> Data Sources (ODBC).
In the ODBC Data Source Administrator window, select the System DSN tab. This is where your ODBC DSN should be listed.
In the list of DSNs, find the one you want to use with TransForm E-Forms Manager. The value in the Name column is the value for the ODBC DSN that you use for your TransForm E-Forms Manager Data Source configuration.
For more information on ODBC settings, refer to your Windows ODBC Help.
Navigate to the Database Integration area of TransForm E-Forms Manager. On this screen, choose the Data Sources tab, and click the New Data Source link.
On the Create Data Source screen, enter a name for your TransForm E-Forms Manager Data Source in the Data Source Name field. This need not match your ODBC DSN name. The name you assign will be used in the Form Catalog and other areas of the TransForm E-Forms Manager administrative interface to represent this database connection.
In the Data Source Type field, choose ODBC. This will allow you to reference an externally-configured ODBC DSN that defines the database connection settings.
Enter the exact name of the ODBC data source (found in part 3 of step 1 above) into the ODBC DSN field.
Some ODBC configurations do not save the user ID and/or password needed to connect to the database. If your ODBC DSN settings do not automatically provide the user ID and password credentials for the database connection, you may enter the user ID and password into the User and Password fields in the Data Source Authentication area.
Click the Save Changes button. This will take you back to the Data Sources screen, where you will find a data source with the name you chose.
Click on the data source name to get to the Data Source Details screen for this data source, and on this screen, click the Test Connection button to ensure that your TransForm E-Forms Manager instance can connect to the database.
Starting in version 7.3, TransForm E-Forms Manager supports the use of JDBC-type drivers for connectivity to external databases. The creation of a JDBC Data Source for TransForm E-Forms Manager involves the use of a JDBC Driver file or files that you obtain from a database vendor or elsewhere, along with a set of configuration parameters in TransForm E-Forms Manager that tells the system how to use those drivers in order to connect to an external database. The steps that follow describe how to set up a JDBC driver for use in providing database connectivity through a TransForm E-Forms Manager Data Source.
First, you must obtain the appropriate JDBC driver from your database vendor. Each driver comes with its own set of instructions for installation and configuration. Pure Java drivers usually consist of a single file that can be deployed using the instructions below. Others are based on native interfaces that must be installed as well.
Note: If your TransForm E-Forms Manager system is already configured to use an Oracle or SQL Server database as its data repository, and you also want to create a Data Source for one of these types of database, then the required files and resources are already available to TransForm E-Forms Manager, so you may skip the steps in this section.
Obtain the JDBC driver appropriate to the database with which you want to connect, and follow any manufacturer's instructions for installing the driver. If the driver consists of a single file ending in a .jar extension, it is likely a pure-Java driver that requires no prior installation steps, and you should be able to skip directly to the next step. (For example, Oracle Corporation provides a pure-Java JDBC driver for Oracle databases, consisting of a single file called ojdbc5.jar). If the driver does require separate installation, it should include a .jar file that results from the extension which you will make available to TransForm E-Forms Manager using the steps below.
Copy the JDBC driver .jar file into the /FSBServer/shared/lib directory relative to your TransForm E-Forms Manager installation location. (For example, if your TransForm E-Forms Manager software is installed in C:\Program Files\TransForm\EFM, you would place the driver's .jar file to the C:\Program Files\TransForm\EFM\FSBServer\shared\lib directory.
Restart your TransForm E-Forms Manager server service.
Navigate to the Database Integration area of TransForm E-Forms Manager. On this screen, choose the Data Sources tab, and click the New Data Source link.
On the Create Data Source screen, enter a name for your TransForm E-Forms Manager Data Source in the Data Source Name field. The name you assign will be used in the Form Catalog and other areas of the TransForm E-Forms Manager administrative interface to represent this database connection.
In the Data Source Type field, choose JDBC.
In the JDBC Driver field, enter the Java Class Name of the JDBC driver class. Refer to the driver vendor's documentation if you do not know this value.
In the JDBC Connection URL field, enter the full connection string that your driver will need to establish a connection. The contents and structure of this connection string will vary by driver, so if you are unsure of what should be in the connection string refer to the driver vendor's documentation.
Enter the user ID and password for the database connection into the User and Password fields in the Data Source Authentication area. Note that depending on whether user and password information are provided in the connection URL above, these values may or may not be necessary here.
Click the Save Changes button. This will take you back to the Data Sources screen, where you will find a data source with the name you specified.
Click on the data source name to get to the Data Source Details screen for this data source, and on this screen, click the Test Connection button to ensure that your TransForm E-Forms Manager instance can connect to the database.
Completing Data Source Configuration returns you to the Data Sources sub-tab on the DB Integration screen. You now see your data sources in a list by name. Each listing also specifies the data source type and an indication of whether or not it is in use or editable:
Data Source Name: Provides a unique name for the data source, which can be referenced when setting up a form for Autofill. Clicking here will provide a summary of the full details of the data source.
Data Source Type: Lists the type of data source used to connect to your database.
Edit: If a Data Source is not already associated with an active Autofill Handler, Autowrite Handler, or Authentication Provider, this column will contain an Edit icon that you may click to modify the data source configuration.
If the Data Source is already associated with an active Autofill Handler, Autowrite Handler, or Authentication Provider, you will see an "In Use" indicator instead of an Edit icon. If you want to edit the configuration, you may click the In Use link, which will open a window that allows you to view and/or disable any objects that are using the Data Source. If you disable the objects shown in this window, you will then be able to edit the Data Source configuration.
Note: If you disable Autofill or Autowrite Handlers from the "In Use" popup window, you will want to re-enable each Handler in the form catalog.
Delete: If a Data Source is not already associated with an active configuration, this column will contain a Delete icon that you may click to delete the data source configuration.
If the Data Source is already associated with an active handler or provider, you will see an "In Use" indicator instead of a Delete icon. Clicking the In Use link will open a window that shows you the list of objects that are using the Data Source. Before you can delete the Data Source, you will have to first edit each object in this list, changing each to reference a different data source name.