Connections & Queries
Introduction
Connections enable the integration of external data sources with various features within PSIfusion. Each connection contains one or more queries, which specifies the container (typically a table) within the external data source that should be used.
Lists
Connections can be used by the Lists feature to create a list that whose display text and values are populated by a database query. Lists can then be mapped to a Document Type field to provide a pick-list style interface when indexing.
Lookups
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 on the connections tab. To edit an existing connection, select the connection you wish to edit and click the edit button. 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 for features such as Lists and Lookups.
Data Provider
The selected data provider determines which type of data source the connection should connect to. The following data source providers are supported:
| Name | Description |
|---|---|
| Microsoft SQL Server Compact Data Provider | Enables connections to Microsoft SQL CE version 3.5. |
| Microsoft SQL Server Compact Data Provider 4.0 | Enables connections to Microsoft SQL CE version 4.0. |
| 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 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 PSIfusion typically already exist and contain existing data, you 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.
Queries
Before queries can be added, you must complete the connection configuration and hit the Connect button.
Each query represents a single container within a connected data source. Refer to the Query Type setting below for details.
Adding or Editing Query
To add a new query click the Add button on the edit connection dialog. To edit an existing query, select the query you wish to edit and click the edit button. 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 feature configuration lists 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:
| Type | Description |
|---|---|
| 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. |
Support for advanced query types is planned, but is currently not scheduled. Advanced query types will be completed in an undetermined future version of PSIfusion.
Table / View
The table / view selection control will be visible when the Query Type is set to Simple.
Select the table or view that this query should connect to.
Remove a Query
To remove a query from the connection, select the query you wish to delete from the list of queries and click the Remove button. 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 of available queries configured for the current connection.
Remove a Configuration
To remove a connection from the system, select the connection you wish to delete from the list of connections and click the Remove button. 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 PSIfusion and the external data.
Removed connections are not recoverable. If you wish to restore a connection you will have to add a new connection, configure the connection string, and recreate any necessary queries.