Connect Apache Derby Database using JDBC-ODBC Bridge in Power BI, Excel, MSAccess, Informatica

[nayan]

Introduction

In this article we will see how to connect Apache Derby Database and integrate in non-Java / ODBC apps like Excel, Power BI, Informatica and many more.

Prerequisite

1. Download official Java8 JDBC driver for Apache Derby
   from here. Other versions might work too but we have tested with Java8 + ZappySys JDBC-ODBC Bridge combination (Assuming Java 8 is installed on the OS). Match driver with your Java version if possible.
2. Download and Install ZappySys ODBC PowerPack .
3. Refer to Video On this page for Basic Instructions on Java Install / Driver Setup

Configure Derby JDBC-ODBC Connection (DSN)

1. Open ODBC Data Sources (x64):
   

2. If you want access for yourself then stay of User DSN Tab. If you want grant access other users then go to the System DSN tab. For SQL Server Integration go to System Tab and add new System DSN rather than User DSN. Click New button.
   

3. From the Driver list Select ZappySys ODBC Driver. For this example select [ZappySys JDBC Bridge Driver].
   

4. Enter the following values on JDBC Bridge UI
   Connection String:
   For Local Database (Embedded Mode):
   Syntax: jdbc:derby:{database-file-path}
   Example: jdbc:derby:c:\Apache\derby\databses\toursdb
   For Remote Database (Network Mode):
   Syntax: jdbc:derby://localhost:1527//{database-relative-path}
   Example: jdbc:derby://localhost:1527//somefolder/somedb

5. JDBC Driver File(s):
   For Local Database (Embedded Mode):
   c:\Apache\derby\lib\derby.jar
   For Network Mode:
   If DB running on Remote server (network mode) need to choose below file
   c:\Apache\derby\lib\derbyclient.jar

6. Username/password: <optional and may not be needed>
   

   

7. Click Test Connection see it works?

8. Go to Preview Tab to query some data (Enter a simple query and click Preview Data button
   

Embedded (DB on Local PC) vs Network Mode (DB on Remote PC)

If you are connecting to Local Derby Database (embedded mode) then refer this doc

If you are connecting to Remote Derby Database (Network mode) then refer this doc

Load Apache Derby data into Apps like Excel / Power BI

Once DSN is created you can now use DSN in Excel / Power BI. Click here to learn how to connect JDBC Driver in PowerBI

Load Multiple Tables in Power BI

Load Query in Power BI

If you like to import data using custom query then during load expand following textbox and enter your Apache Derby query.

Connect JDBC Driver in Other Non-Java / ODBC Apps

So far we saw how to connect ZappySys JDBC Bridge Driver in Power BI / Excel but if you want to see instructions for more Apps click here

Conclusion

Following these steps, you can efficiently connect Apache Derby Database using ZappySys JDBC Bridge Driver in any Non-Java / ODBC Apps (e.g. Excel, Power BI, MS Access), enabling smooth data integration for reporting and analysis. If you encounter any issues or need further assistance, please get in touch with the ZappySys support team.