Connect to Data Lake Relational Engine Using the JDBC Driver

Ensure that you have a Java Development Kit (JDK) installed and make sure it is accessible from your path. Details of the driver and supported versions can be found at JDBC Drivers and Oracle Java SE Support Roadmap.

A few options include:

• Java JDK such as Java SE 11 (LTS)

• A SAP supported version of the OpenJDK

To verify that the JDK is correctly set up, run the following:

java -version
javac -version

If these commands fail, ensure that the folder they are located in is included in your path.

The following command will install Java on openSUSE Leap 15.2.

sudo zypper install java-11-openjdk-devel

The SAP IQ JDBC driver is a type 2 driver, which means it has a native (non-Java) component. For additional details see Type 2 driver – Native-API driver. The driver is located in %IQDIR17%\Java\sajdbc4.jar on Microsoft Windows and $IQDIR17/java/sajdbc4.jar on Linux. The native component is at %IQDIR17%\Bin64\dbjdbc17.dll on Microsoft Windows and $IQDIR17\lib64\libdbjdbc17.so on Linux.

A native JDBC driver called jConnect is also provided. This tutorial focuses on the SAP IQ JDBC driver.

See JDBC Drivers for additional details.

1. The following commands create a folder named java, enter the newly created directory, create a file named JavaQuery.java, and open the file in notepad.

   The HOMEPATH environment variable should resolve to your user in your users folder such as c:\users\dan. Its value can be seen on Microsoft Windows by entering echo %HOMEPATH% into a shell.

   Shell (Microsoft Windows)

   Copy

   mkdir %HOMEPATH%\DataLakeClientsTutorial\java
   cd %HOMEPATH%\DataLakeClientsTutorial\java
   notepad JavaQuery.java
   

   Shell (Linux)

   Copy

   mkdir -p ~/DataLakeClientsTutorial/java
   cd ~/DataLakeClientsTutorial/java
   pico JavaQuery.java
   

2. Copy the following code into JavaQuery.java:

   Java

   Copy

   import java.sql.*;
   public class JavaQuery {
       public static void main(String[] argv) {
           Connection connection = null;
           try {
               //Option 1
               connection = DriverManager.getConnection("jdbc:sqlanywhere:uid=USER1;pwd=Password1;Host=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXX.iq.hdl.trial-XXXX.hanacloud.ondemand.com:443;ENC='TLS{tls_type=rsa;direct=yes}'");
   
               //Option 2, the connection properties can be loaded from an ODBC datasource.
               //connection = DriverManager.getConnection("jdbc:sqlanywhere:DSN=HC_HDL_Trial;LOG=myLog.log");  
               //The LOG option is helpful when diagnosing connection issues.
           }
           catch (SQLException e) {
               System.err.println("Connection Failed:");
               System.err.println(e);
               return;
           }
           if (connection != null) {
               try {
                   System.out.println("Connection to data lake Relational Engine successful!");
                   Statement stmt = connection.createStatement();
                   ResultSet resultSet = stmt.executeQuery("SELECT TITLE, FIRSTNAME, NAME FROM HOTEL.CUSTOMER;");
                   while (resultSet.next()) {
                       String title = resultSet.getString(1);
                       String firstName = resultSet.getString(2);
                       String lastName = resultSet.getString(3);
                       System.out.println(title + " " + firstName + " " + lastName);
                   }
               }
               catch (SQLException e) {
                   System.err.println("Query failed!");
               }
           }
       }
   }
   

3. Update the host value in the connection string.

4. Compile the .java file into a .class file using the following command:

   Shell (Microsoft Windows)

   Copy

   javac -cp %IQDIR17%\Java\sajdbc4.jar;. JavaQuery.java
   

   Shell (Linux)

   Copy

   javac -cp $IQDIR17/java/sajdbc4.jar:. JavaQuery.java
   

5. Run JavaQuery.class and indicate where the JDBC driver is located.

   Shell (Microsoft Windows)

   Copy

   java -classpath %IQDIR17%\Java\sajdbc4.jar;. JavaQuery
   

   Shell (Linux)

   Copy

   java -classpath $IQDIR17/java/sajdbc4.jar:. JavaQuery
   

   

See JDBC Program Structure for additional details.

Visual Studio Code can run and debug a Java application. It is a lightweight but powerful source code editor available on Microsoft Windows, macOS, and Linux.

1. If required, Download Visual Studio Code.

2. In Visual Studio Code, choose File | Open Folder and then add the DataLakeClientsTutorial folder.

   

3. Open the file JavaQuery.java, and if asked, install the recommended extensions.

   

4. Once the Java Extension Pack has been installed, expand the Java Project Explorer and click on the + icon to add the JDBC driver as a referenced library.

   

   The JDBC driver is located at %IQDIR17%\Java\sajdbc4.jar on Microsoft Windows and $IQDIR17/java/sajdbc4.jar on Linux.

5. Place a breakpoint and then select Run | Start Debugging.

   Notice that the debug view becomes active.

   Notice that the program stops running at the breakpoint that was set. Step through the code by pressing F10 and observe the variable values in the variables pane.

   

DBeaver is a free and open source database tool and can be used with the data lake Relational Engine JDBC driver.

The following steps demonstrate how to configure DBeaver to connect to data lake Relational Engine.

1. Download and install the community edition of DBeaver.

   

2. In DBeaver, select Database | Driver Manager | New to create a new driver configuration.

   

3. Under the Libraries tab, specify Java and native components that make up the driver.

   

   Select sajdbc4.jar and choose Find Class and press OK to save the new driver definition.

   On Linux, if an error occurs indicating that the library cannot load, ensure that source IQ.sh has been added to your environment as described at Step 5: Install the data lake IQ client.

4. Select Database | New Database Connection | All and select the newly created driver definition.

   

5. Provide the JDBC URL and credentials and press the Test Connection button. An example of the URL is shown below.

   JDBC URL

   Copy

   jdbc:sqlanywhere:uid=HDLADMIN;pwd=MyPasword1;Host=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXX.iq.hdl.trial-XXXX.hanacloud.ondemand.com:443;ENC='TLS{tls_type=rsa;direct=yes}'
   

   

6. A SQL Console can now be opened and queries can be executed.

   SQL

   Copy

   SELECT TITLE, FIRSTNAME, NAME FROM CUSTOMER;
   

   

Congratulations! You have now created and debugged a Java application that connects to and queries a data lake Relational Engine database and used the driver in DBeaver.