Connections enable the integration of external data sources with Lists and Lookups within document profiles. Each connection contains one or more queries, which specifies the container (typically a table) within the external data source that should be used.
Connections can be used by the Lists feature to create a list whose values are populated by a database query. Lists can then be mapped to a document profile index field to provide a pick-list style interface when indexing.
Connections are also used by the Lookups feature, which allows the automatic population of document type index fields by querying data from external data sources.
Adding or Editing a Connection
To add a new connection click the Add button at the top right corner of the connections table. To edit an existing connection, select the connection to edit and click the pencil icon. Both adding and editing a connection share the same dialog, as seen in the screenshot above.
- Connection Name - Enter a unique name for the connection. The connection name will be used when selecting connections in Lists and Lookups.
- Data Provider - The selected data provider determines which type of data source the connection should use. The following data source providers are supported:
- Odbc Data Provider - Enables connections to ODBC compliant databases.
- OleDb Data Provider - Enables connections to OleDb compliant databases, such as Microsoft Access.
- OracleClient Data Provider - Enables connections to Oracle compliant databases.
- SqlClient Data Provider - Enables connections to SQL Server databases. Use the appropriate option above for SQL CE databases.
- Connection String - The connection string is used by the provider to establish the database connection. Connection strings for all supported data providers differ based on the provider selected. As the databases used by connections in PSIcapture Fusion typically already exist and contain existing data, users should already have, or have access to the necessary connection string to make the connection. For additional information on connection strings, as well as many examples for various database types, please refer to the following external site: http://www.connectionstrings.com.
Before queries can be added, users must complete the connection configuration and hit the Connect button.
Each query represents a single container within a connected data source.
- Adding or Editing Query - To add a new query click the Add button at the upper right corner of the query table. To edit an existing query, select the query to edit and click the pencil icon. Both adding and editing a query share the same dialog, as seen in the screenshot below.
- Query Name - Enter a unique name for this query. Query names must be unique within the current connection, and are used to identify and select the query in configuration lists or lookups where a query is required.
- Query Type - The query type setting determines how the query is selected or defined. The following query types are supported:
- Simple - Simple query types are defined by selecting a table or view from the connected database.
- Advanced - Advanced query types are defined by creating a SQL Query that returns the necessary columns from the connected database. This type may also be used to create a query from a stored procedure.
- Table / View - Select the table or view that this query should connect to.
Remove a Query
To remove a query from the connection, select the query to delete from the list and click the "x" icon. Removing a query has no effect on the tables or views in the linked external data source, it simply removes the query from the list for the current connection.
Remove a Configuration
To remove a connection from the system, select the connection to delete from the list and click the "x" icon. Removing a connection will also delete all attached queries. The connected external data source is not affected in any way, removing the connection simply removes the link between PSIcapture Fusion and the external data.
Removed connections are not recoverable. If a user wishes to restore a connection then it will need to be recreated.
Connections and queries that are in use by features within the system are not removable. First remove any Lists or Lookups that use the connection or query, then retry the remove operation.