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:
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:
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“
A detail of the results can be seen in the following table:
Congratulations on your first database connection in MashZone 2.0!