How can I Connect to a Database?

It is very easy to connect DataMinder to a database and you will start working with your data in under 5 minutes. All you need to do is to set up a connection. Select the "Default SQL connection" plugin and add it to your Runnables.

In Runnables put the connection in a Category and configure it to connect to your database e.g.

All database connections go through Java Database Connectivity (JDBC) which means you must get the correct driver for your database unless it is already provided.

The following drivers are already provided as part of the Data Plugins library (each as a JAR file):

1. MySql : mysql-connector-java-...-bin.jar
2. Microsoft SQL Server : sqljdbc4.jar

Each database has different connection URLs. Here are some examples. Please verify with your database.

1. MySql : jdbc:mysql://127.0.0.1:3306/YourDatabaseName
2. Microsoft SQL Server : jdbc:sqlserver://127.0.0.1:1433;databaseName=YourDatabaseName;

To connect to a database not provided here download the JAR file implementing the JDBC driver and put it in DataMinder/Server/Internal/DMPlugins/DMPluginData/lib folder and restart DataMinder.

Just go to Google and do a search for "jdbc driver" and the name of the database you are trying to connect to. JDBC drivers for most databases are available for download.

Back to top

Start working with the Database Connection

Now the you have your database connection you can test it by creating a process and add any of the SQL tasks e.g. "SQL Select" to verify you can get data from your database. Here we run the SQL Select in Debug View and load data into the DataMinder table i.e. the data table DataMinder Processes and Tasks work with internally in DataMinder. For more information please see DataMinder Help (also accessible from the main Support page).

Select data from Database

The most basic is to run a SELECT and retrieve data. You have 3 options.

The first is to have a Static SQL statement e.g. "SELECT FirstName, LastName , Email FROM UserDatabase.Users "

The second is to have a Dynamic SQL statement getting data from the DataMinder table and replacing all ${TableColumn} with the value from DataMinder table column e.g. "SELECT FirstName, LastName , Email FROM UserDatabase.Users WHERE EMAIL LIKE '${InEmail}' " selects only users having matching email as found in DataMinder table column "InEmail". This is done for each row containing data.

The third choice allows you to Generate any SQL statement you like. Either by someone calling the process or by one of the tasks in the process before running this task. You just set the "Value from table" flag meaning the value will be provided at runtime and found in column matching the parameter name "SQL statement".

Obviously there are security issues e.g. there must be access control and verification of what statements are allowed. But the options above provide powerful possibilities.

Insert data to and Modify data in Database

As with Select above we can crate: Static, Dynamic or Generated SQL statements. This gives you lot of possibilities to work with.

You could easily imagine bulk updates where SQL statements are generated from e.g. file data. We simply create a number of INSERT/UPDATE statements and run them one by one as a batch job.

Possible Use Cases

Now you can read data and modify data in the database. You can also set up new connections to more databases. This means that you can do a lot of things:

• Serve data from database to others in different formats e.g. via http/s calls, files
• Compare data between databases
• Create new data based on current data existing in multiple databases e.g. for future analysis or business intelligence
• Clean up data to maintain high data quality
• Bulk upload or updates of data

You can use current tasks or create new plugins to do whatever you like with the data.

Build your own plugins

If you need to build custom plugins to implement your use cases you can get started in 5 minutes. For more information how please see DataMinder Help Plugin Development (also accessible from the main Support page).

How to start Database Processes

There are 3 ways to start a Process

1. Manually e.g. when we do an import or sync just once
2. By Scheduler e.g. when we want the sync to start every hour
3. By external events like Service Calls e.g. Web Service calls

If you don't find a plugin to cover your needs e.g. no matching Scheduler, you can easily build your own. For more information please see Plugin Development in the Help.

Current Scheduler and Service/Listener plugins

Schedule Batch Jobs

A common task is to make sure data in two databases is the same. For example imagine a situation where you have the same user data in more then one database e.g. a Web Shop database and a internal Customer database. You may want to import new users from the Web Shop to the Customer database or update current data if users modify it.

To do that you would build a process managing the data and add a Scheduler to e.g. run the process every hour.

Respond to Web Service Calls

You may also want to e.g. update users when other systems send new data by Web Services.

Then you need to set up a process that listens to Web Services and starts when it receives a matching call e.g. with a user id and a new phone number.

Back to top