Database Connections Admin

Database Connections Admin provides an area for administrators to define lists of data that can be displayed on index fields, form fields, or other custom sources within VisualVault. By default there are two connections already established in the default installation of VisualVault. These connections are to the VisualVault database and the VisualVault_FormData database. Administrators can add other connections to external databases using an appropriate Connection String. Once a connection has been defined, new queries can be defined under each connection. Once a query is established, it will become accessible to administrators when defining an index field on a folder, or a drop down list on a form template. By default when a form is released in VisualVault, a query will be created in the FormData database that will display a wide open list of all data collected against a form.

Connections

Adding a New Connection

To add a new connection in Database Connection Admin you must be a member of either the VaultAdmins or VaultAccess groups. Navigate to the Control Panel - Enterprise Tools tab, then select Database Connection.

To add a connection the user will select the Add Connections button. The following screen will then appear:

New Database Connection View

Key in a name for the connection as well as a description. Then a connection string will need to be entered for the database that will be accessed outside of VisualVault. The name of the database connection string cannot be the same name as another connection string defined within VisualVault. An example of a SQL Server type database connection string is as follows:

SQL Authentication: data source=SERVERNAME;initial catalog=VisualVault;persist security info=False;user id=sa;password=something;

Windows Authentication: data source=SERVERNAME;initial catalog=VisualVault;persist security info=False;integrated security=SSPI;

Administrators will need to refer to .NET programming documentation in order to derive appropriate connection strings for other types of databases. We strongly recommend that administrators setup a user on the database server that is a read only type user. This will prevent information shown in a connection string from being used inappropriately to gain access to other database resources. If you are using VisualVault as a hosted solution, there are licensed features available for you to query a list of information into VisualVault drop down lists.

Connection Strings

Connection strings are programmatic information that assists the .NET programming language to know how to connect to a database or data source. VisualVault only supports connections to Microsoft SQL Server databases.

The following are examples of two SQL Server connection strings:

SQL Authentication: data source=SERVERNAME;initial catalog=VisualVault;persist security info=False;user id=sa;password=something;

Windows Authentication: data source=SERVERNAME;initial catalog=VisualVault;persist security info=False;integrated security=SSPI;

Deleting a Connection

To delete a Database Connection, the user must have VaultAccess permissions to VisualVault. If a user has these permissions the user can navigate to Control Panel - Enterprise Tools tab - Database Connections. The select the check mark next to the database connection the user wishes to delete. Once the user has selected the check box, they then need to select Delete Selected Connections. If the connection or any of its queries are being used in VisualVault, then the user will not be allowed to delete the connection.

Database Connection View

Queries

Adding Queries

To add a query a VaultAccess user will need to:

Navigate to Control Panel - Enterprise Tools - Database Connections - and select the queries link for a Database Connection.
Select the Add Query button

Key in the Name and description of the Query.
Select the Query Type.
Key in the SQL Select statement for the Query.

Select the Preview button to see the end results of the query.
Select Save if the Query is showing the desired results.

When adding a new query there are several key points that should be considered. First, you can key in something like "select * from groups" for the groups table to get an idea of what columns are available on the table. Once you know the columns you want displayed, you can modify your SQL query to reflect the targeted columns. We strongly recommend that you optimize your queries to insure they are efficient and fast. Queries that are not optimized can cause the system to be slow as a result of the query and not the application. We recommend some of the following methods to optimize a query:

Select top 100 - limit the query results to only be the top 100 items.
Select distinct - limit the query to only distinct values.
Select column from table where - give only a list of items that meet a certain criteria.

Other factors that may contribute to performance of queries include:

Slow performance of the server hosting the data source services.
Poor disk access performance.
Slow or congested network connection.

Users of the Database Connection Admin should be used by experienced and knowledgeable staff who understand how to build appropriate queries.

Queries cannot contain Update, Delete, Union or other database manipulation type queries. A query can include a Text Type query (SQL Select statement), or a Stored Procedure.

Deleting Queries

Queries can be deleted by navigating to the Database Connection Admin and a specific Database Connection. A screen similar to the following screen will appear:

Query List View

Once on this view the user will select the check box next to the query they wish to delete and select Delete Selected Queries. If the query is being used in VisualVault then the user will not be allowed to delete the query.