Giving Processes in Laserfiche Forms Access to an External Database

On the Data Sources page, you can create data sources that will be used for specific business processes. Once a process is associated with a data source, forms in that process can access information in the data source to automatically fill out fields or suggest values to users. Learn more.

Users with the System Administrator role can access the data sources page by clicking on their account name, selecting the Administration option and then selecting Data Sources from the left menu.

Data source security has been enhanced as follows:

  1. System Administrators have access to all data sources.
  2. Business processes that have been added to the data source via the Processes tab have access to the source regardless of the user.
  3. Users or groups that have been granted access rights via the Access Rights tab have access to that data source in any business process, whether or not the process has been added to the data source.

This allows users that been given access rights either directly or through a group to use that data source in lookup rules in any business process, without having to request that the business process be added to the data source.

Note: SQL databases must be version 2005 or later.

To create a new data source

  1. Click New data source at the top of the page.
  2. In the New Data Source dialog box, specify the Name, DBMS type, Server, Database, and authentication information for that data source.

    Note: The format for authenticating with Windows accounts must be like "domain\username".

  3. Click Test connection to ensure Laserfiche Forms can connect to the data source.
  4. When you are finished, click Save. Your new data source will be shown on the left side of the screen, under Data Sources.

To add user or group access rights to a data source

Access to a data source can be limited to specific users or groups in order to enhance security. Users with the System Administrator role can add or remove these access rights.

  1. Click the name of the data source you want to add access rights to.
  2. Select the Access Rights tab.
  3. Type the name of a user or group. When the desired item appears in the search results, select it. Multiple users or groups can be added at the same time.
  4. Once the user or group is populated in the field, click Add.

To remove access rights from a user or group

  • Click the Delete icon (X) to the right of the listed user or group item in the list, and then confirm by clicking the Remove button.

To associate a business process with a data source

  1. Click the name of the data source you want to add a process to.
  2. Click the Add/remove processes button at the top of the page.
  3. In the Add or Remove Processes dialog box, select the checkboxes next to the processes that will use this data source. Clear the checkboxes next to the processes you don't want to use this data source.

    4. Note: If you remove a process from a data source, all lookup rules in that process that use that data source will be deleted. If you are using another data source and want to retain your lookup rule logic, change the data source in the rule before removing the process here.

  4. Click Save.

To update the tables and views available from a data source

  1. Select the data source you want to update.
  2. Click the Refresh button at the top of the page.

    3. Note: If, after refreshing, a data source no longer contains a table or stored procedure used by existing lookup rules, Forms will display a warning and automatically disable these invalid lookup rules.

To edit a data source's configuration details

  1. Select the data source you want to edit.
  2. Click the Edit button at the top of the page.
  3. Make changes to the data source as desired.
  4. Click Test connection to ensure the new configuration works.
  5. Click Save.

    6. Note: If, after editing, a data source no longer contains a table or stored procedure used by existing lookup rules, Forms will display a warning and automatically disable these invalid lookup rules.

To delete a data source

  1. Click the data source you want to delete.
  2. Click the delete button in the toolbar.

    3. Important: Deleting a data source used by a business process will delete all lookup rules associated with the data source.

Related Topics

Supported ODBC Drivers

Many ODBC drivers are supported. Fully supported ODBC drivers include: Microsoft Access Database Engine (supporting Excel, Access, CSV, and TXT), MSSQL, MySQL, PostgreSQL, Oracle, OpenEdge, IBM DB2, FileMaker, SoftVelocity Topspeed, and Pervasive. These drivers have been tested with Forms. While other ODBC drivers may work, they have not been tested, and may have potential issues.

Note: In order to support using Oracle databases, Oracle Data Access Components 11.2 or higher must be installed on the Forms server. However, Forms does not support Oracle Stored Procedures.

Note: On an x64 operating system, if you install Forms 64 bit, use 64 bit ODBC. If you install 32 bit, use 32 bit ODBC.

User vs. System DSN

When you configure the ODBC driver as a system DSN, the ODBC driver is always available when you configure the ODBC data source from Forms. When you configure as a user DSN, the driver is only available when the FormsAppPool identity is set with the user who created the user DSN.

Specifying User Authentication

Once you have an ODBC data source in the ODBC Data Source Administrator, you must add the ODBC data source to the Forms data sources page. Forms uses the username and password you enter in the Forms data sources page, not the ones saved in the ODBC Data Source Administrator. Leaving the username and password blank means you do not need to authenticate to connect to ODBC.

Escape Sequences

Each supported database uses different escape sequences. You must add the escape sequences in your cf_options table when you want to perform a query on a database. The following displays escape sequences based on what is used by the database. If you change the ODBC driver, you must manually update the corresponding option.

Escape Sequences Options
Option ODBC Driver Name Action
ODBCBracketEscapeDrivers sqlsrv, aceodbc The lookup query adds [] around the table and column name (and replaces existing ] with ]]).
ODBCBacktickEscapeDrivers myodbc The lookup query adds `` around the table and column name.
ODBCDoubleQuoteEscapeDrivers ora, psql The lookup query adds "" around the table and column name.

Note: If the Forms database was created in Forms 10.3 or later, the names for the ODBCBacktickEscapeDrivers are ora, psql, ddoe (OpenEdge), fmodbc (FileMaker), and cci (Pervasive SQL).

Note for IBM DB2

Because DB2 uses the local user system, it is better to set the service to run as a domain user and use that domain user for authentication in the ODBC data source. With certain ODBC CLI driver versions the IIS app pool must run as the domain user.