Introduction

ConnectALL's Database Adapter provides the capability to sync records to and from ALM applications to a database. While traditional adapters use HTTP connections to fetch the content, the Database Adapter uses JDBC connections to perform the DML operations on the database tables.

What's Supported

Functionalities

Can handle multiple database vendors (Oracle, MySQL, SQL Server, Postgres)

Has the ability to map the columns of the table to the ALM fields

Create and update new records in the databases

Supports filtering records using traditional SQL

Supports Dynamic Record Linking

Setup

Configuration

Create an automation between your application to the database.

Enter the connection details for the Database Adapter.
URL: JDBC connection string
Username: Login user
Password: Password
Database type: Database engine classification valid values are (MySQL, Oracle, Postgres, and MSSQL)

Entity Mapping

The Database Adapter lists all the catalogs and schemas defined on the database for mapping. Based on the catalog and schema selected, the tables will be listed.

Flow Filter

Note that ConnectALL will use the poll query as an additional 'AND' clause when trying to sync the records from database tables. The syntax is governed by the SQL 'Where' clause of the database. Query results are limited to 1000 by default.

Sync Type

ConnectALL does support both 'Poll' and 'Push' flows, even though 'Poll' is the most commonly used option for databases. The "Change Date" field should be mapped as defined in the "Field Mapping" section. Based on the option chosen in the Sync Type field (as shown below) ConnectALL will query the modified records.

Field Mapping

The Database Adapter will list all the columns defined on the table selected, so they can be mapped to the ALM fields as required.	To save the field mapping, you will need to select the ID and change date fields, and set the database fields value types' to Id and lastUpdatedDt respectively:
	*Click the cogwheel icon to select the data type for the selected fields.

To make a database table compatible with the Database Adapter, You need to first map an auto-incrementing column to the ID data type and then map a column to the lastUpdatedDt data type, that will update for every alteration. Below is an example file containing compatible tables for multiple database types.

We have provided some Sample Database Scripts for your reference.

Enhancements

Connection Pooling

The Database Adapter allows the managing of the JDBC connections that are created. ConnectALL manages these connections by pooling the connection objects, and the same can be configured per automation in the "DatabaseAdapter.Properties" file under the "$MULE_HOME/conf" directory.

Example

# Connection Pooling configuration for MySQLJiraBug automation

# ###################################################################

# Maximum active connections

database.MySQLJiraBug.maxActive=4

# Maximum idle connections

database.MySQLJiraBug.maxIdle=1

# Block when exhausted flag database.MySQLJiraBug.blockWhenExhausted=true

Make sure you restart the Tomcat and ConnectALL core services after making changes to the Connection Pooling Profile.

Database Specific Customizations

The Database Adapter provides many customizations to configure based on the requirements. The property modifications shown below are all done in the "DatabaseAdapter.properties" file in the "$MULE_HOME/conf" directory.

In the below examples, ‘MySQLJiraBug’ is the name of the automation(the name of the automation will be displayed). And ‘MySQL’ is the database used. Note that, if you are using 'Postgres' or 'Oracle' as your database, then, the name of the respective database will be displayed (in lowercase text).

JDBC Driver

The JDBC driver used for connecting to the database is configured using the property <database type>.driver=<fully qualified class name>
Default values:

mysql.driver=com.mysql.jdbc.Driver
oracle.driver=oracle.jdbc.OracleDriver
mssql.driver=net.sourceforge.jtds.jdbc.Driver
postgres.driver=org.postgresql.Driver

CODE

JDBC drivers for Oracle, Postgres, and SQL Server are not bundled by ConnectALL. They need to be deployed manually.

To add the JDBC drivers that are going to be used by ConnectALL, copy the Jar file of your database driver into the $MULE_HOME/lib/user directory and the $CATALINA_HOME/lib/ directory and restart Tomcat and ConnectALL core services.

Catalog/Schema separator

Databases will have different namespaces such as catalogs, schemas, instances, and databases to locate a database table. ConnectALL uses the fully qualified name of a table based on the configurations defined in the Entity Mapping screen. The fully qualified name of a table is computed by concatenating the "Catalog", "Schema" and "Table" names delimited by a "." \[Dot\] separator by default. If needed, you can configure the separator using the below property.

MySQLJiraBug.mysql.catalog.schema.separator=.

CODE

Insert template

To insert new records into a table, ConnectALL uses this template to create the insert statement, where $TABLE is a placeholder for the table name, $INSERT_FIELDS is a placeholder for comma-separated field names, and $INSERT_VALUES is a placeholder for comma-separated values. The default insert statement is "insert into $TABLE ($INSERT_FIELDS) values ($INSERT_VALUES)", which can be customized using:

MySQLJiraBug.mysql.insert.template=insert into $TABLE ($INSERT_FIELDS) values ($INSERT_VALUES)

CODE

Update template

To update existing records in a table, ConnectALL uses this template to create the update statement, where $TABLE is a placeholder for the table name, $UPDATE_FIELD_VALUES is a placeholder for the comma-separated field name, and value pairs and $ID_FILTER is the placeholder for record id filter. The default update statement is "update $TABLE set $UPDATE_FIELD_VALUES where $ID_FILTER" which can be customized using:

MySQLJiraBug.mysql.update.template=update $TABLE set $UPDATE_FIELD_VALUES where $ID_FILTER

CODE

Select template

To retrieve existing records in a table, ConnectALL uses this template to create the select statement, where $TABLE is a placeholder for the table name and $SELECT_FIELDS is a placeholder for comma-separated field names. The default select statement is "select $SELECT_FIELDS from $TABLE" which can be customized using:

MySQLJiraBug.mysql.select.template=select $SELECT_FIELDS from $TABLE

CODE

Integer value formatting

The formatting of the values might be different for each data type across databases. The datatype formatting templates help to resolve these differences. The default template for an Integer value is '$VALUE'.

MySQLJiraBug.mysql.integer.value.template=$VALUE

CODE

String value formatting

The formatting of the values might be different for each data type across databases. The datatype formatting templates help to resolve these differences. The default template for a String value is '$VALUE'.

MySQLJiraBug.mysql.string.value.template='$VALUE'

CODE

Date value formatting

The formatting of the values might be different for each data type across databases. The datatype formatting templates help to resolve these differences. The default template for String values is TO_DATE('$VALUE'\,'$FORMAT') where $VALUE is the placeholder for value and $FORMAT is the placeholder for date format.

MySQLJiraBug.mysql.date.value.template='$VALUE'

CODE

For parsing the dates correctly, ConnectALL needs to know the correct format to be used to convert the application date when inserting or updating table records. For this purpose — ConnectALL has 2 property options to convert the date to a given format and to tell the database the format.

# Format to which application date needs to be converted to
MySQLJiraBug.mysql.java.date.format=yyyy/MM/dd
# Format to be sent to the database during insert and update
MySQLJiraBug.mysql.sql.date.format=yyyy/mm/dd

CODE

Important

If you are using Oracle as your database, change the property for the date field from – **oracle.date.value.template=TO_DATE($VALUE,$FORMAT) to oracle.date.value.template=$VALUE in the DatabaseAdapter.properties file.

Also, if you are using any other databases (excluding MYSQL and Oracle), if the date field is not parsed, change the date.value.template=TO_DATE($VALUE,$FORMAT) to *date.value.template=$VALUE* in the DatabaseAdapters.properties file.

Datetime value formatting

The formatting of the values might be different for each datatype across databases. The datatype formatting templates help to resolve these differences. The default templates for String values are TO_DATE('$VALUE'\,'$FORMAT') where $VALUE is the placeholder for value and $FORMAT is the placeholder for date format.

MySQLJiraBug.mysql.date.value.template='$VALUE'

CODE

For parsing the dates correctly, ConnectALL needs to know the correct format to be used to convert the application date when inserting or updating table records. For this purpose — ConnectALL has 2 property options to convert the date to a given format and to tell the database the format.

# Format to which application date needs to be converted to
MySQLJiraBug.mysql.java.datetime.format=yyyy/MM/dd HH:mm:ss
# Format to be sent to the database during insert and update
MySQLJiraBug.mysql.sql.datetime.format=yyyy/mm/dd hh24:mm:ss

CODE

Important

In the above-mentioned properties, the preference is given to MySQLJiraBug.mysql.java.datetime.format=yyyy/MM/dd HH:mm:ss. If it is not available in the DatabaseAdapter.properties file, then, ConnectALL scans for the mysql.java.datetime.format property for handling the date & time formatting. And last preference would be given for java.datetime.format property. Note that the MySQLJiraBug.mysql.java.datetime.format property is applicable only for the specific automation with the name "MySQLJiraBug". The mysql.java.datetime.format property is applicable for all the database automations with specific database type "mysql". The java.datetime.format property is common for all the database automations irrespective of the database type and the automation name.

Print pool statistics

When this value is set, the Connection Pool statistics will be printed in the log when acquiring and releasing a connection for a given connection key. The default value for this is 'false'.

print.pool.statistics=false

CODE

Use-cases

Dump the data from multiple ALM applications onto a database table for generating customized reports and dashboards.
Import the aggregated data into an application, using back-end processing techniques from other systems.

Assumptions

A table is already created based on the requirements in the preferred database (ConnectALL will not be performing any DDL operations on the tables).
The table should have an auto-incrementing column that will be referred to as an "ID" field in the database; this helps to identify the records based on this unique ID while updating the records.
The table should have a column to store the modification date of the record, and also this needs to be set on every modification using triggers or any similar feature of the database when using the sync option as 'Poll'.
The ID field and the Change Date field will not be updated by ConnectALL.
Use the special datatypes "LastUpdatedDt" and "ID" for configuring the ID and Change Date fields in the automation for the database.

Note

ConnectALL can read from a database and write to it. However, it cannot do the same in CLOB and BLOB datatypes.
Table relationships cannot be read by ConnectALL.
Comments, Attachments, and Issue Linking features of other adapters are not supported by the Database Adapter.
In an automation between the Database Adapter and Atlassian Jira, if you have set the datatype as unknown in any field, the sync may fail with the following error message: "’com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed'." In such a scenario, update the below-mentioned property by increasing the ConnectALL core service level count from 0 to 1 in the databaseAdapter.properties file against your respective database adapter type and restart the ConnectALL core service after you make this change.

If the Database Adapter Type Is	Then Update
MySQL	database.mysql.mule.maxIdle=1
MSSQL	database.mssql.mule.maxIdle=1
PostgreSQL	database.oracle.mule.maxIdle=1
Oracle	database.postgres.mule.maxIdle=1