PL/Java, So we meet again...

Now that I have Matlab and Java playing nice, it is time to bring PostgreSQL into the mix. What could possibly go wrong?

I do have PostgreSQL and pljava working on my home laptop, but I keep getting blue screens and black screens with gibberish on them at random times. I think it is safe to say that, at a minimum, the hard drive on that one will need to be replaced soon.  Maybe I can get Steve to spill a beer on it? Anyway, back to the task at hand: a computer with Matlab, Eclipse, PostgreSQL and PL/Java all working together in harmony.

Nothing ever goes as easily as I hope it will. Since user guides never given you the full story, I figured I would enter some lessons learned just in case anyone else in foolish enough to try this (or for me to use as reference later when I do this yet again).

In your Environment Variables, make sure that you have (or at least something like):

In your postgresql.conf file, you need to add something like:

I think the message I hate most is: “ERROR:  could not load library "C:/Program Files/Postgres/8.3/lib/pljava.dll": The specified procedure could not be found.” It is a lot like Oracle’s tnsnames.ora error: not very informative and usually isn’t the real problem. The causes I have had for this error include, but are not limited to:

• I had the wrong version of Java installed,
• The pljava.jar file was for the a different version of PostgreSQL,
• All of my variables weren’t set exactly right,
• I had the 64 bit version of Java, but PostgreSQL was still at 32 bit,
• I’m sure there are others.

Once you think you have it working, here is a nice test using one of the default sqlj functions, just to make sure:

CREATE FUNCTION getsysprop(VARCHAR)

  RETURNS VARCHAR

  AS 'java.lang.System.getProperty'

  LANGUAGE java;

SELECT getsysprop('user.home'); 

If this function doesn’t work, you have issues. Now it is time to try to put one of your own functions in!

Once your function is written in java, export it as a jar file. Then the fun begins. Do not forget to do what I always seem to which is to set your class path. Then you can create and test your function. It is generally good to have a basic “test function” that really doesn’t do much to help you test that your jar loaded properly.

Now, getting PostgreSQL and Matlab working together is another story…