From: thhal at mailblocks.com (Thomas Hallgren)
Date: Wed, 23 Feb 2005 09:17:20 +0100
Subject: [Pljava-dev] pl/java and oracle compatibility
In-Reply-To: <ICCLWN$0040A8FD36E7EE4B959BA9914F0E5656@uol.com.br>
References: <ICCLWN$0040A8FD36E7EE4B959BA9914F0E5656@uol.com.br>
	<421C3C10.2070005@mailblocks.com>
	<thhal-0ktT3Ap/YxichWbt4tRose+2vMMw/sH@mailblocks.com>
	<thhal-0ktT3AqHYxiclynf8nRR0PpkdURjG6u@mailblocks.com>
Message-ID: <thhal-0k9T3AqPYxicqnRYG9AviDakRnHkMDj@mailblocks.com>

Rodrigo,
Thanks for the examples. I can directly conclude that the parameter 
passing from the trigger to the procedure in the Oracle case is 
something that PLJava has no control over and thus can do nothing about. 
The Oracle trigger passes a non-constant value as a parameter to the 
stored procedure and the stored procedure makes use of an OUT parameter.

The PostgreSQL syntax stipulates that you call a function with zero or 
more constant arguments (you use this to pass a name of a column since 
the non-constant value of that column cannot be passed on). Further 
more, PostgreSQL doesn't have stored procedures and their functions does 
not allow OUT parameters.

Having said that, what *can* be done using PLJava?

Well, first of all, I'd like to rewrite your PLJava example so that it 
performs the exact same task as your Oracle example (the examples you 
sent where very different):

  public static void DeptIdJava(TriggerData td)
  throws SQLException
  {
    Connection c = DriverManager.getConnection( "jdbc:default:connection" );
    String vQuery = "select lkup_key.nextval from dual";
    PreparedStatement st = c.prepareStatement( vQuery );
    ResultSet rs = st.executeQuery();
    rs.next();
    td.getNew().updateInt(td.getArguments()[0], rs.getInt( "proximo" ));
    st.close();
  }

Now there is one single line differentiateing the two.

If you want to keep your Oracle trigger 100% intact, you can instead add 
a dispatch class with for the PLJava case like so:

public class PLJavaTesteTrigger
{
  public static void DeptIdJava(TriggerData td)
  {
      int[] deptIdOut = new int[1];
      TesteTrigger.DeptIdJava(deptIdOut);
      td.getNew().updateInt(td.getArguments()[0], deptIdOut[0]);
  }
}

(the use of td.getArguments()[0] could perhaps be replaced by a 
hardcoded "dept_id" if that's always the case)

Keep your dispatch classes in a separate package that you don't bundle 
with the Oracle version.

A side note:
I see a System.exit() in your example. Starting with the next version of 
PLJava (due this week), an attempt to do System.exit() will yield a 
SecurityException since such a call effectively kills the backend 
process. My advice is to let your method throw an SQLException when it 
encounters a problem. That will cause a controlled transaction rollback.

Regards,
Thomas Hallgren