Creating and deploying custom database drivers

Custom database drivers provide JDBC connectivity for database vendors that are not supported by default in Infor ION.

This section describes how to create and deploy a custom database driver for use with Infor ION when the required database vendor is not supported by default.

Custom database drivers are deployed using the Custom Database Driver Wrapper.

The wrapper packages the database driver as an OSGi bundle that can be uploaded to ION.

Before you deploy a custom database driver, ensure that these requirements are met:

  • The database supports JDBC connectivity.
  • A JDBC driver JAR file is available.
    • Multiple JAR files are not supported. If the driver consists of multiple JAR files, merge the files into one flattened JAR file.
  • A supported Java Development Kit (JDK) is installed on the system where you run the wrapper.
    • A Java Runtime Environment (JRE) is not sufficient.
  • You have access to ION Desk with permissions to configure ION Service.
Note: This procedure requires advanced database and JDBC knowledge.

Understanding custom database drivers

A custom database driver extends ION database connectivity for a database type that is not supported by default in ION.

The custom database driver is based on a JDBC driver JAR file and is deployed as an OSGi bundle.

The Custom Database Driver Wrapper creates the deployment bundle that is uploaded to ION.

After deployment, the custom database driver is available for supported JDBC application connection points and database connection points.

Preparing a single (flattened) database driver JAR

To create a flattened database driver JAR:

  1. Create a temporary directory, for example: C:\Temp\FlatJars.
    Note: If the database driver consists of multiple JAR files, merge the files into one flattened JAR file before you continue.
  2. Extract all database driver JAR files into this directory.
  3. Open a command prompt and navigate to the directory.
  4. Run the command: 
    jar -cvf flattened.jar ./
  5. Verify that the directory contains one JAR file.

Use this flattened JAR file in the subsequent steps.

Downloading the Custom Database Driver Wrapper

To download the wrapper:

  1. Navigate to Enterprise Connector management pages.
  2. Open Custom Database Drivers.
  3. Click Download Custom Database Driver Wrapper.
  4. Save the file as BundleWrapper.zip.

Extracting the Bundle Wrapper

To extract the wrapper:

  1. Extract the contents of BundleWrapper.zip.
  2. Extract the file to C:\temp\BundleWrapper.
  3. If a different location is used, this location must be reflected in the InstallPath property.

Adding the database driver JAR

To add the driver:

  1. Copy the database driver JAR file to BundleWrapper\OriginalJar.
  2. Ensure that only one JAR file is present in this directory.

Configuring driver properties

The behavior and identification of the custom database driver are defined in the properties.txt file.

To configure the properties:

  1. In the BundleWrapper directory, locate the properties.txt file.
    • Example property files are available in the BundleWrapper\examples directory.
  2. Update the properties to match the database driver being deployed.

Required properties

These properties must be specified:

  • InstallPath

    Absolute path where the BundleWrapper directory is extracted.

    • This path must match the localRepository property in BundleWrapper\build\settings.xml.

  • OriginalJar

    Name of the database driver JAR file located in the OriginalJar directory.

  • BundleSymbolicName

    Unique identifier for the bundle, following the reverse domain name convention.

  • BundleVersion

    Version of the OSGi bundle.

    • Use the database driver version.

  • BundleName

    Human-readable name of the bundle.

  • BundleDescription

    Description of the database driver.

  • SpringDriverClassName

    Fully qualified class name of the database driver.

  • SpringTypeName

    Name of the database type as it appears in ION Desk.

    • This name must not match any standard database type that is supported by ION.
    • For Oracle custom drivers used with AnySQL Modeler, the name must start with ORACLE_.

Advanced properties

  • SpringReturnAutoGeneratedKeys

    Set this value to true if the database does not permit explicit selection of columns to be returned for insert statements.

    In this case, the database automatically determines which generated key to return.

    The default value is false.

  • SpringWrapAsLob

    Set this value to true if the database driver requires explicit Blob or Clob wrapping, such as PostgreSQL.

    The default value is false.

  • SpringStreamAsLob

    Set this value to true for JDBC 4.0 operations.

    The default value is false.

  • SpringOracleLobHandler

    This handler is used by Oracle drivers only.

    The default value is false.

  • SpringUrlRegularExpression

    Optionally specify a regular expression if the URL used to connect to the database must meet specific requirements.

  • SpringDataDefinitionInLowerCase

    Optionally set to true if the DDL (data definition language) is lowercase or for a case-sensitive database.

    • If the DDL is lowercase and SpringReturnAutoGeneratedKeys is false, the auto index for column C_ID uses c_id.
Note: Do not use special XML characters such as &, <, >, ', and " in SQL statements. Use escaped equivalents instead.

Polling query properties

  • SpringJdbcUnprocessedPollingEntriesSql

    If the database type is configured for an application connection point, specify the query that selects new messages from the outbox.

    • For database connection points, the query is not used.
    • Use these placeholders in the query:
      • ?1 replaces the placeholder with the state:
        • 0 = unprocessed
        • 1 = processed
      • ?2 replaces the placeholder with the number of rows to retrieve.
    • The * character in the SELECT clause is replaced with the C_WAS_PROCESSED column name when status information is retrieved.
    • The ?1 placeholder is replaced by 0.
    • The ?2 placeholder is replaced by 1.
    • The query must always contain the * character in the SELECT clause.

  • SpringJdbcUnprocessedEntriesByTenantSql

    If the database type is configured for an application connection point, specify the query that selects new messages from the outbox based on the tenant.

    • For database connection points, the query is not used.
    • Use these placeholders in the query:
      • ?1 replaces the placeholder with the state:
        • 0 = unprocessed
        • 1 = processed
      • ?2 replaces the placeholder with the number of rows to retrieve.
    • The * character in the SELECT clause is replaced with the C_WAS_PROCESSED column name when status information is retrieved.
    • The ?1 placeholder is replaced by 0.
    • The ?2 placeholder is replaced by 1.
    • The query must always contain the * character in the SELECT clause.

  • SpringJdbcUnprocessedEntriesByTenantAndLogicalIdSql

    If the database type is configured for an application connection point, specify the query that selects new messages from the outbox based on the tenant and Logical ID.

    • For database connection points, the query is not used.
    • Use these placeholders in the query:
      • ?1 replaces the placeholder with the state:
        • 0 = unprocessed
        • 1 = processed
      • ?2 replaces the placeholder with the number of rows to retrieve.
    • The * character in the SELECT clause is replaced with the C_WAS_PROCESSED column name when status information is retrieved.
    • The ?1 placeholder is replaced by 0.
    • The ?2 placeholder is replaced by 1.
    • The query must always contain the * character in the SELECT clause.

Connection test properties

  • SpringJdbcConnectionTestSql

    Specify the query that tests the connection to the required inbox and outbox tables in the database through an application connection point.

    • Use these placeholders in the query:
      • ?1 replaces the placeholder with the required column names.
      • ?2 replaces the placeholder with the required table names.
      • ?3 replaces the placeholder with the number of rows to retrieve.

  • SpringDatabaseConnectionTestSql

    Specify the query that tests a database connection point.

    The query validates whether ION can connect to the database.

Building the custom database driver bundle

After you configure the properties, generate the deployment bundle.

  1. Open a command prompt in the BundleWrapper directory.
  2. Run the command: 
    build.bat
  3. Verify that a ZIP file is generated.

This ZIP file contains the OSGi bundle that is required by ION.

Uploading the custom database driver to ION

To upload the custom database driver:

  1. Navigate to Enterprise Connector management pages.
  2. Open Custom Database Drivers.
  3. Click Import Custom Database Drivers.
  4. Upload the generated ZIP file.
  5. Confirm that the driver appears in the list of custom database drivers.
Note: When a custom database driver is added or removed, the Enterprise Connector service is restarted automatically. During this time, the Enterprise Connector is temporarily unavailable.

Using the custom database driver in a connection point

After the driver is uploaded, it can be used in these connection points:

  • Application connection points of type JDBC
  • Database connection points

To use the custom driver:

  1. Create a new connection point.
  2. Select the correct Enterprise Location.
  3. Select the database driver.
    • Scroll to the section for custom drivers.
    • The database driver name corresponds to the value specified in SpringTypeName.
  4. Specify the JDBC URL.
    • Custom database drivers do not support basic mode configuration.
  5. Test the connection.
  6. Complete the remaining configuration steps, such as document selection and activation.

If ION Desk was open when the driver was deployed, refresh the browser to make the new database type visible.

Limitations

  • Database connection points of type AnySQL Modeler support Oracle custom drivers only.
  • Multiple database driver JAR files are not supported unless merged into one flattened JAR.