Configuring a Data Source

When configuring any of the lookup or verification processes involving a third party database, you will need to configure the connection to the external database. Quick Fields offers three wizards to help you with this connection, with different capabilities for automation and customization.

  • Windows ODBC (Recommended): Helps configure the data source drivers by connecting to the Windows ODBC Data Sources settings for a particular Windows user or computer.
  • Advanced (Direct connections): Shows all the drivers currently supported by Quick Fields. Requires knowledge of the settings expected by the data source you configure.
  • Expert (Connection strings): Allows you to manually configure the connection string. Requires expert knowledge of how to connect to a data source.

Supported Databases

Below are databases supported by the lookup and verification processes when using a direct connection.

ODBC

Most databases that support Windows ODBC are supported, including

  • Microsoft Access 2003 and 2007
  • CSV
  • Microsoft Excel 2003, 2007, and 2010
  • Microsoft FoxPro
  • Informix
  • Pervasive
  • Microsoft SQL Server
  • SAP HANA

OLE

  • Microsoft Access 2003 and 2007
  • CSV
  • Microsoft Excel 2003, 2007, and 2010
  • Microsoft FoxPro

Direct

  • Informix
  • Oracle
  • MySql
  • Pervasive
  • Microsoft SQL Server

Windows ODBC

The Windows ODBC Configure Data Source Wizard helps you set up your data source drivers by connecting to the Windows ODBC (Open Database Connectivity) Data Sources settings for a particular Windows user or computer.

Third-Party Database Selection

Select a data source type

  • User: Available only to the Windows user logged in to the current computer.
  • System: Available to any user logged in to the current computer.

Choose a data source driver

  • Select a data source driver from the drop-down list.

Administer Data Source Providers

To configure data source drivers

  1. Click Administer Data Source Providers.
  2. The ODBC Data Source Administrator dialog box will open. This is a Windows dialog box associated with the current computer. It is also available from the Windows Control Panel in the Administrative Tools option.
  3. Select the User DSN tab or System DSN tab, depending on what you selected in the first step.
  4. Click Add to select a driver.
  5. A setup dialog box for the selected driver will appear. Configure your preferred settings and click OK.
  6. In the Configure Data Source Wizard, select your driver from the drop-down list.

Third-Party Database Authentication

Choose whether to use Windows authentication or server authentication. If using server authentication, specify a username and password.

When you have finished configuring your data source, click Finish.

Advanced

The Advanced Configure Data Source Wizard offers more choices than the Windows ODBC Wizard, but less information about each selection. To use it, you will need to know the format expected by the data source you are using.

Third-Party Database Selection

Select a Data Source Type

  • Choose ODBC, OLE, or Direct.

Select a Data Source Driver

  • Choose a driver. The available selections will depend on the selected data source type.

Third-Party Database Information

Specify the server, database name, and any other information for your database.

Expert

The Expert Configure Data Source Wizard allows you to connect to a third-party database using a connection string. It allows more customization than the Windows ODBC or Advanced wizards, but requires expert knowledge.

Third-Party Database Connection String

Data Source Provider

  • Click Browse and select a data source provider from the menu.

Data Source Connection

  • Specify a connection string.


    • To use a template
    1. Click Template. The Connection String Templates dialog box will open.
    2. Under Template name, expand the list of available templates.
    3. Select a template.
    4. Click OK.
    5. Under Configure how to connect to your data source:, modify the template to suit your desired database connection.
  • Click Test to test whether the connection works.
  • When you are ready, click Finish.

Custom Queries

In the Input step of processes that connect to outside data sources, you can create custom SQL queries. Custom queries should be used when you want to do something that is not supported by the main user interface. You can use it to sort your results, choose randomly from them, or even append them together into one string so they all get combined (later to be turned into a multi-value field by pattern matching). To write queries, you will need to be familiar with the appropriate SQL syntax and the parameter naming conventions for your data source.

To create a custom query

  1. From the Input step of Field Lookup (Real-Time Lookup) or Lookup, select Writing a custom query from the Configure your query by drop-down menu, then click inside the Query box.
  2. The Custom Query Editor dialog box will open.
  3. Under Custom Query, specify a custom query.
  4. Under Parameter Names, specify parameter names if applicable (see below). Under Type, you can specify the type of data being looked up (Date, Time, String, etc.). Under Parameter Values, specify corresponding values—you can use the token button (right arrow) to insert any available token.
  5. Optional: When configuring parameters, typing information into one parameter line will automatically add another parameter line for you to configure. Specify as many parameter names, types, and values as you need. To remove a parameter, click the trash can at the end of the parameter line.
  6. When you are finished, click OK.
  7. Optional: You can click Test at the bottom of the Input step to see how many results were returned or if there were any errors. Clicking Edit enables you to edit your query.

    Warning: Clicking Test actually runs the query so be careful to not run a query that will do something you do not want to do (e.g., drop a table).

Parameter Names: How to specify the parameter names depends on how you are connecting to the data source:

  • When using an ODBC connection, you cannot use named parameters. Use "?" for each parameter name. They will be interpreted in the order in which they are listed.

  • With any other type of connection, use the naming convention specific to that connection. Named parameters can be used more than once in the query without being listed more than once in the parameter list.

Note: Tokens generated by custom queries will not be automatically available in the Results stage. You can use them by specifying them in the format %(Column.Column Name) for Real-Time Lookup and %(Lookup Process Name_Column Name) for Lookup, replacing the text in italics with your own information. The column name may or may not be case sensitive, depending on your data source.

Warning: Quick Fields does not check or validate your queries. Be careful to not put anything in your query that will harm your database.