[Pljava-dev] pl/java and oracle compatibility

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