Database
Last updated
Last updated
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.
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.
The following parameters are required to configure the Database Connector:
Navigate to the DBSync website and click on Login.
Choose Cloud Workflow from the options.
Provide your User Name and Password and click Login.
Once logged in, click on Create to start a new project. Name your project (e.g., Database Integration Project).
Click on Add Task and name it (e.g., 'Database Operations Task').
Assign the task to a Bot and click Configure.
In the task configuration screen, look for the option to add connectors. Select Add Connector and choose Database Connector from the list.
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.
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.
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).
The below table lists the advanced properties that are required while updating the source.
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
Click Save to store your configuration.
Click the Run icon to execute the workflow. Monitor the console output for any errors or confirmation messages regarding the database operations.
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.
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.
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.
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
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