Database

Overview

DBSync's Database Connector provides support for various JDBC-compliant databases, including Oracle, MySQL, DB2, and more. This connector enables users to perform operations such as selecting, inserting, updating, and deleting records in databases. A single connector instance can be reused across different processes within the same project.

Prerequisites

To establish a connection to a database via the DBSync Database Connector, a user must:

• Have a valid license for the DBSync Database Connector, along with a username and password to connect to the DBSync iPaaS platform.

• Have valid database credentials, including username, password, host/IP address, and port.

• Ensure that the username and password used have sufficient privileges to access the database.

• Note that the on-demand version of DBSync cannot connect to databases hosted locally; the database must be accessible over the network.

Connector Configuration

The following parameters are required to configure the Database Connector:

Property	Description	Required	Default Values
Username	The username for the database.	Yes
Password	The password for the database.	Yes
Host	The hostname or IP address of the database server.	Yes
Port	The port number used to connect to the database server.	Yes
Database	The name of the database to connect to.	Yes
Type	JDBC Driver for the database (e.g., sqlserver, oracle, mysql, etc.).	Yes
Database URL	JDBC connection string (e.g., jdbc:mysql://[hostname]:[port]/[databasename]).	Yes

Steps to Use the Database Connector

Login to DBSync Cloud Workflow

1. Navigate to the DBSync website and click on Login.

2. Choose Cloud Workflow from the options.

Enter Login Credentials

3. Provide your User Name and Password and click Login.

Create a New Project

4. Once logged in, click on Create to start a new project. Name your project (e.g., Database Integration Project).

Add a Task

5. Click on Add Task and name it (e.g., 'Database Operations Task').

6. Assign the task to a Bot and click Configure.

Add the Database Connector

7. In the task configuration screen, look for the option to add connectors. Select Add Connector and choose Database Connector from the list.

Configure the Database Connector

8. Enter the required configuration details:

• Username: Your database username.

• Password: Your database password.

• Host: The database server's hostname or IP address.

• Port: The port number for the database connection.

• Database: The name of the database.

• Type: Select the appropriate JDBC driver for your database.

• Database URL: Enter the JDBC connection string.

Define Operations

9. Choose the operations you want to perform, such as:

• Select: Retrieve data from the database.

• Insert: Add new records to the database.

• Update: Modify existing records.

• Delete: Remove records from the database.

Set Additional Properties (Optional)

10. Configure additional properties such as:

• Auto Create: Set to true if you want the table to be created automatically.

• Table: Specify the name of the table to write into.

• Write Type: Choose between create, update, or upsert operations.

• Prefix: The prefix excluding the '.' operator used for the field names.

• Pkey: Comma separated list of primary key column names (required if update or upsert).

Advanced Properties for Updating the Source

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).	Required if Update or Upsert	True

Supported Databases

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

• Access

• DB2

• MySQL

• Oracle

• Pervasive Btrieve

• PostgreSQL

• Redshift

• SQL Server

Save and Run the Workflow

11. Click Save to store your configuration.

12. Click the Run icon to execute the workflow. Monitor the console output for any errors or confirmation messages regarding the database operations.

Review Logs

13. After running the integration, check the logs to verify that the operations were successfully executed. This will help you identify any issues that may have occurred during the process.

Uses of the Database Connector

• Data Synchronization: Automate the synchronization of data between different databases.

• Data Migration: Facilitate the migration of data from one database to another.

• Data Backup: Create backups of critical data by exporting it to another database.

PRO TIP

Always ensure your database credentials have the necessary privileges to perform the desired operations to avoid permission-related issues during execution.

Frequently Asked Questions (FAQ)

1. Does the Database Connector support bi-directional data flow?

Yes, the Database Connector can be configured for both bi-directional and uni-directional data flows.

2. How frequently can data be synced with the supported databases?

Data synchronization can be performed at intervals as frequent as every five minutes.

3. Is this connector available in both on-demand and on-premise versions?

Yes, the Database Connector can be used with both on-demand and on-premise versions of DBSync Cloud Workflow.

4. How can I try the Database Connector before purchasing a license?

DBSync offers a 15-day free trial after completing the signup process.

5. Can both source and target act as Database Connectors?

Yes, you can use the Database Connector to communicate with different databases or the same database with different user instances.

Related Content

• DBSync SQL Server Integration: Guide to configure SQL Server with DBSync Cloud Workflow.

• Automating Data Integration with DBSync: Learn how to set up automated data integration using DBSync.

• Database to Database Integration: Discover how to integrate data between different databases seamlessly.

• Troubleshooting Database Connector Issues: Common issues and solutions for Database Connector problems using DBSync.