Apache Derby Connector for SAP Crystal Reports

Apache Derby Connector enables you to connect to Apache Derby, a lightweight, open-source relational database management system (RDBMS).

In this article you will learn how to quickly and efficiently integrate Apache Derby data in SAP Crystal Reports without coding. We will use high-performance Apache Derby Connector to easily connect to Apache Derby and then access the data inside SAP Crystal Reports.

Let's follow the steps below to see how we can accomplish that!

Download Documentation

Prerequisites

Before we begin, make sure you meet the following prerequisite:

If you already have a JRE installed, you can try using it too. However, if you experience any issues, we recommend using one of the distributions mentioned above (you can install an additional JRE next to the existing one; just don't forget to configure the default Java in the Windows Environment Variables).

Download Apache Derby JDBC driver

To connect to Apache Derby in SAP Crystal Reports, you will have to download JDBC driver for it, which we will use in later steps. Let's perform these little steps right away:

  1. Visit Apache Derby website.
  2. Follow the instructions there, download the JDBC drivers, and save them locally.
    It is recommended to use JDBC driver compiled for Java 8, if possible.
  3. Done! That was easy, wasn't it? Let's proceed to the next step.

Create ODBC Data Source (DSN) based on ZappySys JDBC Bridge Driver

Step-by-step instructions

To get data from Apache Derby using SAP Crystal Reports we first need to create a DSN (Data Source) which will access data from Apache Derby. We will later be able to read data using SAP Crystal Reports. Perform these steps:

  1. Download and install ODBC PowerPack.

  2. Open ODBC Data Sources (x64):

    Open ODBC Data Source
  3. Create a User data source (User DSN) based on ZappySys JDBC Bridge Driver

    ZappySys JDBC Bridge Driver
    Create new User DSN for ZappySys JDBC Bridge Driver
    • Create and use User DSN if the client application is run under a User Account. This is an ideal option in design-time, when developing a solution, e.g. in Visual Studio 2019. Use it for both type of applications - 64-bit and 32-bit.
    • Create and use System DSN if the client application is launched under a System Account, e.g. as a Windows Service. Usually, this is an ideal option to use in a production environment. Use ODBC Data Source Administrator (32-bit), instead of 64-bit version, if Windows Service is a 32-bit application.
  4. Now, we need to configure the JDBC connection in the new ODBC data source. Simply enter the Connection string, credentials, configure other settings, and then click Test Connection button to test the connection:

    ApacheDerbyDSN
    jdbc:derby://hostname:1527/mydatabase
    org.apache.derby.jdbc.ClientDriver
    D:\Apache\derby\lib\derbyclient.jar
    admin
    **************
    []
    JDBC-ODBC Bridge driver data source settings

    For Client/server Environment, use these values when setting parameters:

    • Connection string: jdbc:derby://hostname:1527/mydatabase
    • Driver class: org.apache.derby.jdbc.ClientDriver
    • JDBC driver file(s): D:\Apache\derby\lib\derbyclient.jar
    • User name: admin
    • User password: **************
    • Connection parameters: []

    For Embedded Environment, use these values when setting parameters:

    • Connection string: jdbc:derby:c:\apache\derby\databases\mydatabase
    • Driver class: org.apache.derby.jdbc.EmbeddedDriver
    • JDBC driver file(s): D:\Apache\derby\lib\derby.jar

  5. You should see a message saying that connection test is successful:

    ODBC connection test is successful

    Otherwise, if you are getting an error, check out our Community for troubleshooting tips.

  6. We are at the point where we can preview a SQL query. For more SQL query examples visit JDBC Bridge documentation:

    ApacheDerbyDSN
    SELECT * FROM "APP"."ORDERS"
    JDBC ODBC Bridge data source preview
    SELECT * FROM "APP"."ORDERS"
    You can also click on the <Select Table> dropdown and select a table from the list.

    The ZappySys JDBC Bridge Driver acts as a transparent intermediary, passing SQL queries directly to the Trino JDBC driver, which then handles the query execution. This means the Bridge Driver simply relays the SQL query without altering it.

    Some JDBC drivers don't support INSERT/UPDATE/DELETE statements, so you may get an error saying "action is not supported" or a similar one. Please, be aware, this is not the limitation of ZappySys JDBC Bridge Driver, but is a limitation of the specific JDBC driver you are using.

  7. Click OK to finish creating the data source.

Video Tutorial

Read data in SAP Crystal Reports from the ODBC data source

  1. First of all, open SAP Crystal Reports and create the new Crystal Report.
    Create New Crystal Report

  2. And it will open the new data source selection window. Under ODBC(RDO) double click on the Make New Connection and Select the desired ODBC DSN, in our case we need to select ApacheDerbyDSN which we created in upper section. And Click on Next.

    ApacheDerbyDSN
    SAP Crystal Report - Select ODBC ApacheDerbyDSN DSN

  3. Expand the Connection and under the data select the desired table(s) or view(s) and click Add > button and click on Next.

    ApacheDerbyDSN
    ApacheDerbyDSN
    SAP Crystal Report - Add ODBC ApacheDerbyDSN DSN Tables

  4. Add the desired Fields to Display in the Reports. Here we are adding all fields and click on Finish.
    SAP Crystal Report - Add Display Fields

  5. That's it and you will be able to load the data in the Report.
    SAP Crystal Report - ApacheDerbyDSN Data Output

Using ZappySys Driver Custom Views and Stored Procedure in SAP Crystal Report

You can create parameterized Stored Procedure and Virtual Tables on the same Data Source (ODBC DSN or Data Gateway Data Source). Please refer to this article for the same. You can create procedures to encapsulate custom logic and then only pass handful parameters rather than long SQL to execute your API call.

Using Custom Views in the SAP Crystal Report

After establishing the connection with ApacheDerbyDSN Data Source you can use the custom view in SQL Crystal Report.
ApacheDerbyDSN
ApacheDerbyDSN
SAP Crystal Report - Custom Views

Using Custom Stored Procedure in the SAP Crystal Report

  1. First of all, create the custom stored procedure in the ZappySys Driver. Please refer to this article to create the custom stored procedure.
    ZappySys Driver Custom Stored Procedure
  2. After establishing the connection with ApacheDerbyDSN Data Source you can use the custom Stored Procedure in SQL Crystal Report. Same way you can write custom queries also in the query box.
    ApacheDerbyDSN
    SAP Crystal Report - Custom Views

Conclusion

In this article we showed you how to connect to Apache Derby in SAP Crystal Reports and integrate data without any coding, saving you time and effort. It's worth noting that ZappySys JDBC Bridge Driver allows you to connect not only to Apache Derby, but to any Java application that supports JDBC (just use a different JDBC driver and configure it appropriately).

We encourage you to download Apache Derby Connector for SAP Crystal Reports and see how easy it is to use it for yourself or your team.

If you have any questions, feel free to contact ZappySys support team. You can also open a live chat immediately by clicking on the chat icon below.

Download Apache Derby Connector for SAP Crystal Reports Documentation

More integrations

Other connectors for SAP Crystal Reports

All
Big Data & NoSQL
Database
CRM & ERP
Marketing
Collaboration
Cloud Storage
Reporting
Commerce
API & Files

Other application integration scenarios for Apache Derby

All
Data Integration
Database
BI & Reporting
Productivity
Programming Languages
Automation & Scripting
ODBC applications

  • How to connect Apache Derby in SAP Crystal Reports?

  • How to get Apache Derby data in SAP Crystal Reports?

  • How to read Apache Derby data in SAP Crystal Reports?

  • How to load Apache Derby data in SAP Crystal Reports?

  • How to import Apache Derby data in SAP Crystal Reports?

  • How to pull Apache Derby data in SAP Crystal Reports?

  • How to push data to Apache Derby in SAP Crystal Reports?

  • How to write data to Apache Derby in SAP Crystal Reports?

  • How to POST data to Apache Derby in SAP Crystal Reports?

  • Call Apache Derby API in SAP Crystal Reports

  • Consume Apache Derby API in SAP Crystal Reports

  • Apache Derby SAP Crystal Reports Automate

  • Apache Derby SAP Crystal Reports Integration

  • Integration Apache Derby in SAP Crystal Reports

  • Consume real-time Apache Derby data in SAP Crystal Reports

  • Consume real-time Apache Derby API data in SAP Crystal Reports

  • Apache Derby ODBC Driver | ODBC Driver for Apache Derby | ODBC Apache Derby Driver | SSIS Apache Derby Source | SSIS Apache Derby Destination

  • Connect Apache Derby in SAP Crystal Reports

  • Load Apache Derby in SAP Crystal Reports

  • Load Apache Derby data in SAP Crystal Reports

  • Read Apache Derby data in SAP Crystal Reports

  • Apache Derby API Call in SAP Crystal Reports