PlanetPress Database

PlanetPress Database action tasks retrieve 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.

PlanetPress Database action tasks are not considered input tasks as such, because they cannot be used to start a process. Although they cannot be used to get the process’ initial input, they can be used to gather secondary input (see Input Tasks). In cases where all your data comes from databases, you can use a Create File input task as a dummy task at the beginning of your process, and then use a PlanetPress Database action task to gather your actual data.

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

When adding a PlanetPress Database action task, you have two options:

You can use static properties (properties that will remain the same regardless of the data processed at runtime). 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 PlanetPress 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 runtime). This option lets you create a dynamic database connection string as well as an SQL statement that changes based on the data processed by PlanetPress 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.

Output

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

PlanetPress Database action task properties are as follows:

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 PlanetPress 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 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 PlanetPress Database action task.
- Output file emulation: Select the emulation corresponding to the type of output file you want the PlanetPress 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.
- PlanetPress Database Emulation: If you selected PlanetPress 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 Action 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.
Import Config: If you previously created and exported a PlanetPress 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 PlanetPress. 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 recordset must be downloaded.
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 PlanetPress Workflow action task. The exported configuration can then be reused on other PlanetPress Workflow workstations.

Dynamic SQL tab

Use dynamic values at runtime: Select to use a dynamic database connection string and/or SQL statement at runtime. 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 PlanetPress 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 ti 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 is 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

By default, any action task, branch, splitter or condition that generates an error will simply be ignored, and the task just under it (not within a branch) will be given control of the job file without any modification. Any initial input task that generates an error will stop the process from running as a whole, and output tasks will not generate output. The On Error tab can be used to overwrite the default behaviors.

Send to Process: Check this option to send the job file to an error management process.
Error Process drop-down: Enabled only when the Send to Process option is checked. Lists any process of which the initial input task is the Input Error Bin task.
Action Group: This group is disabled in the initial input tasks and defaults to Stop Process. In all other tasks where the On Error tab is present, the following options are available:
- Ignore: The task is ignored as if it did not exist, and the job file is passed on to the next task in the process.
- Stop Branch: If the task is in a branch of the process, the branch is stopped and the job file is returned to the process after the branch. The branch will not produce any output.
- Stop Process: The process is stopped and no more processing is done. No further output is produced.
Log Message: Check this option to enable logging a custom error message in the PlanetPress Suite Workflow Tools' log file.
Message: Enabled only when the Log Message option is checked. Enter a message that will be logged in the PlanetPress Suite Workflow Tools' log file. You can use any variables available in PlanetPress Workflow to customize the message.
Store the message in variable: Select in which jobinfo, local or global variable you want to store the message content.
ID: Enter an error ID. This ID will be visible in the Windows Event Viewer. However, the ID is not visible in the PlanetPress Suite Workflow Tools' log file.
Store the ID in variable: Select in which jobinfo, local or global variable you want to store the error ID.
Reset to defaults: Resets all options in this tab to their default values.

If storing the message or ID, if they are store in a jobinfo they will be available in any error handling process where errors are being forwarded. In all cases, if your process continues after the error, the contents of the variables selected in this window will be available for the rest of your process, or whenever they are overwritten.

Common Errors

Though some error messages are specific to a task in particular, others may apply to any and all tasks because they are related more to the system than to PlanetPress itself. Some examples would be W3813, W3830, W3991, W4005. These correspond to issues such as not having any space to write files, permission errors on folders or files, etc.

Comments Tab

The Comments tab, added in PlanetPress Suite 7.5, is common to all tasks. It contains a single 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.