OpenIndiana and PostgreSQL

Table of Contents

• Compilation
• Installation
• Lookup by Primary Key
• Connecting to other PostgreSQL Instances
• Connecting to Oracle
• Downloads
• Footnotes

Compilation

In order for the example to compile, both plava.jar and the Oracle JDBC driver need to be on the classpath. For the former see Set Returning Functions with PL/Java in PostgreSQL: CLASSPATH and for the latter we refer to the download section below.

Installation

After compiling the example, install the example.jar file in the usual way.

# select sqlj.install_jar( 'file:///path/to/example.jar', 'example', false );

And set the classpath.

# select sqlj.set_classpath( 'example', 'example' );

These steps are not performed by the installation script, as it does not know where the example.jar file is located.

Caution. The provided installation script, example.sql, drops the schema called example and recreates it with all the tables and functions. Do not run it on a pre–existing database unless you know for a fact this schema does not exists. We recommend that it be used on a pristine database.

The installation script can be run from within a psql session with

# \i example.sql

Lookup by Primary Key

The following is the names table from which we'll be selecting from and the id column is the primary key.

The function getName() looks for the name by the integer id.

    public static String getName( int id )
	throws SQLException
    {

To select from this table, we first start by creating a JDBC connection to the server. This connection represents the currently running transaction and as such we can not modify it in any way. There is no concept of a commit or rollback inside a stored procedure.

	Connection connection 
	    = DriverManager.getConnection( "jdbc:default:connection" );

Then we create a simple query that looks up a text value, the name, by an integer primary key.

	PreparedStatement query 
	    = connection.prepareStatement( "select name from example.names"
					   + " where id = ?" );

We are using the dispose pattern and we'll close the query in a finally clause, so we start with a try.

	try {

The query parameter is set to the function argument. PreparedStatement parameters start at one.

	    query.setInt( 1, id );

Then we execute the query..

	    ResultSet results = query.executeQuery();

If there are no results (the correspending key was not present in the table) we return null.

	    if ( !results.next() )
		{
		    return null;
		}

Since we are looking up by primary key, there is only ever a single row in the ResultSet.

	    return results.getString( 1 );

If we do not close the PreparedStatement, PL/Java will do it for us with a warning. These can be annoying and a hindrance when debugging real problems.

	} finally {
	    query.close();
	}

An example.

# select example.get_name( 1 );

Connecting to Other PostgreSQL Instances

First, in order to use the JDBC driver to connect to other PostgreSQL instances, or the same one for semi-autonomous transactions, it needs to be set on the pljava.classpath in postgresql.conf¹.

pljava.classpath='$libdir/pljava.jar:$libdir/postgresql-9.1-901.jdbc4.jar'

The function getVersion() which connects to a PostgreSQL instance and returns the value of SELECT version();

    public static String getVersion( String host, String port,
				     String user, String database )
	throws SQLException
    {

Our example is rather limited, it does not allow us to specify a password or otherwise set connection parameters such as ssl. Adding that is left as an exercise for the reader.

We are using the dispose pattern and therefore start with null initializations and a try block.

	Connection connection = null;
	PreparedStatement versionQuery = null;

	try {

To avoid constructing a connection string, all the parameters are set with properties.

            Properties properties = new Properties();
            properties.setProperty( "user", user );

The following ones are coded with internal knowledge of the JDBC driver. This is the reason we chose the port parameter to be a String rather than int; it saves us from conversions.

            properties.setProperty( "PGHOST", host );
            properties.setProperty( "PGPORT", port );
            properties.setProperty( "PGDBNAME", database );

We then use the properties to create the connection directly.

	    connection = DriverManager.getConnection( "jdbc:postgresql",
						      properties );

Our example just queries the server for its version.

	    String selectVersion = "SELECT pg_catalog.version()";

	    versionQuery = connection.prepareStatement( selectVersion );

Unlike our previous example, there are no parameters to set before execution.

	    ResultSet results = versionQuery.executeQuery();

Since our query either returns exactly one row, or fails — in which case we never reach here due to the SQLException — we do no error checking and just select the first row in the result set.

	    results.next();

After which we just return the results.

	    return results.getString( 1 );

In order to prevent stale connections to Postgres from accumulating, we make sure to clean up our resources before exiting the function.

	} finally {
	    if ( versionQuery != null )
		versionQuery.close();

	    if ( connection != null )
		connection.close();
	}	    
    }

Some examples.

# select example.get_version( 'localhost', '5432', 'johann', 'johann' );

# select example.get_version( 'localhost', '5433', 'johann', 'johann' );

# select example.get_version( 'localhost', '5434', 'johann', 'johann' );

# select example.get_version( '192.168.1.6', '5432', 'postgres', 'johann' );

Connecting to Oracle

In this example we do not use the service loader mechanism to load the Oracle driver behind the scenes. Instead we load it directly with an OracleDataSource and therefore we can install the driver normally with sqlj.install_jar().

# select sqlj.install_jar( 'file:///path/to/ojdbc6.jar', 'ojdbc6', false );

And append it to the classpath.

# select sqlj.set_classpath( 'example', 'example:ojdbc6' );

Depending on the Java platform, the function may need to be untrusted; that is createed with LANUGUAGE javau. This is due to the strict security manager in PL/Java and that the Java platform may need to load a cryptographic library from /usr/lib when connecting to Oracle². The provided installation script does this.

In order to simplify this particular set returning function, we cheat and create a dummy table, example.oracle_version_t that servers as the return parameter. This way, we make use of the ResultSetHandle interface and do not have to hand code an iterator.

    public static ResultSetHandle oracleVersion( String url )
	throws SQLException
    {

We obtain an OracleDataSource and construct the connection url from the argument.

	OracleDataSource ods = new OracleDataSource();

	ods.setURL( "jdbc:oracle:thin:" + url );

Then we establish the connection and return a new ResultSetHandle.

	Connection connection = ods.getConnection();

	return new OracleVersion( connection );
    }

The actual code is contained in the inner class, OracleVersion.

    private static class OracleVersion
	implements ResultSetHandle
    {

Our private parameters. We hold on to the connection object so we can close it when we're done.

	private Connection connection;
	private PreparedStatement selectVersion;

	public OracleVersion( Connection connection )
	{
	    this.connection = connection;
	}

The getResltSet function is used by PL/Java to get a hold of the result set from the query. This particular interface is meant to make it easy to return queries of the form SELECT * FROM table. In this case we cheated and created a local table that looks like the remote query to use as the return type in Postgres.

	public ResultSet getResultSet()
	    throws SQLException
	{
	    
	    selectVersion = connection.prepareStatement
		( "select banner from v$version" );

	    return selectVersion.executeQuery();
	}

When there are no more rows in the result set, PL/Java calls close() — which we use to close the connection to Oracle.

	public void close()
	    throws SQLException
	{
	    connection.close();
	}
    }

An example.

# select * from example.oracle_version( 'johann/johann@localhost:1522/orcl' );

Downloads

• 2ndquadrant-pljava-example.tar.bz2 (PostgreSQL License)

External Dovnloads

• PostgreSQL JDBC Driver
• Oracle JDBC Driver (this link is liable to rot)

Footnotes

¹ This is true when using the service loader mechanism. When the driver is loaded directly by name the jar file may reside in the database.

² If this is an issue, please bring your concern to the PL/Java mailing list.