Connect to OceanBase Database and retrieve data in SAP Crystal Reports

SAP Crystal Reports is a reporting tool developed by SAP. It allows you to create and deliver multi-page reports based on almost any data source, including databases, enterprise systems, and cloud services. This topic describes how to connect to OceanBase Database by using an ODBC driver and generate reports in Crystal Reports. Crystal Reports supports connecting to OceanBase Database MySQL tenants and Oracle tenants, but the connection methods for the two types of tenants are slightly different.

Version compatibility

• OceanBase Database version: ≥ V4.2.5 (tested in V4.2.5)
• SAP Crystal Reports version: SAP Crystal Reports 2020 (64-bit)

Limitations

• You can use Crystal Reports to directly select tables from an OceanBase MySQL tenant by using the OceanBase ODBC driver.
• You can use Crystal Reports to read data from an OceanBase Oracle tenant by using the OceanBase ODBC driver and SQL commands.

Prerequisites

Before you use SAP Crystal Reports, make sure that:

• You have deployed OceanBase Database and created a MySQL tenant. For more information, see Create a tenant.
• You have installed SAP Crystal Reports 2020 (64-bit).
• You have installed the OceanBase ODBC driver.
• Your operating system is Windows. Crystal Reports is available only for Windows and not for macOS.

Procedure

Step 1: Obtain the connection string of OceanBase Database

Contact the OceanBase Database deployment personnel to obtain the connection string, for example:

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

Parameter description:

• $host: the connection IP address. For ODP connections, use the ODP address. For direct connections, use the OBServer IP address.

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

• $database_name: the database name.

  Notice

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

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

• $password: the account password.

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 Crystal Reports and the OceanBase ODBC driver

1. Go to the SAP Crystal Reports website (https://www.sap.cn/products/technology-platform/crystal-reports.html), click Try for Free, register, and then you can use it for 30 days. The product key will be sent to the email address you provided during registration.
2. Download the Crystal Reports installation program, double-click the exe file, and follow the wizard to complete the installation.
3. Go to the OceanBase Software Center (https://en.oceanbase.com/softwarecenter-cloud), select the Win64 version of the OceanBase ODBC driver, and click the Download button.
4. Double-click the downloaded msi file and follow the wizard to complete the installation of the OceanBase ODBC driver.

Step 3: Set up the ODBC data source

Set up the ODBC data source for an OceanBase MySQL tenant

1. In the Windows search box, search for ODBC Data Source (64-bit) and open it.

2. Select the System DSN tab and click Add.

3. In the driver list, select OceanBase Driver and click Complete.

4. Enter the data source name and description, and click Next.

5. Select the TCP/IP protocol and enter the following connection information:

   • Server Name: $host (the IP address of the OceanBase server)
   • Port: $port (default value is 2881)
   • User name: the username for logging in to the database.
   • Password: the password for logging in to the database.

6. Click Test DSN to test the connection. If the connection is successful, the configuration is correct.

7. Follow the wizard to complete the remaining configurations and click Finish to create the data source.

Set up the ODBC data source for an OceanBase Oracle tenant

1. In the Windows search box, search for ODBC Data Source (64-bit) and open it.

2. Select the System DSN tab and click Add.

3. In the driver list, select OceanBase ODBC 2.0 Driver.

4. Enter the data source name and description.

5. Enter the following connection information:

   • Server Name: $host (the IP address of the OceanBase server)
   • Port: 1521 (the default port for an Oracle tenant)
   • User name: the username for logging in to the database.
   • Password: the password for logging in to the database.

6. Click Test DSN to test the connection. If the connection is successful, the configuration is correct.

7. Follow the wizard to complete the remaining configurations and click Finish to create the data source.

Step 4: Connect to OceanBase Database in Crystal Reports to generate reports

Generate reports by connecting to an OceanBase MySQL tenant

1. Open Crystal Reports and click Report Wizard.
2. In the data source selection interface, click ODBC.
3. Select the previously created OceanBase MySQL tenant data source and click Next.
4. Enter the database username and password, and click Next.
5. In the table selection interface, select the table to generate the report (such as the t1 table) and click Next.
6. Select the fields to display (such as id and name) and click Next.
7. Follow the wizard to complete the remaining configurations and click Finish to import the data into the report.

Generate reports by connecting to an OceanBase Oracle tenant

1. Open Crystal Reports and click Report Wizard.
2. In the data source selection interface, click ODBC.
3. Select the previously created OceanBase Oracle tenant data source and click Next.
4. Enter the database username and password and click Finish.
5. In the report design interface, double-click Add Command.
6. In the command window that appears, enter an SQL query command, such as SELECT * FROM t1, and click OK.
7. Click Next and select the fields to display.
8. Follow the wizard to complete the remaining configurations and click Finish to display the data in the report.

Verify the results

After completing the above steps, you can see the data imported from OceanBase Database in Crystal Reports. For MySQL tenants, the data can be directly imported by selecting the table. For Oracle tenants, the data must be imported using SQL commands. Verify that the data in the report is correctly displayed to ensure that the connection and configuration are successful.

Frequently asked questions

Q1: Why can't I directly select tables when connecting Crystal Reports to an OceanBase Oracle tenant?

Cause: The support for Oracle tenants in OceanBase ODBC drivers differs from that for MySQL tenants, requiring data to be retrieved via SQL commands.

Solution: Follow the steps in the "Generate a report by connecting to an OceanBase Oracle tenant" section of this topic and use the "Add Command" feature to input an SQL query statement to obtain data.

Q2: Does Crystal Reports support macOS?

Cause: SAP Crystal Reports only has a Windows version and no macOS version is available.

Solution: You need to install and use Crystal Reports on a Windows operating system.

Q3: What should I do if the connection test fails?

Cause: The failure may be due to incorrect connection parameters, network issues, insufficient permissions, or the OceanBase service not being started.

Solution:

1. Check if the connection parameters (Server Name, Port, User name, Password) are correct.
2. Confirm that the network can access the OceanBase server.
3. Verify that the user has the necessary database permissions.
4. Ensure that the OceanBase database service is running normally.