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:

NameDescription
Microsoft SQL Server Compact Data ProviderEnables connections to Microsoft SQL CE version 3.5.
Microsoft SQL Server Compact Data Provider 4.0Enables connections to Microsoft SQL CE version 4.0.
Odbc Data ProviderEnables connections to ODBC compliant databases.
OleDb Data ProviderEnables connections to OleDb compliant databases, such as Microsoft Access.
OracleClient Data ProviderEnables connections to Oracle databases.
SqlClient Data ProviderEnables 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:

TypeDescription
SimpleSimple 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.

Removed queries are not recoverable. If you wish to restore a query you will have to add a new query and reconfigure the simple query or recreate the SQL query used for an advanced query.

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.

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.

On this page:


Related Items: