ODBC Data Sources
This page provides details on ODBC data sources in Laserfiche Forms.
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: Forms 9.1 introduced support for using Oracle databases as data sources. In order to do so, Oracle Data Access Components 11.2 or higher must be installed on the Forms Server machine. 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.
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 is 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 local user system, it is better to set the service to run as a domain user and use that domain user for authentication in ODBC data source. With certain ODBC CLI driver versions the IIS app pool must run as the domain user.