Skip to main content

OpenDB Usage Examples

Michael Duller
  • Updated

Introduction

This guide provides instructions on connecting to OpenDB through various methods. It includes details on using JDBC clients, with a focus on DBeaver for specific examples. Additionally, the guide covers how to establish a connection via a Java application, demonstrating direct integration in a programming environment. For those interested in using PolySQL+, this guide also includes steps to connect using this client. After establishing a successful connection to OpenDB, users can submit queries, examples of which can be found in the file /home/rocky/samples/samples_test.sql on the OpenDB VM.

 

Connecting to OpenDB in Oracle Mode via DBeaver

Prerequisites

  • DBeaver installed on your system.
  • Internet access to download the Datometry JDBC driver.

Step-by-Step Guide

1. Download the Datometry JDBC Driver

Download the latest Datometry JDBC driver from https://downloads.datometry.com/jdbc.

2. Set Up an Oracle Connection in DBeaver

  • Open DBeaver and create a new Oracle connection.

3. Configure Driver Settings

  • In DBeaver, navigate to the "Driver Settings" for the new Oracle connection.
  • Go to the "Libraries" tab.
  • Remove all existing Oracle libraries.
  • Add the downloaded Datometry JDBC driver.
  • Click on “Find Class” to verify that DBeaver locates the main class (com.datometry.DtmDriver) successfully.

4. Adjust Connection Settings

  • In the connection settings, go to the “Custom” tab.
  • Enter the JDBC URL as jdbc:datometry://<IP>:1521/dtm_demo, where <IP> is the IP address of the VM named “OpenDB” as per your EC2 console.

5. Test the Connection
Click on “Test Connection…” to ensure that all configurations are correctly set up.

Troubleshooting

If you encounter any issues, verify that the Datometry JDBC driver is correctly added and that the class name and JDBC URL are correctly typed.

 

Connecting to OpenDB in PostgreSQL Mode via DBeaver

Prerequisites

  • DBeaver installed on your system.
  • Internet access to download the PostgreSQL JDBC driver.

Step-by-Step Guide

1. Download the PostgreSQL JDBC Driver

2. Set Up a PostgreSQL Connection in DBeaver

  • Open DBeaver and create a new PostgreSQL connection.

3. Configure Connection Settings

  • Enter the required connection details; the host is the IP address of the VM named “OpenDB” as per your EC2 console.

4. Test the Connection

  • Click on “Test Connection…” to verify that the connection is set up correctly.

Connecting to OpenDB in Java Application

Prerequisites

Setting Up the Environment

1. Download and Place the DTM JDBC Driver

  • Download the DTM JDBC driver from https://downloads.datometry.com/jdbc.

  • Place the downloaded JDBC driver in a known directory. For example, create a folder named lib in your project directory and put the driver there.

2. Set Up Your Java File

  • Create a file named OpenDBDemo.java.

  • Insert the following Java code into this file:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class OpenDBDemo {

   // Database connection parameters
   private static final String DB_URL = "jdbc:datometry://[[HOST]]:1521/dtm_demo";
   private static final String DB_USER = "[[USERNAME]]";
   private static final String DB_PASSWORD = "[[PASSWORD]]";

   public static void main(String[] args) {
      Connection conn = null;
      PreparedStatement pstmt = null;
      ResultSet rs = null;

      try {
         // Load DTM JDBC Driver
         Class.forName("com.datometry.Driver");

        // Establish connection
        conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);

        // Prepare SQL query
        String sql = "SELECT 1 FROM dual;";

        pstmt = conn.prepareStatement(sql);

       // Execute query
       rs = pstmt.executeQuery();

       // Process the results
       if (rs.next()) {
           int result = rs.getInt(1); // Retrieve the first column's value
           System.out.println("Query result: " + result);
       }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
       // Clean up resources
        try { if (rs != null) rs.close(); } catch (Exception e) { /* ignored */ }
        try { if (pstmt != null) pstmt.close(); } catch (Exception e) { /* ignored */ }
        try { if (conn != null) conn.close(); } catch (Exception e) { /* ignored */ }
    }
  }
}

Replace [[HOST]], [[USERNAME]], and [[PASSWORD]] in the code with the actual OpenDB host address, username, and password.

 

Compiling and Running the Program Using Command Line

  • Open a terminal or command prompt.
  • Navigate to the directory containing OpenDBDemo.java.

Compile the Java Program

  • Compile the Java file using the javac command and include the DTM JDBC driver in the classpath. Replace [YourPath] with the path to the folder where you stored the driver:
javac -cp ".:[YourPath]/dtmjdbc.jar" OpenDBDemo.java

For Windows, use a semicolon (;) instead of a colon (:) in the classpath.

Run the Compiled Java Program

Run the compiled Java class using the java command with the classpath specified:

java -cp ".:[YourPath]/dtmjdbc.jar" OpenDBDemo

Use a semicolon (;) for Windows.

 

Connecting to OpenDB in Oracle Mode via PolySQL+

PolySQL+ provides a command-line interface for interacting with OpenDB, much like SQLPlus. This section details how to use PolySQL+ to connect to OpenDB in Oracle mode.

Prerequisites

  • PolySQL+ installed on your system. PolySQL+ is already installed with your self-serve offering.

Step-by-Step Guide

1. Open PolySQL+

  • Launch PolySQL+ from your command line or terminal by entering polysqlplus. Upon doing so, you will be presented with the PolySQL+ terminal, as shown below:
PolySQL*Plus: Release 21.0.0.0.0 - Production on Wed Dec 6 19:11:42 2023
Version 2.4.102

Copyright (c) 1982, 2021, Datometry. All rights reserved.

SQL>

2. Connect to OpenDB

  • Use the following command format to establish a connection to OpenDB:
connect [[USERNAME]]/[[PASSWORD]]@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=[[HOST]])(Port=1521))(CONNECT_DATA=(SID=ORCLCDB))) as SYSDBA;

Replace [[USERNAME]], [[PASSWORD]], and [[HOST]] with your OpenDB username, password, and host IP address respectively.

3. Execute Queries

  • Once connected, you can execute SQL queries directly in the PolySQL+ interface. Example:
SELECT * FROM your_table;


4. Exit PolySQL+

  • To disconnect and exit PolySQL+, use the command:
exit

 

 

Comments

0 comments

Please sign in to leave a comment.