Database Adapter


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. 

Capabilities

  • 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


Configuration

Create an application link 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, 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.

         

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 as shown below, and set the database fields value types' to Id and lastUpdatedDt respectively:


For a database table to be compatible with the Database Adapter, an auto-incrementing column to be mapped to the Id data type, and a column that will update for every alteration to be mapped to the lastUpdatedDt data type. Below is an example file containing compatible tables for multiple database types.

Sample Database Scripts

Click the cogwheel icon to select the data type for the selected fields. 


Advanced Configuration

Flow Filter

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, while 'Poll' is the most commonly used option for databases. The "Change Date" field should be mapped as defined in the "Field Mapping" section; when using POLL sync as ConnectALL will query the modified records based on this field. 

Connection Pooling

The first thing that comes to mind when using the Database Adapter is how can we manage the JDBC connections that are being created. ConnectALL manages these connections by pooling the connection objects, and the same can be configured per application link in the "DatabaseAdapter.Properties" file under the "$MULE_HOME/conf" directory. 

Example 

# Connection Pooling configuration for MySQLJiraBug application link

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

# Maximum active connections

database.MySQLJiraBug.maxActive=4

# Maximum idle connections

database.MySQLJiraBug.maxIdle=1

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

Any changes to the Connection Pooling Profile requires a restart of the Tomcat and Mule services.

Database Specific Customizations

The Database Adapter provides a bundle of customizations for configuring based on the requirements. All of these properties are set in the "DatabaseAdapter.properties" file in the "$MULE_HOME/conf" directory.

In each of the below code block/templates examples, ‘MySQLJiraBug’ is the name of the app-link (the name of the app-link 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).


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

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 the Mule and Tomcat services.

Databases will have different namespaces such as catalogs, schemas, instances, and databases to locate a database table. ConnectALL uses the fully qualified name of the 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. The separator can be configured if needed using the below property.

MySQLJiraBug.mysql.catalog.schema.separator=.

To insert new records into a table, ConnectALL uses this template to create the insert statement, where $TABLE is a placeholder for 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)

To update existing records in a table, ConnectALL uses this template to create the update statement, where $TABLE is a placeholder for 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

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

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

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

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

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

The formatting of the values might be different for each data type across databases. Datatype formatting templates help to customize 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'

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

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=$VALU*E in the DatabaseAdapters.properties file.

The formatting of the values might be different for each datatype across databases, datatype formatting templates help to customize these differences. 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'

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

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

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 application link for the database.

Limitations


  • 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 app-link 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 Mule level count from 0 to 1 in the databaseAdapter.properties file against your respective database adapter type and restart the Mule service after you make this change. 
If the Database Adapter Type IsThen 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