Using the wizard for databases

The DataMapper wizard for database files helps you create a data mapping configuration for a database file. The wizard extracts the data in one extraction step.

The wizard cannot create detail tables. If the file contains transactional data, the data mapping configuration is best created without a wizard (see Creating a new data mapping configuration).

Opening a database file with a wizard

There are two ways to open an XML file with a wizard: from the Welcome screen or from the File menu.

  • From the Welcome screen

    1. Open the PlanetPress Connect Welcome page by clicking the icon at the top right or select Help > Welcome on the menu.

    2. Click New at the left, then click Data at the right.

    3. Select From database.

    4. Use the drop-down to select the database type.

    5. Click Next.

  • From the File menu

    1. In the menu, click File > New.

    2. Click the Data mapping Wizards drop-down and select From databases.

    3. Click Next.

    4. Use the drop-down to select the database type.

    5. Click Next.

Wizard settings for a database file

After opening a database file with a wizard there are a number of settings to make, depending on the database type (see below).
On the last page of the dialog, click Finish to close the dialog and open the actual data mapping configuration.

Note: After creating the initial data mapping configuration you may use a custom SQL query via the Input Data Settings; see Settings for a database.

Tip: The Sort on option, combined with the Stop data mapping option of the Action step, allows to process only a group of items without having to examine all records. (See also: Action step properties.)

MariaDB, MySQL, SQL Server or Oracle

  • Server: Enter the server address for the database.

  • Port: Enter the port to communicate with the server. The default port is 3306.

  • Database name: Enter the exact name of the database from where the data should be extracted.

  • User name: Enter a user name that has access to the server and specified database. The user only requires Readaccess to the database.

  • Password: Enter the password that matches the user name above.

  • Table name: The selected database is a set of related tables composed of rows and columns corresponding respectively to source records and fields. Select a table from which you want to extract data.

  • Encoding: Choose the correct encoding to read the file.

    • Sort on: Select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

Microsoft Access

  • Password: Enter a password if one is required.

  • Table name: The selected database is a set of related tables composed of rows and columns corresponding respectively to source records and fields. Select a table from which you want to extract data.

  • Encoding: Choose the correct encoding to read the file.

    • Sort on: Select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

ODBC Data Source

Caution: ODBC drivers do not all work with Java applications. Since PlanetPress Connect is a Java application, it is strongly recommended to use JDBC instead.

  • ODBC Source: Use the drop-down to select an ODBC System Data Source. This must be a data source that has been configured in the 64-bit ODBC Data Source Administrator, as PlanetPress Connect is a 64-bit application and thus cannot access 32-bit data sources.

  • This ODBC source is MSSQL: Check this option if the ODBC source is MSSQL (SQL Server). The options below appear under MSSQL-ODBC advanced configuration:

    • Windows authentication: Select to use the Windows user name and password that are used by the Connect Service.

    • SQL Server authentication: Select to use the User name and Password set below to connect to the SQL Server:

      • User name: Enter the SQL Server user name.

      • Password: Enter the password for the above user name.

    • Sort on: Select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

JDBC

Note: Since JDBC can connect to multiple types of databases, a specific database driver and path to this driver's JAR file must be specified.

  • JDBC Driver: Use the drop-down to select which JDBC Driver to use for the database connection.

  • JAR file path: Enter a path to the JAR file that contains the appropriate driver for the database.

  • Server: Enter the server address for the database server.

  • Database name: Enter the exact name of the database from where the data should be extracted.

  • User name: Enter a user name that has access to the server and specified database. The user only requires Read access to the database.

  • Password: Enter the password that matches the user name above.

  • Advanced mode: Check to enable the Connection String field to manually enter the database connection string.

  • Connection string: Type or copy in your connection string.

    • Sort on: Select a field on which to sort the data, in ascending (A-Z) or descending (Z-A) order. Note that sorting is always textual. Even if the selected column has numbers, it will be sorted as a text.

Note: To instruct the SQL Server driver to not use encryption, the ";encrypt=false" parameter needs to be present in the connection string. For more information see Known Issues.