Database Query

The Database Query input or action task retrieves data from various databases to use as input data. The data received by the task may be kept as is or converted to the CSV, Fixed Length Columns or XML format.

Database Query input tasks are used to start a process. The action task is used to gather secondary input (see Input tasks and Action tasks).

Note: Database Query tasks require version 2.5 or higher of the Microsoft Data Access Components (MDAC), including JET 4.0.

When adding a Database Query task, you have two options:

  • You can use static properties (properties that will remain the same regardless of the data processed at run-time). This option lets you use an Open DataBase Connectivity (ODCB) compliant data source. You can also edit the SQL statement that assembles the database table. Note that you can import a database connection configuration that you previously exported from PlanetPress Design (when you created a document) or from OL Connect Workflow (when you set up a sample data file for a process).
  • You can use dynamic properties (properties that include variables or data available at run-time). This option lets you create a dynamic database connection string as well as an SQL statement that changes based on the data processed by OL Connect Workflow. Note that this option will not let you test the query performed by this task before it is performed with actual data.

Input

Any data file. The data file will be discarded by the task.

Processing

A connection to the selected database is made, the data is retrieved, and an output in the selected emulation format is generated.

The password is not displayed in the log files, provided it starts with pwd or password.

Output

The result of the query is output in the selected data format. The current emulation is changed to the selected format. Metadata and Job Info variables are not modified in any way.

Job Information definitions

  • %1 - DB Connection String

  • %2 - The actual query used

  • %3 - The number of records returned.

Task properties

Database Connection Tab

  • Database group
    • Location: Enter either the path and name of the database or a database connection string in this box. You may click to navigate to the database and paste the database path and name automatically to this box. You may also click create an ODBC connection string to the data source and paste the string automatically to this box. If a login name and password are required to connect to the database, a dialog box is displayed and the information you enter is saved in the configuration of the OL Connect Workflow Database action task.
    • Table/Query: Select the table or query containing the information you need as your input data.
  • Range group
    • All: Select this option use all the records included in the database.
    • Records: Select this option to use only some of the records in the database. Indicate the range by entering the number of the first record followed by a dash and the number of the last record. To use records 50 to 75, for example, enter 50-75. Note that this option is intended mostly for testing purposes, since in real life scenarios, you typically want to use all the records stored in a database.
  • Emulation group: Use options from this group to customize the data file generated by the OL Connect Workflow Database action task.
    • Output file emulation: Select the emulation corresponding to the type of output file you want the OL Connect Workflow Database action task to generate.
    • CR-LF replacement: If you want CR-LF (Carriage Return-Line Feed) characters within the data file to be replaced by another character, use this box to indicate which character to use. You may select the replacement character from the list or type your own.
    • Emulation options group: Options from this group change based on the selected output file emulation.
    • OL Connect Workflow Database Emulation: If you selected OL Connect Workflow Database in the Output file emulation box, the following options are available:
      • Create data pages as follows: Select the option that will be used to generate the data pages. Each data page created using the table or query selected above (Table/Query box) can contain a single record, a fixed number of records, or a variable number of records. To choose the last option, select one of the When [field name] changes listed in this box.
      • Sort on conditional field: Select this option if you want the table to be sorted using the field selected in the Create data pages as follows box before the data page creation process is started.
      • Maximum number of records per page: For data pages that contain multiple records (a fixed or variable number of records), enter a maximum number of records per page in this box. Note that this value cannot exceed 4,000.
    • CSV Emulation: If you selected CSV in the Output file emulation box, the following options are available:
      • Sort on field: If you want the table to be sorted before the data page creation process is started, select the sort field from this box.
      • Text delimiter: Select the text delimiter to be used in the generated file.
      • Field separator: Select the field separator to be used in the generated file.
      • Add a header record with field names: Select this option if you want the generated file to have a header record (a record that includes the field names only).
    • Fixed Length Columns Emulation: If you selected Fixed length columns in the Output file emulation box, the following options are available:
      • Sort on field: If you want the table to be sorted before the data page creation process is started, select the sort field from this box.
      • Default width: This box is used to set the default width for all fields. It is set to 60 by default, but can be set to any value between 1 and 65535. This value is applied to all the fields in the generated file. To set different widths for each field, use the Configure Width button. Doing this disables the Default width box.
      • Configure Width: Click to set the width of each field in the generated file. The displayed Configure Width dialog box lists all the fields in the file that will be generated and indicates their widths. To change the indicated widths, simply click the values displayed in the Width column and enter new values. Click OK when you are done to close the dialog box. You will then no longer be able to use the Default width box.
    • XML Emulation: If you selected XML in the Output file emulation box, the following options are available:
      • Create data pages as follows: Select the option used to generate the data pages. Each data page created using the table or query selected above (Table/Query box) can contain a single record, a fixed number of records, or a variable number of records. To choose the last option, select one of the When [field name] changes listed in this box.
      • Sort on conditional field: Select this option if you want the table to be sorted using the field selected in the Create data pages as follows box before the data page creation process is started.
      • Data encoding: Select the encoding used in the generated XML file. By default, this option is set to the default encoding of the computer used to create or edit the configuration. You may choose any encoding listed in the drop-down list or enter your own.
      • Maximum records per page: Select this option if you want to limit the number of records per page. This option is only available if you indicated that you wanted each data page to contain several records in the Create data pages as follows box.
    • XML for PrintShop Mail: This emulation is specifically for use with merging your data with a PrintShop Mail document, using the PrintShop Mail task (see PrintShop Mail). No options are offered, as this format is static and should not be modified.
  • Alternate syntax: Select to prevent automatically enclosing the names of any database tables and fields that appear in the SQL query in square brackets when it exits the Advanced SQL Statement dialog box. The alternate syntax may be required for some database types.
  • Edit SQL: Click to create and test an advanced SQL query; see Advanced SQL Statement Dialog.
  • Import Config: If you previously created and exported a OL Connect Workflow Database Connection configuration, click this button to import it. This saves you the trouble of configuring the connection every time.
  • Client-side Cursor: When this option is enabled, the complete result set is downloaded before processing starts, and changing records is done by OL Connect Workflow. This is generally faster for queries returning a small number of results ; otherwise the start of the record processing can be delayed since the whole record set must be downloaded.

    Note: MySQL, using ODBC 5.0, must be set to use a client-side cursor.
    Microsoft Access will always work better when using a Server-Side cursor.

  • Include password in config: Select to save an encrypted version of the database password (if any) within the exported configuration.
  • Export Config: Click to export the currently displayed properties of the task. The exported configuration can then be reused on other OL Connect Workflow workstations.

Dynamic SQL Tab

  • Use dynamic values at run-time: Select to use a dynamic database connection string and / or SQL statement at run-time. Check this box to enable the options included in this group (this disables the corresponding options in the General tab).
    • Parse normally: Select to interpret any backslashes included in the database connection string as backslashes. If this option is not selected, any backslash that is not doubled will be disregarded.
    • Expect record set: Check if you are expecting a result from the database after executing the SQL query. If the query is expecting a record set in return and does not return one, the task will trigger an error.
    • Database connection string: Enter a variable connection string in this box. To do this you may begin by clicking to create an ODBC connection string to the data source and paste the string automatically to this box. Note that if a login name and password are required to connect to the database, a dialog box is displayed and the information you enter is saved in the configuration of the OL Connect Workflow Database action task. Another option, if a database connection string (not a database path and name) was already entered in the Database Connection tab, is to simply copy and paste it to this box. Bear in mind that if the Parse normally option is not selected, any backslashes included in the connection string that are not doubled will be disregarded. Once your connection string is displayed in this box, you can edit it by adding variables or data selections.
    • SQL statement: Enter your SQL statement. Remember that you may use variables and data selections in your statement.

On Error Tab

For a description of the options on the On Error tab see Using the On Error tab.

Miscellaneous Tab

The Miscellaneous tab is common to all tasks.

It contains a text area (Task comments) that lets you write comments about the task. These comments are saved when the dialog is closed with the OK button and are displayed in The Task Comments Pane.

Check the option Use as step description to display the text next to the icon of the plugin in the Process area.

The tab also provides an option to highlight the task in The Process area with the default color, set in the Preferences (see Colors), or the color selected or defined under Highlight color on this tab.
To revert the selected highlight color to the default color, open this tab, turn the Highlight option off and close the dialog with the OK button; then turn highlighting back on.
Highlighting can also be turned on and off via the task's contextual menu and with the Highlight button on the View ribbon.