Knime with database

Question

How to add new driver into database through KNIME preferences? Generally,

File-> Preferences -> Add File/ Add Directory

The files accepted are only of *.jar or *.zip.

MY QUESTION

I have installed ODBC64 into my PC. Now I need to add that file into knime preferences and use the Driver into Database Connector node.

How to add and use the file into my Knime?
And What is meant by Database URL jdbc:mysql://host:port/database_name
Host and port?

Can anyone please briefly explain and help me out?

(I doubt the URL for an MS SQL Server would contain the string `mysql` in the protocol part.) — Gábor Bakos, Oct 17 '17 at 05:14

score 0 · Answer 1 · answered Oct 12 '17 at 10:12

I'm assuming based on your database URL of jdbc:mysql:// that you are wanting to connect to a MySQL database? Based on that, then there is a thread on the KNIME forum which explains pretty much all of your question, but the process is the same for any other sort of database. The steps are as follows:

Download the jdbc driver (e.g. from https://dev.mysql.com/downloads/connector/j/ for MySQL) - NB KNIME now comes bundled with several drivers already installed - MySQL is one of those - in the Database Connector node the drivers installed are listed.
In the database URL, you need to change those parts in <> - i.e. the hostname, port number and database name. Hostname may be localhost if it is a local database. The port number you will need to find from your database administrator, or will be what you set it up to be if you are running a local database (3306 is the default for MySQL), so for a database called 'myDB' on the default port on your local machine, the url should be jdbc:mysql://localhost:3306/myDB

For some of the shipped drivers, there are also connector nodes, e.g. MySQL Connector, SQLite Connector, PostgreSQL Connector etc, which still require the server name/port and database name, but take them as individual inputs rather than requiring editing of the URL

score 0 · Answer 2 · answered Oct 12 '17 at 17:46

0

Recent versions of KNIME are based on Java 8, which dropped support for ODBC, so you should first find an alternative driver for your database and only after you can connect to that with KNIME as described on the KNIME documentation page for DB connectors.

answered Oct 12 '17 at 17:46

Gábor Bakos

8,982
52
35
52

Thank you @SteveR. But found that, as KNIME was built with Eclipse which is java based mostly the drivers are supported by only JDBC. Is it true? – Uma Ramya Oct 13 '17 at 05:25
@UmaRamya I think only jdbc, yes – SteveR Oct 16 '17 at 21:45
@SteveR Thankyou! :) – Uma Ramya Oct 18 '17 at 05:38
@Gabor Bakos Does nodes in knime support for auto update (get the updated data from database for every 5 hours) ? Is any nodes are available in knime? – Uma Ramya Nov 14 '17 at 06:27

score 0 · Answer 3 · answered Nov 30 '18 at 11:27

You have several nodes which allow you to connect to a DB (especially MySQL). I remember there was a dedicated MySQL node for connecting with the DB. Just remember this: you have to input the IP adress : port, then insert credentials and point to the DB you want to open by default.

Knime with database

3 Answers3