Connect to OceanBase Database and retrieve data in FineReport

FineReport is a tool for report creation, analysis, and presentation. With FineReport, users can easily build flexible data analysis and reporting systems. This topic describes how to configure a connection to OceanBase Database in FineReport to enable data visualization and reporting. FineReport supports connecting to OceanBase Database through the JDBC driver and can convert data from OceanBase into rich reports and charts.

Version compatibility

FineReport 11.0 supports the following drivers for connecting to OceanBase Database:

• FineReport version: ≥ 11.0
• OB-JDBC version: ≥ 2.2.7.2
• MySQL-JDBC version: ≥ 5.1.47

Prerequisites

Before you use FineReport to connect to OceanBase Database, make sure that:

• You have installed FineReport 11.0 or later. For more information, see Download.
• You have deployed OceanBase Database and created a MySQL user tenant. For more information, see Create a tenant.
• You have obtained the connection information of OceanBase Database, including the host, port, username, password, and database name.
• You have downloaded the corresponding OceanBase JDBC driver file (oceanbase-client-.jar or mysql-connector-java-.jar).

Procedure

Step 1: Obtain the connection string of OceanBase Database

Contact the deployment personnel of OceanBase Database to obtain the connection string. For example:

obclient -h$host -P$port -u$user_name -p$password -D$database_name

Parameter description:

• $host: the IP address for connection. For connection through ODP, use the IP address of ODP. For direct connection, use the IP address of OBServer.

• $port: the port for connection. For connection through ODP, the default value is 2883. For direct connection, the default value is 2881.

• $database_name: the name of the database.

  Notice

  The user used to connect to the tenant must have the CREATE, INSERT, DROP, and SELECT privileges on the database. For more information about user privileges, see Privilege types in MySQL mode.

• $user_name: the username. For connection through ODP, the format is user@tenant#cluster or cluster:tenant:user. For direct connection, the format is user@tenant.

• $password: the password of the account.

For more information about the connection string, see Connect to an OceanBase tenant by using OBClient.

Example:

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

Step 2: Install FineReport

If FineReport is not installed on your local computer, install it.

1. Go to the FineReport download page: https://www.finereport.com/product.
2. Download the Windows installation package of FineReport 11.0.
3. Run the installation package and follow the wizard to complete the installation of FineReport.

Step 3: Configure the OceanBase JDBC driver

FineReport connects to OceanBase Database by using the JDBC driver. Copy the corresponding driver JAR package to the driver directory under the installation directory of FineReport, based on the type of driver you use.

• If you use OB-JDBC, you can download the driver file oceanbase-client-2.4.0.jar by running the following command:

  wget https://repo1.maven.org/maven2/com/oceanbase/oceanbase-client/2.4.0/oceanbase-client-2.4.0.jar
  

• If you use MySQL-JDBC, you can download the driver file mysql-connector-java-5.1.47.jar by running the following command:

  wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.47/mysql-connector-java-5.1.47.jar
  

Example of the location where you can place the driver file:

FineReport installation directory/
├── driver/
│   ├── oceanbase-client-2.4.0.jar
│   └── mysql-connector-java-5.1.47.jar
└── ...

If FineReport has been started, restart it after you place the driver file to make the driver configuration take effect.

Step 4: Configure an OceanBase data source in FineReport

1. Open FineReport: Start FineReport.

2. Go to the data connection configuration:

   • Click Server in the menu bar and choose Define Data Connection.
   • In the dialog box that appears, click +JDBC to add a new JDBC data source.

3. Configure the connection parameters:

   • Database: select JDBC.

   • Driver: enter com.alipay.oceanbase.jdbc.Driver = > com.oceanbase.jdbc.Driver (if you use the OB-JDBC driver).

   • URL: enter the connection URL of OceanBase Database in the jdbc:oceanbase://$host:$port/$database_name format.

     • Replace $host with the IP address of the OceanBase server.
     • Replace $port with the port number of the OceanBase server.
     • Replace $database_name with the name of the database to be connected.

   • Username: enter the username of OceanBase Database.

   • Password: enter the password of OceanBase Database.

   • Encoding: select UTF-8.

4. Test Connection:

   • Click Test Connection to verify whether the configuration is correct.
   • If the connection is successful, the system displays a prompt indicating that the connection is successful.

5. Save the configuration:

   • Click OK to save the data source configuration.

Verify the result

After you complete the preceding steps, you can verify whether the OceanBase data source is properly connected in FineReport:

1. Test the connection:

   • In the data source configuration page, click Test Connection. If the connection is successful, "Connection succeeded" is displayed.
   • If the username or password is incorrect, an error message is displayed.

2. View data:

   • In the dataset management page of FineReport, you can view tables and data in the OceanBase database.
   • You can execute SQL queries and obtain query results.

3. Create reports:

   • Create a report template based on the OceanBase data source.
   • Drag fields from the OceanBase data table to the report design area.
   • Preview the report to confirm that the data is displayed properly.

The verification result indicates that the OceanBase database runs properly in FineReport. You can connect to the database, query data, and create reports.

FAQs

Q1: I see the "Driver not found" error when I connect to the OceanBase database.

Cause: The OceanBase JDBC driver file is not properly placed in the driver folder of FineReport, or the driver file is incompatible with the current version of FineReport.

Solution:

• Make sure that the correct oceanbase-client-*.jar or mysql-connector-java-*.jar file is copied to the driver folder of the installation directory of FineReport.
• Check whether the driver file is correctly named and undamaged.
• Try using a different version of the JDBC driver. For example, you can switch from OB-JDBC 1.1.10.4 to the latest or a higher version.

Q2: What is the URL format for connecting to the OceanBase database?

Cause: URL format errors are common connection issues.

Solution:

• Make sure that the URL format is jdbc:oceanbase://Host IP:Port/Database name.
• If you connect to the OceanBase database through ODP, the port is usually 2883. If you directly connect to the OBServer, the port is usually 2881.
• Make sure that the host IP address and port number are correct.

Q3: Does FineReport support the Oracle mode of OceanBase Database?

Cause: According to the verification result, the current version of FineReport and the driver support the Oracle mode of OceanBase Database to a limited extent.

Solution:

• According to the verification result, OB-JDBC 1.1.10.4 is unavailable in the Oracle mode.
• We recommend that you use a higher version of OB-JDBC to connect to the Oracle mode of OceanBase Database.