R – Connecting to Progress database from Mac OSX

I solved my issue, after roughly 6 or 7 hours wasted.

Thanks to this post: Virtualbox "port forward" from Guest to Host

And specifically to the comment from @Nicholas which made me realize that I had deactivated my Mac's firewall, but not the firewall in the guest OS. Turning that off made everything work suddenly.

I suppose that the default configuration of the guest OS's firewall did allow for SSH on port 2222, as that worked fine even with the firewall in place, which served to mislead me into thinking that there was something wrong with my database driver or connection.

Hopefully this will benefit posterity.

EDIT:

As @HarpreetDawar mentions, the correct connection string to access the database is:

jdbc:oracle:thin:@localhost:1521/PDB1

The one that I was using, ending in :orcl instead of /PDB1, connects to the "container database". I don't fully understand Oracle 12c and the "pluggable database" implementation, but the rough idea is that you can have multiple databases within a single one, and you can turn them on and off by plugging/unplugging them. Hence, "PDB1", Pluggable Database 1.

If you connect to the Container, you will find that you are unable to create a new User/Schema without prefixing it with "C##", which is a tad awkward. That's because the Container is not meant to be used as a normal DB. You connect to the Container to plug/unplug the pluggable dbs (and other things, no doubt).

So if you want to connect to the Container, use a colon and the "SID" (orcl in the VM) in the connection string, and if you want to connect to a Pluggable, use a forward slash and the "Service Name" (PDB1 in the VM) in the connection string.

Anyway, this is additional data that I learned after solving my original problem.

One more thing! A note on port forwarding.

Let's say you now have your VM up and running, and you can connect to your Oracle DB properly, etc. But you have a friend who wants to use the DB with you? Well, that's easy. He should just use the same connection string, with your IP address instead of localhost, and the port forwarding that you set up in VirtualBox should send his connection (using port 1521) straight into your VM. Right?

Well, if you set up your Port Forwarding as From: 127.0.0.1, it won't work, because your buddy's connection is going to your external IP address. So add an additional port forwarding setting, from your external IP address to your VM. Then it will work great!

-Matt

My best guess is that I shall use org.sqlite.SQLiteDataSource class (it comes in sqlite-jdbc-3.15.1.jar for Xerial sqlite-jdbc driver),

Yes, that seems likely.

but how?

I just tried the following and it worked for me:

package com.example.sqlite.sqlite_test;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.sqlite.SQLiteDataSource;

public class SqliteTestMain {

    public static void main(String[] args) {
        SQLiteDataSource ds = new SQLiteDataSource();
        ds.setUrl("jdbc:sqlite::memory:");
        try (Connection conn = ds.getConnection()) {
            System.out.println("Connected.");
            String sql = 
                    "SELECT COUNT(*) AS n FROM \"sqlite_master\"";
            try (
                    Statement s = conn.createStatement();
                    ResultSet rs = s.executeQuery(sql)) {
                rs.next();
                System.out.printf(
                        "The \"sqlite_master\" table contains %d row(s).%n", 
                        rs.getInt(1));
            }
        } catch (SQLException e) {
            e.printStackTrace(System.err);
        }
    }

}