PL/Java Notes

The following were the steps taken in my attempt to install
pl/java on windows.

System Information: PostgreSQL Server 8.0 on Windows XP, Java 1.5

• Download pl/java binary
  Since I use Postgresql 8.0, I downloaded pljava-i686-pc-mingw32-pg8.0-1.2.0.tar.gz
  
  
• Extract pljava
  Extract pljava archive into a directory. I installed it in C:\Program Files\PostgreSQL\8.0\pljava
  
  
• Edit the C:\Program Files\PostgreSQL\8.0\data\postgresql.conf file
  Add (or modify) the following settings. Note the double backslashes:
  
  
  
  
  

  dynamic_library_path = '$libdir;C:\\Program Files\\PostgreSQL\\8.0\\pljava' custom_variable_classes = 'pljava' pljava.classpath='C:\\Program Files\\PostgreSQL\\8.0\\pljava\\pljava.jar'

  
  
  
• Add JRE to your System Path
  It is assumed that you have already installed a java runtime environment (JRE) or the java development kit (JDK) on your system.
  
  Here are the entries in my system environment
  
  

  JRE_HOME=C:\Program Files\Java\jre1.5.0_05 PATH=%PATH%;%JRE_HOME%\bin;%JRE_HOME%\bin\client;%JAVA_HOME%\bin

  
  
  
• Restart PostgreSQL Server for the changes to take affect.
  
  
• Install pl/java
  The extracted pljava files will include an install.sql and uninstall.sql script.
  
  run
  
  

  {location of psql}/psql.exe -f {location of pljava directory}/install.sql {target database where you want to install pl/java}

  
  
  To uninstall run
  

  {location of psql}/psql.exe -f {location of pljava directory}/uninstall.sql {target database where you want to install pl/java}

  
  
  
• Testing your installation
  
  I followed the instructions to run the test.Here is a summary of the steps.
  
  
  
  
  
  • Download pljava source
    
    I had to do this because the binary did not include test.jar.It only included examples.jar
    
    I downloaded the source from sourceforge
    Since I use Postgresql 8.0, I downloaded pljava-src-1.2.0.tar.gz
    
    
  
  
  
  
  • Extract source to a folder in your system
    
    I used C:\Program Files\PostgreSQL\8.0\pljava\pljava-1.2.0
    
    
  
  
  
  
  
  • Compile and jar test.jar
    
    Navigate to the subdirectory - ..\src\java\test
    compile the source and jar into test.jar
    Note that you may require the postgresql jdbc driver, pljava.jar, deploy.jar and examples.jar in your classpath
    
    
  
  
  
  
  • Install test schema
    
    I ran into issues here as my postgresql installation did not provide cross-reference database feature.
    So when I used psql on the database that I had used to install pljava and tried to run the command
    
    

    SELECT sqlj.install_jar('file:///some/directory/examples.jar', 'samples', true);

    
    
    It complained about the lack of cross reference feature.This is because the examples.jar include a deployment script
    to create a schema called javatest.
    
    In order to get around it I had to create the database javatest first manually.
    <location of createdb executable>/createdb javatest
    
    
  
  
  
  
  
  • install pljava on javatest
    
    connect to javatest database using psql
    
    run the command
    
    

    SELECT sqlj.install_jar('file:///some/directory/examples.jar', 'samples', true);

    
    
    I had to make a few attempts here as the location of examples.jar was misleading to postgresql
    
    The following urls failed for my local installation of c:\Program Files\PostgreSQL\8.0\pljava
    
    file://c:/Program Files/PostgresqlSQL/8.0/pljava/examples.jar
    file://c:\Program Files\PostgresqlSQL\8.0\pljava\examples.jar
    file://c:\\Program Files\\PostgresqlSQL\\8.0\\pljava\\examples.jar
    
    The following url finally worked
    file:\c:\\Program Files\\PostgreSQL\\8.0\\pljava\\examples.jar
    
    

    javatest=# select sqlj.install_jar('file:\c:\\Program Files\\PostgreSQL\\8.0\\pl java\\examples.jar', 'samples', true);

    
    
    This successfully created table employees1 and employees2
    
    Then define the classpath for this schema
    
    

    SELECT sqlj.set_classpath('javatest', 'samples');

    
    
    Now you should be able to run the client test application
    
    

    java -cp org.postgresql.pljava.test.Tester

    
    
    Here is the list of issues I ran into while running the test...
    
    
    Wrapping directory names which have space in it in the classpath
    
    I had installed jre and pljava under c:\Program Files
    I had to tweak the classpath in the System Environment Variable Setting to wrap all references to
    c:\Program Files with double quotes.
    
    So an echo %CLASSPATH% showed as
    
    

    .;"C:\Program Files"\Java\jre1.5.0_05\lib\rt.jar;"...

    
    
    instead of
    
    

    .;C:\Program Files\Java\jre1.5.0_05"\lib\rt.jar;...

    
    
    
    passing the database name and credentials
    
    Invoking the command mentioned above will not work unless you are running a database with the same name as the current user with a user who is the current
    
    user and a blank password.
    
    If you run a command similar to the above you end up getting an error.
    
    

    C:\Program Files\PostgreSQL\8.0\pljava>java -cp %CLASSPATH%;"c:\Program Files\Po stgreSQL\8.0\pljava\test.jar" org.postgresql.pljava.test.Tester org.postgresql.util.PSQLException: The server requested password-based authentic ation, but no password was provided. at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(Connect ionFactoryImpl.java:325) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(Conne ctionFactoryImpl.java:94) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactor y.java:65) at org.postgresql.jdbc2.AbstractJdbc2Connection.(AbstractJdbc2Conn ection.java:117) at org.postgresql.jdbc2.Jdbc2Connection.(Jdbc2Connection.java:24) at org.postgresql.Driver.connect(Driver.java:235) at java.sql.DriverManager.getConnection(Unknown Source) at java.sql.DriverManager.getConnection(Unknown Source) at org.postgresql.pljava.test.Tester.main(Tester.java:184)

    
    
    I had to try the help option to get the list of options available
    
    

    C:\Program Files\PostgreSQL\8.0\pljava>java -cp %CLASSPATH%;"c:\Program Files\Po stgreSQL\8.0\pljava\test.jar" org.postgresql.pljava.test.Tester --help usage: java org.postgresql.pljava.test.Tester [ -host ] # default is localhost [ -port ] # default is blank [ -database ] # default is name of current user [ -user ] # default is name of current user [ -password ] # default is no password [ -debug ] # wait for debugger to attach to backend

    
    
    
    Next I tried with the database name, username and password.However I still end up with errors
    
    

    C:\Program Files\PostgreSQL\8.0\pljava>java -cp %CLASSPATH%;"c:\Program Files\Po stgreSQL\8.0\pljava\test.jar" org.postgresql.pljava.test.Tester -database javate st -user foo -password bar *** testTimestamp() java.sql.SQLException: ERROR: java.lang.SecurityException: read on \C:\Program F iles\PostgreSQL\8.0\pljava\examples.jar at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx ecutorImpl.java:1471) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor Impl.java:1256) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja va:175) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat ement.java:389) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Abstract Jdbc2Statement.java:314) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc 2Statement.java:221) at org.postgresql.pljava.test.Tester.testTimestamp(Tester.java:466) at org.postgresql.pljava.test.Tester.testParameters(Tester.java:242) at org.postgresql.pljava.test.Tester.main(Tester.java:198)

    
    
    
    On a related note when I try running the test on another machine with similar environment, I got
    the following error
    
    
    

    C:\Program Files\PostgreSQL\8.0\pljava>java -cp %CLASSPATH%;"c:\Program Files\Po stgreSQL\8.0\pljava\test.jar" org.postgresql.pljava.test.Tester Exception in thread "main" java.lang.UnsupportedClassVersionError: org/postgresq l/pljava/test/Tester (Unsupported major.minor version 49.0) at java.lang.ClassLoader.defineClass0(Native Method) at java.lang.ClassLoader.defineClass(Unknown Source) at java.security.SecureClassLoader.defineClass(Unknown Source) at java.net.URLClassLoader.defineClass(Unknown Source) at java.net.URLClassLoader.access$100(Unknown Source) at java.net.URLClassLoader$1.run(Unknown Source) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source) at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source) at java.lang.ClassLoader.loadClass(Unknown Source) at java.lang.ClassLoader.loadClassInternal(Unknown Source)