How To - Establish a Database Connection (JDBC) in MashZone 2.0

A new feature from MashZone 2.0 allows the use of new data sources. The JAVA Database Connectivity (JDBC) plays an important role here. It allows users to establish connections to different database models. However it should be noted, that the JDBC isn't included in delivery with MashZone. Where you can get it and how you install it, will be described later.

In this post I will show you how to establish a connection with the help of an Oracle Database and output the data records of  demo tables.

First you must decide which database to use. As mentioned above, I decided to use an Oracle Database in the Express Edition, because it’s popular and has a demo data record. To use it, you must configure certain allocations. This How To assumes that the database user “HR” is unlocked and otherwise the JDBC Driver of Oracle is copied in the MashZone folder. This can be done in the following manner:

Open the Adminstration interface of Oracle http://127.0.0.1:8080/apex/ and log in with “system” and the password you specified in the setup. Afterwards, switch to the user administration, unlock “HR” and change the password.

Now you need the JDBC Driver so that MashZone can establish a connection to the database. This is the default setup folder, which was automatically selected during setup:

C:\oraclexe\app\oracle\product\10.2.0\server\jdbc\lib

Copy  the file “ojdbc24.jar” and paste it accordingly in the subfolder “jdbcdrivers” of MashZone 2.0.

Now, to set up the database, reboot the MashZone server, switch to the "Administration" tab and select the new item "Database Connection". Once there click on "Create" and enter the following values:

Driver: oracle.jdbc.driver.OracleDriver

Database URL: jdbc:oracle:thin:@localhost:1521:XE

When your input is saved, click the button "Test database connection" to test the settings you entered. Now you are asked to specify the user data for the test connection.

If your login was successful the following message appears: "Connection test with database ‚Oracle Database‘ successful."

Now switch to the Feed editor, create a new data feed and select "Database" as the data source. The following shows a window that displays the database you just created.

Now select this database and confirm it with „OK“. As user enter the user “HR” which was already tested above. For this post, I invented a connection between the employees and their departments as an SQL statement. Due to this, the IDs between “Employees” and “Departments” were compared: „SELECT First_Name, Last_Name, Department_Name FROM Employees, Departments WHERE Employees.Department_ID = Departments.Department_ID“

If you want to experiment with your own instructions, there are several programs that can help you:

SQuirrel- Eclipse- DBVisualizer

A detail of the results can be seen in the following table:

Congratulations on your first database connection in MashZone 2.0!

Note: See this post for a list of other articles about MashZone 2.0. You might be also interested to join the ARIS MashZone group at ARIS Community.