Understanding connecting to external data

FileHold gives library administrators the option to define connections to arbitrary data sources for dropdown menus and schema lookups. By default Microsoft SQL Server data sources are supported and customers can request the option of enabling direct access to the list of data providers installed on the server from [email protected].

This article is intended for skilled Windows and database administrators to assist connecting to external data sources and this subject is not included with FileCare support. Our professional services team may be available to help customers that do not have the necessary skill sets available.

The following providers are included out-of-the-box with dot net and available to FileHold .

.Net Framework Data Provider for SqlServer
.Net Framework Data Provider for Oracle
.Net Framework Data Provider for OleDb
.Net Framework Data Provider for Odbc

Specific support is built into FileHold for the .Net Framework Data Provider for MySQL, but this provider must be installed outside of dot net. Other providers may also work with FileHold if they return database schema information in a fashion similar to the SqlServer data provider. The OleDb and Odbc providers are simply a layer on top of an OLE DB or ODBC driver. Those drivers may have their own specific requirements. If they operate similarly to other Microsoft drivers such as Microsoft.ACE.OLEDB.12.0 or the IBM DB2 ODBC driver they should function without issue. Specific support for returning database schema information is also available for Quickbooks and Sage 50 and similar ODBC drivers. If you have a driver or provider that is not on this list and does not appear to be operating with FileHold, contact [email protected].

It is possible to connect FileHold to a wide variety of data sources using ODBC or OLE DB connections, however, there is no way to guarantee any arbitrary data source is fully compatible with FileHold. Make sure you confirm technical compatibility before committing to a solution design using an ODBC or OLE DB connection. In some cases FileHold may be able to support an incompatible data source through a product change.

Selecting special processing by connection string

Many special processing rules can be determined according to the data provider. However, some are specific to an OleDb or Odbc driver. These rules can be enabled according to text in the connection string. For example when asked a list of tables, drivers will typically return a column called "TABLE_NAME", but this is not true for all.

QuickBooks

The special processing is triggered when text in the connection string matches text defined in the library manager web config appSettings section. There are two key values:

QuickBooksTypeDatabasesConnectionStringElements - The value must uniquely match any portion of the connection string used for the dropdown menu or database schema lookup.

Example QuickBooks and BrandXDb setup

The QuickBooks connection string is DSN=QuickBooks Data 64-Bit QRemote;Driver=QRemote for QuickBooks;Open;Open=F;OLE DB Services=-2;. The BrandXDb connection string is DSN=MyBrandXDb;Driver=BrandXDb-64;.

<appSettings>
   <add key="QuickBooksTypeDatabasesConnectionStringElements" value="QuickBooks;BrandXDb;" />
</appSettings>

If there is more than one driver that requires the QuickBooks type processing multiple values can be provided by separating them by semi-colons.

Progress OpenEdge

ProgressTypeDatabasesConnectionStringElements - The value must uniquely match any portion of the connection string used for the dropdown menu or database schema lookup.

You may need to set the default SQL-92 schema. Progress provides some additional reading on this topic. https://community.progress.com/s/article/000027585

Example Progress setup with two different progress servers

The first Progress connection string is DRIVER=DataDirect HDP;HOST=mydbserver.bighost.com; and the second Progress connection string is DRIVER=OpenEdge 6;HOST=mydbserver.bighost.com;

<appSettings>
   <add key="ProgressTypeDatabasesConnectionStringElements" value="DataDirect;OpenEdge;" />
</appSettings>

Passing parameters

Document schema lookups require at least one parameter to be provided to select a single row from the lookup query. The form of the parameter in the lookup query can vary according to the chosen provider. For SqlServer or MySQL, prefix the name with the at sign (@). For Oracle, prefix the name with a colon (:). For OleDb and Odbc, the parameter names are not important. They are positional and indicated by a question mark (?) in the query.

Sample SqlServer or MySQL query

SELECT * FROM MyTable WHERE MyColumn = @MyParameter

If using MySQL with ODBC provider and MySQL driver, only use custom queries with this combination.

Sample Oracle query

SELECT * FROM MyTable WHERE MyColumn = :MyParameter

Sample OleDb or Odbc query

SELECT * FROM MyTable WHERE MyColumn = ?

It is known that installing the MySQL data provider on the FileHold server may impact the web client. You may see messages in the event log like:
The connection string name is missing for the MySqlSiteMapProvider
or
Host 'myfilehold.mycompany.com' is not allowed to connect to this MySQL server (C:\Windows\Microsoft.NET\Framework64\v2.0.50727\Config\machine.config line XXX)
Adding the following to your web client web.config file may resolve the issue:
<configuration>
         <system.web>
           <siteMap>
             <providers>   
               <remove name="MySqlSiteMapProvider" />
             </providers>
          </siteMap>      
         </system.web>
        </configuration>