Database Connector

Overview

DBSync's Database connector comes with support for many of the commonly used databases which are of JDBC compliant - like oracle, Mysql, DB2 etc. It is implemented in such way that any user, that uses database connector, can perform operations like, select, Insert, update and delete records, on databases. The Database connector contains connection properties of external database like credentials, endpoint URLs and Password. These properties are dynamic in nature i.e. for different databases the properties vary and are listed in connector configuration section.

A single connector instance, created in a project, can be used across different processes. However, the processes must belong to the same project.

Prerequisites

To establish a connection, to any of the desired database via DBSync database connector, a user must:

• Have a valid license to DBSync database connector, username and password to connect to DBSync iPaaS platform.

• Have a valid database username, password, host/IP address and port on which database is hosted.

• The username and password configured for Database connector instance should have sufficient privilege to access the database - which is required for integration requirement.

• The On-demand version of DBSync will not connect to databases hosted locally. It must fall within the network for connection to be successful.

Connector Configurations

A Database connector instance represents a single database connection. If a user wishes to connect to multiple instances of the different databases, create a separate connection for each and configure the URL accordingly. The following table represents parameters required for opening the connection to the database.

Property	Description	Required
Username	Database username.	Yes
Password	Database password.	Yes
Host	Host name or IP address of the database server.	Yes
Port	The port used to connect to database server.	Yes
Database	The name of your database.	Yes
Type	JDBC Driver for the Databases mentioned in the URL section in the same order. sqlserver oracle mysql postgresssql db2 access	Yes
Database URL	JDBC connection string SQLServer: jdbc:sqlserver://[hostname]:[port];databaseName=[databasename] Oracle: jdbc:oracle:thin:@[hostname]:[port]:[sid] mySQL: jdbc:mysql://[hostname]:[port]/[databasename] PostgreSQL: jdbc:postgresql://[host]:[port]/[database] DB2: jdbc:db2://[host]:[port]/[database] RedShift: jdbc:redshift://[host]:[port]/[data Access: jdbc:[databasename]	Yes

The below table lists additional properties that are required while writing/updating to a database. DBSync has an auto create feature. The feature is enabled when target connector is database so that it replicates the whole source schema.

Property	Description	Required	Default Value
Auto create	True if you want the table to be created automatically.	No	False
Table	Name of the table to write into	Yes	Empty
Write type	one of create, update or upsert operations	Yes	Empty
Prefix	The prefix excluding the '.' operator used for the field names	No	Empty
Pkey	Comma separated list of primary key column names	Required if update or upsert	True

The below table lists the advanced properties that are required while updating the source.

Property	Description	Required	Default Value
Table	Name of the table to write into	Yes	Empty
Prefix	The prefix excluding the '.' used for the field names	No	Empty
Pkey	Comma separated list of primary key column names	Required if Update or Upsert	True

Supported Databases

The Database connector supports all the database that are JDBC or ODBC compliant. Some of the supported databases are listed below:

• Access

• DB2

• MySql

• Oracle

• Pervasive Btrive

• PostgreSql

• Redshift

• Sqlserver

Frequently Asked Questions

Q: Does Database connector support Bi-directional data flow?

A: Yes. Furthermore, if you so choose, you can also make it uni-directional.

Q: How frequently one can sync the data if reading from, or writing to, any of the supported databases?

A: The sync can be performed at every five minutes time interval.

Q: Is this connector available with both, On-demand and On-premise, versions?

A: Yes. Database connector can be used with both, On-demand and On-premise, versions of DBSync Cloud Workflow.

Q: How can I try Database connector before purchasing the license?

A: We have a free trial for 15 days after going through the sign up process.

Q: Can both, source and target, act as database connectors?

A: Yes. If integration requirement demands, one can use the database connector to talk to different databases, or to the same databases, with different user instances.

Useful hint

Access 2013 videos and tutorials

DB2 basic tutorial

MySql

Oracle

Pervasive Btrive

PostgreSql

Redshift

Sqlserver