MacLochlainns Weblog

I had a request from one of the adjunct professors to connect SQL Developer to the PostgreSQL database. This is in support of our database programming class that teaches students how to write PL/SQL against the Oracle database and pgPL/SQL against the PostgreSQL database. We also demonstrate transactional management through Node.js, Python and Java.

Naturally, this is also a frequent step taken by those required to migrate PostgreSQL data models to an Oracle database. While my final solution requires mimicking Oracle’s database user to schema, it does work for migration purposes. I’ll update this post when I determine how to populate the database drop-down list.

The first step was figuring out where to put the PostgreSQL JDBC Java ARchive (.jar) file on a Linux distribution. You navigate to the end-user student account in a Terminal and change to the .sqldeveloper directory. Then, create a jdbc subdirectory as the student user with the following command:

mkdir /home/student/.sqldeveloper/jdbc

Then, download the most current PostgreSQL JDBC Java ARchive (.jar) file and copy it into the /home/student/.sqldeveloper/jdbc, which you can see afterward with the following command:

ll /home/student/.sqldeveloper/jdbc

It should display:

-rw-r--r--. 1 student student 1041081 Aug  9 13:46 postgresql-42.3.7.jar

The next series of steps are done within SQL Developer. Launch SQL Developer and navigate to Tools and Preferences, like this:

Inside the Preferences dialog, navigate to Database and Third Party JDBC Drivers like shown and click the Add Entry button to proceed:

Inside the Select Path Entry dialog, select the current PostgreSQL JDBC Java ARchive (.jar) file, which is postgresql-42-3.7.jar in this example. Then, click the Select button.

You are returned to the Preferences dialog as shown below. Click the OK button to continue.

After completing the 3rd Party Java Driver setup, you attempt to create a new connection to the PostgreSQL database. You should see that you now have two available Database Type values: Oracle and PostgreSQL, as shown below:

When you click on the PostgreSQL Database Type, the dialog updates to the following view. Unfortunately, I couldn’t discover how to set the values in the list for the Choose Database drop down. Naturally, a sandboxed user can’t connect to the PostgreSQL database without qualifying the database name.

Unless you qualify the PostgreSQL database or connect as the postgres user with a privileged password, SQL Developer translates the absence of a database selection to a database name equivalent to the user’s name. That’s the default behavior for the Oracle database but differs from the behavior for MySQL, PostgreSQL, and Microsoft SQL Server. It returns the following

Status: Failure - Test failed: FATAL: database "student" does not exist

As seen in the diaglog’s result when testing the connection:

Based on my hunch and not knowing how to populate the database field for the connection, I did the following:

Created a Linux OS videodb user.
Copied the .bashrc file with all the standard Oracle environment variables.
Created the /home/videodb/.sqldeveloper/jdbc directory.
Copied the postgresql-42.3.7.jar into the new jdbc directory.
Connected as the postgres super user and created the PostgreSQL videodb user with this syntax:
CREATE USER videodb WITH ROLE dba ENCRYPTED PASSWORD 'cangetin';
CREATE USER videodb WITH ROLE dba ENCRYPTED PASSWORD 'cangetin';

As the postgres super user, granted the following privileges:

-- Grant privileges on videodb database videodb user.
GRANT ALL ON DATABASE "videodb" TO "videodb";
 
-- Connect to the videodb database.
\c
 
-- Grant privileges.
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO videodb;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO videodb;

Added the following line to the pg_hba.conf file in the /var/lib/pgsql/15/data directory as the postgres user:
local all videodb peer
local all videodb peer
Connected as the switched from the student to videodb Linux user, and launched SQL Developer. Then, I used the Tools menu to create the 3rd party PostgreSQL JDBC Java ARchive (.jar) file in context of the SQL Developer program. Everything completed correctly.
Created a new PostgreSQL connection in SQL Developer and tested it with success as shown:

Saving the new PostgreSQL connection, I opened the connection and could run SQL statements and display the catalog information, as shown:

Connected as the videodb user to the videodb database I can display tables owned by student and videodb users:

-- List tables.
\d
 
                   List of relations
 Schema |           Name           |   Type   |  Owner
--------+--------------------------+----------+---------
 public | new_hire                 | table    | student
 public | new_hire_new_hire_id_seq | sequence | student
 public | oracle_test              | table    | videodb
(3 rows)

In SQL Developer, you can also inspect the tables, as shown:

At this point, I’m working on trying to figure out how to populate the database drop-down table. However, I’ve either missed a key document or it’s unfortunate that SQL Developer isn’t as friendly as MySQL Workbench in working with 3rd Party drivers.

The majority of information to write this post comes form knowing how Java works and where to find the PostgreSQL JDBC Java archive (.jar) file and the standard documentation. Here are the URLs:

The rest of the example is simply demonstrating how to create a fully working program to return one or more rows from a static query. After you download the latest PostgreSQL JDBC archive, with a command like:

wget https://jdbc.postgresql.org/download/postgresql-42.3.7.jar

Assuming you put it in test directory, like /home/student/java, you would add it to your Java $CLASSPATH environment variable, like this:

export set CLASSPATH="/home/student/Code/java/postgresql-42.3.7.jar:."

If you’re new to Java and Linux, the . (dot) represents the present working directory and is required in the Java $CLASSPATH to avoid raising a java.lang.ClassNotFoundException when you test your code. For example, the sample program name is PostgreSQLDriver.java and if you failed to include the present working directory in the $CLASSPATH it would raise the following error message when you try to run the compiled class file:

Error: Could not find or load main class PSQL
Caused by: java.lang.ClassNotFoundException: PSQL

Now that you’ve set your Java $CLASSPATH correctly, you can copy or type this PostgreSQLDriver.java Java program into a file.

// Import classes.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
 
/* You can't include the following on Linux without raising an exception. */
// import com.mysql.jdbc.Driver;
 
public class PostgreSQLDriver {
  public PostgreSQLDriver() {
    /* Declare variables that require explicit assignments because
       they're addressed in the finally block. */
    Connection conn = null;
    Statement stmt = null;
    ResultSet rset = null;
 
    /* Declare other variables. */
    String url;
    String username = "student";
    String password = "student";
    String database = "videodb";
    String hostname = "[::1]";
    String port = "5432";
    String sql;
 
    /* Attempt a connection. */
    try {
      // Set URL.
      url = "jdbc:postgresql://" + hostname + ":" + port + "/" + database;
 
      // Create instance of MySQLDriver.
      conn = DriverManager.getConnection (url, username, password);
 
      // Query the version of the database.
      sql = "SELECT version()";
      stmt = conn.createStatement();
      rset = stmt.executeQuery(sql);
 
      System.out.println ("Database connection established");
 
      // Read row returns for one column.
      while (rset.next()) {
        System.out.println("PostgreSQL Connected to the [" + rset.getString(1) + "] database."); }
 
    }
    catch (SQLException e) {
      System.err.println ("Cannot connect to database server:");
      System.out.println(e.getMessage());
    }
    finally {
      if (conn != null) {
        try {
          rset.close();
          stmt.close();
          conn.close();
          System.out.println ("Database connection terminated");
        }
        catch (Exception e) { /* ignore close errors */ }
      }
    }
  }
  /* Unit test. */
  public static void main(String args[]) {
    new PostgreSQLDriver();
  }
}

Now, you compile the program from the present working directory with this syntax:

javac PostgreSQLDriver.java

It creates a PostgreSQLDriver.class file, which you can run with this syntax:

java PostgreSQLDriver

It will return the following, which verifies you’ve connected to a valid database in the PostgreSQL instance. You should note that the IPV6 syntax is used in the example on line #25 but you could substitute localhost, an assigned host name, or an IP address.

I neglected to show how to install and configure the Java SDK on my earlier post without relying on including the $JAVA_HOME in the $PATH. This post shows how to install the Java and Java SDK on Alma Linux without layering it with the $JAVA_HOME environment variable.

You install the Java runtime and development kit with the following command:

sudo dnf install -y java-17-openjdk java-17-openjdk-devel

Then, you need to add it to the $PATH environment variable with the following syntax in your .bashrc file or environment file of your choice.

export set PATH=$PATH:/usr/lib/jvm/java-17-openjdk-17.0.8.0.7-2.el9.x86_64/bin/

After you source the new $PATH environment value, you can then write a file like this Hello.java file to test it.

// Define the Java program.
public class HelloWorld {
  public static void main(String[] args) {
    System.out.println("Hello World!");
  }
}

You compile the Hello.java file with this syntax:

javac Hello.java

Then, you run the compiled program with the following syntax:

java Hello

It’ll print:

Hello World!

Working with my students to create an Ubuntu virtual environment for Python development with the MySQL database. After completing the general provisioning covered in this older post, I’d recommend you create a python symbolic link before installing the MySQL-Python driver.

sudo ln -s /usr/bin/python3 /usr/bin/python

You install the Python development driver with the following:

sudo apt-get -y install python3-mysql.connector

Create a python_connect.py file to test your Python deployment’s ability to connect to the MySQL database:

#!/usr/bin/python
 
# Import the library.
import mysql.connector
from mysql.connector import errorcode
 
try:
  # Open connection.
  cnx = mysql.connector.connect(user='student', password='student',
                                host='localhost',
                                database='sakila')
 
  # Print the value.
  print("Database connection resolved.")
 
# Handle exception and close connection.
except mysql.connector.Error as e:
  if e.errno == errorcode.ER_ACCESS_DENIED_ERROR:
    print("Something is wrong with your user name or password")
  elif e.errno == errorcode.ER_BAD_DB_ERROR:
    print("Database does not exist")
  else:
    print(e)
 
# Close the connection when the try block completes.
else:
  cnx.close()

You should change the file permissions of the python_connect.py to read-write-execute as owner, and read-execute as group and other with the following command:

chmod 755 python_connect.py

Then, you can test your python_connect.py program from the local directory with the following command:

./python_connect.py

You see the following output if it works:

Database connection resolved.

As alway, I hope those looking for a code complete solutionk

I promised to give my students a full example of how to write and execute a Python object. There were two motivations for this post. The first was driven by my students trying to understand the basics and the second my somebody else saying Python couldn’t deliver objects. Hopefully, this code is simple enough for both audiences. I gave them this other tutorial on writing and mimicking overloaded Python functions earlier.

This defines a Ball object type and a FilledBall object subtype of Ball. It incorporates the following elements:

A special __init__ function, which is a C/C++ equivalent to a constructor.
A special __str__ function represents a class object instance as a string. It is like the getString() equivalent in the Java programming language.
A bounce instance function, which means it acts on any instance of the Ball object type or FilledBall object subtype.
A get_direction instance function and it calls the __format local object function, which is intended to mimic a private function call, like other object-oriented programming languages.
A private name __format function (Private name mangling: When an identifier that textually occurs in a class definition begins with two or more underscore characters and does not end in two or more underscores, it is considered a private name of that class.)

You can test this code by creating the $PYTHONPATH (Unix or Linux) or %PYTHONPATH% (Windows) as follows with all the code inside the present working directory, like this in Unix or Linux:

export set $PYTHONPATH=.

Then, you create the Ball.py file with this syntax:

# Creates a Ball object type and FilledBall object subtype.
class Ball:
  # User-defined constructor with required parameters.
  def __init__(self, color = None, radius = None, direction = None):
    # Assign a default color value when the parameter is null.
    if color is None:
      self.color = "Blue"
    else:
      self.color = color.lower()
 
    # Assign a default radius value when the parameter is null.
    if radius is None:
      self.radius = 1
    else:
      self.radius = radius
 
    # Assign a default direction value when the parameter is null.
    if direction is None:
      self.direction = "down"
    else:
      self.direction = direction.lower()
 
    # Set direction switch values.
    self.directions = ("down","up")
 
  # User-defined standard function when printing an object type.
  def __str__(self):
    # Build a default descriptive message of the object.
    msg = "It's a " + self.color + " " + str(self.radius) + '"' + " ball"
 
    # Return the message variable.
    return msg
 
  # Define a bounce function.
  def bounce(self, direction = None):
    # Set direction on bounce.
    if not direction is None:
      self.direction = direction
    else:
      # Switch directions.
      if self.directions[0] == self.direction:
        self.direction = self.directions[1]
      elif self.directions[1] == self.direction:
        self.direction = self.directions[0]
 
  # Define a bounce function.
  def getDirection(self):
    # Return current direction of ball.
    return self.__format(self.direction)
 
  # User-defined pseudo-private function, which is available
  # to instances of the Ball object and any of its subtypes.
  def __format(self, msg):
    return "[" + msg + "]"
 
# This is the object subtype, which takes the parent class as an
# argument.
class FilledBall(Ball):
  def __init__(self, filler = None):
    # Instantiate the parent class and then any incremental 
    # parameter values.
    Ball.__init__(self,"Red",2)
 
    # Add a default value or the constructor filler value.
    if filler is None:
      self.filler = "Air".lower()
    else:
      self.filler = filler
 
  # User-defined standard function when printing an object type, which 
  # uses generalized invocation.
  def __str__(self):
    # Build a default descriptive message of the object.
    msg = Ball.__str__(self) + str(" filled with " + self.filler)
 
    # Return the message variable.
    return msg

# Creates a Ball object type and FilledBall object subtype. class Ball: # User-defined constructor with required parameters. def __init__(self, color = None, radius = None, direction = None): # Assign a default color value when the parameter is null. if color is None: self.color = "Blue" else: self.color = color.lower() # Assign a default radius value when the parameter is null. if radius is None: self.radius = 1 else: self.radius = radius # Assign a default direction value when the parameter is null. if direction is None: self.direction = "down" else: self.direction = direction.lower() # Set direction switch values. self.directions = ("down","up") # User-defined standard function when printing an object type. def __str__(self): # Build a default descriptive message of the object. msg = "It's a " + self.color + " " + str(self.radius) + '"' + " ball" # Return the message variable. return msg # Define a bounce function. def bounce(self, direction = None): # Set direction on bounce. if not direction is None: self.direction = direction else: # Switch directions. if self.directions[0] == self.direction: self.direction = self.directions[1] elif self.directions[1] == self.direction: self.direction = self.directions[0] # Define a bounce function. def getDirection(self): # Return current direction of ball. return self.__format(self.direction) # User-defined pseudo-private function, which is available # to instances of the Ball object and any of its subtypes. def __format(self, msg): return "[" + msg + "]" # This is the object subtype, which takes the parent class as an # argument. class FilledBall(Ball): def __init__(self, filler = None): # Instantiate the parent class and then any incremental # parameter values. Ball.__init__(self,"Red",2) # Add a default value or the constructor filler value. if filler is None: self.filler = "Air".lower() else: self.filler = filler # User-defined standard function when printing an object type, which # uses generalized invocation. def __str__(self): # Build a default descriptive message of the object. msg = Ball.__str__(self) + str(" filled with " + self.filler) # Return the message variable. return msg

Next, let’s test instantiating the Ball object type with the following code:

#!/usr/bin/python
 
# Import the Ball class into its own namespace.
import Ball
 
# Assign an instantiated class to a local variable.
myBall = Ball.Ball()
 
# Check whether the local variable holds a valid Ball instance.
if not myBall is None:
  print(myBall, "instance.")
else:
  print("No Ball instance.")
 
# Loop through 10 times changing bounce direction.
for i in range(1,10):
  # Find dirction of ball.
  print(myBall.getDirection())
 
  # Bounce the ball.
  myBall.bounce()

Next, let’s test instantiating the FilledBall object subtype with the following code:

#!/usr/bin/python
 
# Import the Ball class into its own namespace.
import Ball
 
# Assign an instantiated class to a local variable.
myBall = Ball.FilledBall()
 
# Check whether the local variable holds a valid FilledBall instance.
if not myBall is None:
  print(myBall, "instance.")
else:
  print("No Ball instance.")
 
# Loop through 10 times changing bounce direction.
for i in range(1,10):
  # Find dirction of ball.
  print(myBall.getDirection())
 
  # Bounce the ball.
  myBall.bounce()

As always, I hope this helps those looking to learn and extend their knowledge.

The following are samples of creating, changing, and removing users and groups in Linux. These are the command-line options in the event you don’t have access to the GUI tools.

Managing Users:

Adding a user:

The prototype is:

# useradd [-u uid] [-g initial_group] [-G group[,...]] \
 > [-d home_directory] [-s shell] [-c comment] \
 > [-m [-k skeleton_directory]] [-f inactive_time] \
 > [-e expire_date] -n username